| Sql*loader - How can I skip filed from tab delimitted file? [message #314751] |
Thu, 17 April 2008 12:57  |
poratips
Messages: 345
Registered: April 2005
Location: IL
|
Senior Member |
|
|
Hi,
I have incoming file which is TAB Delimitted and i want to load into oracle table which also nneds to be filtered based on field record criteria.
I am using oracle 9i R2 on Sun Solaris server.
Table:
ID VARCHAR2(18)
DESC VARCHAR2(40)
C_FLAG VARCHAR2(1)
E_FLAG VARCHAR2(1)
L_PRICE NUMBER(11,3)
UOM VARCHAR2(3)
DIV VARCHAR2(2)
B_UOM VARCHAR2(3)
E_CAT VARCHAR2(2)
E_UPC VARCHAR2(18)
G_WGHT VARCHAR2(13)
wUNIT2A VARCHAR2(3)
VOL VARCHAR2(13)
volUnit2A VARCHAR2(3)
L1 VARCHAR2(13)
W1 VARCHAR2(13)
H1 VARCHAR2(13)
UOD VARCHAR2(3)
A_UOM VARCHAR2(3)
N_CNV VARCHAR2(5)
D_CNV VARCHAR2(5)
E_CAT2 VARCHAR2(2)
E_UPC2 VARCHAR2(18)
G_WGHT2 VARCHAR2(13)
W_UNIT2 VARCHAR2(3)
VOL2 VARCHAR2(13)
VOL_UNIT2 VARCHAR2(3)
L2 VARCHAR2(13)
W2 VARCHAR2(13)
H2 VARCHAR2(13)
UOD2 VARCHAR2(3)
S_ORG VARCHAR2(4)
D_CH VARCHAR2(2)
MIN_QTY VARCHAR2(13)
P_ID VARCHAR2(4)
Desh VARCHAR2(3)
While loading data into table, i need to ignore following field from the file which is not in my table.
wUNIT2A
volUnit2A
My table is exactly in same order as file except above two fields (wUNIT2A and volUnit2A).
wUNIT2A field comes into file after G_WGHT and volUnit2A field comes after VOL.
How can i avoid while laoding this kind of file into table?
Thanks,
[Updated on: Fri, 18 April 2008 00:48] by Moderator Report message to a moderator |
|
|
|
|
|
|
|
|
|
| Re: Sql*loader - How can I skip filed from tab delimitted file? [message #314794 is a reply to message #314770] |
Thu, 17 April 2008 15:37  |
poratips
Messages: 345
Registered: April 2005
Location: IL
|
Senior Member |
|
|
Thanks Brian and Micheal,
I mis-understood, sorry about it.
I was using filler filed like w-UNIT2A, which is in incoming file map doc i was received but i changed it to wUNIT2A FILLER and then it works.
| Quote: |
I am having still other issue that one fo the filed length is in table is char and when i see the rejected log then i saw that few records get rejected due to length problem.
I have checked actual file found the data like:
123 ==> loading corrctly
1234 ==> Loadind correctly
12345 but this kind of records getting rejected becuase when i move the right cursor, its not moving to next field value but going one more char (white space), how can i handle this?
My control file is:
LOAD DATA
infile 'tab.txt'
DISCARDMAX 999
TRUNCATE
INTO TABLE TAB_Table
FIELDS TERMINATED BY X'09'
TRAILING NULLCOLS
(
fields...
....
)
|
Thanks,
[mod-edit] fixed code tag.
[Updated on: Fri, 18 April 2008 00:47] by Moderator Report message to a moderator |
|
|
|
| Re: Sql*loader - How can I skip filed from tab delimitted file? [message #314803 is a reply to message #314794] |
Thu, 17 April 2008 16:33 |
poratips
Messages: 345
Registered: April 2005
Location: IL
|
Senior Member |
|
|
Thanks for fixing format.
| Quote: |
Actually i used TRIM function and its working.
I am trying to use WHEN for conditional loading (AND and OOR)but it won't me let to use OR as i know it won't support.
I have to load data from file for following condition.
if A_UOM = 'U' and d_Ch = '1' and S_ORG = 'U02' then div= 'Z' OR S_ORG = 'U03' then div= 'G' OR S_ORG = 'U04' then div= 'C'
|
How can I use both AND and OR clause in when condition?
Thanks,
[Updated on: Fri, 18 April 2008 00:45] by Moderator Report message to a moderator |
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
