Home » RDBMS Server » Server Utilities » Sql*loader - How can I skip filed from tab delimitted file?
Sql*loader - How can I skip filed from tab delimitted file? [message #314751] Thu, 17 April 2008 12:57 Go to next message
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 #314753 is a reply to message #314751] Thu, 17 April 2008 13:01 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
Use FILLER.
Re: Sql*loader - How can I skip filed from tab delimitted file? [message #314768 is a reply to message #314753] Thu, 17 April 2008 13:36 Go to previous messageGo to next message
poratips
Messages: 345
Registered: April 2005
Location: IL
Senior Member
Thanks.
But I want to skip from file which contains additional fields but my table doesn't have these fields.
So How can i skip these from files?

Thanks,
Re: Sql*loader - How can I skip filed from tab delimitted file? [message #314770 is a reply to message #314768] Thu, 17 April 2008 13:40 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
ebrian wrote on Thu, 17 April 2008 20:01
Use FILLER.

Click on the link Brian provided.

Regards
Michel
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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

Re: Sql*loader - How can I skip filed from tab delimitted file? [message #314810 is a reply to message #314803] Thu, 17 April 2008 17:37 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8635
Registered: November 2002
Location: California, USA
Senior Member
A when clause is used for determining which table to load into or not. What you are describing is just a condition for determining the value of one column. Just use decode or case or a function and put your conditions in there in your field list, remembering to put a colon in front of the other column names in the condition expression:

div "decode (...)"
Re: Sql*loader - How can I skip filed from tab delimitted file? [message #315038 is a reply to message #314810] Fri, 18 April 2008 13:30 Go to previous message
poratips
Messages: 345
Registered: April 2005
Location: IL
Senior Member
Thanks Barbara,
I will try it.
Appreciate your help.

Thanks,
Previous Topic: IMP-00051: Direct path exported dump file contains illegal column length
Next Topic: load conditional column data into oracle table-column
Goto Forum:
  


Current Time: Wed Dec 07 20:26:34 CST 2016

Total time taken to generate the page: 0.05576 seconds