OraFAQ Forum: Server Utilities

Home » RDBMS Server » Server Utilities » sqlloader

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

sqlloader [message #565970]

Mon, 10 September 2012 03:21

bhanupratap.s
Messages: 18
Registered: September 2012
Location: chennai

Junior Member

Hi experts,

I have written the code for sql loader, I have given termination of file is | , i have given five columns name in sequence

My Requirement is that i want to mapped 100th column from Csv file after 5th column how to do it.
please suggest me.

 
OPTIONS (ERRORS=100000000)
LOAD DATA 
CHARACTERSET UTF8
 
 
INTO TABLE "TARGET_STG_AMC"
TRUNCATE
 
FIELDS TERMINATED BY '|'
TRAILING NULLCOLS
(
GFCID            	"TRIM(:GFCID)",
CUST_NAME        	"TRIM(:CUST_NAME)",
COUNTRY_CODE         "TRIM(:COUNTRY_CODE)",
ISOLEGALNAME         "TRIM(:ISOLEGALNAME)",
COUNTRY_NAME      	"TRIM(:COUNTRY_NAME)",
ABBREVIATION		"TRIM(:ABBREVIATION)",
TRADE_STYLE		"TRIM(:TRADE_STYLE)"
)

Report message to a moderator

Re: sqlloader [message #565972 is a reply to message #565970]

Mon, 10 September 2012 03:38

John Watson
Messages: 8964
Registered: January 2010
Location: Global Village

Senior Member

If your data file is a CSV file, your field delimiter must be a comma ',' not a pipe '|'. That is what CSV means: comma separated values.

Report message to a moderator

Re: sqlloader [message #565973 is a reply to message #565970]

Mon, 10 September 2012 03:39

dariyoosh
Messages: 538
Registered: March 2009
Location: France

Senior Member

bhanupratap.s wrote on Mon, 10 September 2012 10:21

... My Requirement is that i want to mapped 100th column from Csv file after 5th column how to do it.
please suggest me ...

Hello there,

Are you sure that this is the correct SQLLoader control file for updating your table? because here I see only 7 columns, yet you're talking about the 100th column in the CSV file (actually your pipe delimited file) that is read by this SQLLoader control file, for me this is not possible.

Regards,

[Updated on: Mon, 10 September 2012 03:40]

Report message to a moderator

Re: sqlloader [message #565974 is a reply to message #565972]

Mon, 10 September 2012 03:55

bhanupratap.s
Messages: 18
Registered: September 2012
Location: chennai

Junior Member

Hi,

1015298029~PRESTIGE GLOBAL EQUITY SDN BHD~CORP~~1015298029~MY~~~~~~~~~~~~~~~~50 JALAN KHAW SIM BEE PENANG~GEORGETOWN~~MY~10400~50 JALAN KHAW SIM BEE PENANG~GEORGETOWN~~MY~10400~~ ~~~~~~6519~~~~~~531190~~~~~~30 AUG 2012 01:27:04~~~~~ ~~~N~~MY~41~~~~1015298029~OPEN~01010101000000~~~~~~~~~~~~~~~~~~~~~~~~~~~~Prestige Global Equity Sdn Bhd~L~985426V~~~~985426-V~~~~GCG~99999999~99999999~~~MY~N~~PRESTIGE GLOBAL EQUITY SDN BHD~ ~PRESTIGE GLOBAL EQUITY SDN BHD~ ~~ ~3222730~~~U~U~U~U~U~U~~
1015298037~VOPAK TERMINALS AUSTRALIA PTY LTD~CORP~~1001080969~AU~752095778~403935674~~~~~~~~~~~~~~49 FRIENDSHIP ROAD ~MATRAVILLE~NEW SOUTH WALES~AU~2036~49 FRIENDSHIP ROAD ~MATRAVILLE~NEW SOUTH WALES~AU~2036~~ ~~~~~~4226~~~~~~493190~~~~~~30 AUG 2012 01:35:14~~~~~ ~~~N~~AU~42~~~~9000172186~OPEN~01010101000000~~~~~~~~~~~~~~~~~~~~~~~~~~~~Vopak Terminals Australia Pty Limited~L~00000~~~~064 452 520~~~~GB~30801000~30801000~~~NL~N~~VOPAK TERMINALS AUSTRALIA PTY LTD~ ~VOPAK TERMINALS AUSTRALIA PTY LTD~ ~~ ~3223796~~~U~U~U~U~U~U~~
1015298045~FMC CHEMICALS KK~CORP~~0000091189~JP~696562557~009146945~~~~~~~~~~~~~~1-2-3 KITA AOYAMA ~MINATO-KU~TOKYO~JP~10



yes 7 columns are in same  sequence as in csv file to ctl file...after seventh column i want to add 100th column from my csv file into .ctl file.i mean i wnt to add dynamically after 7th column i don't want to add 8th column i want to add 100th column.

Report message to a moderator

Re: sqlloader [message #565977 is a reply to message #565974]

Mon, 10 September 2012 04:31

Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Use FILLER to ignore the 8th to 99th fields.

Regards
Michel

[Updated on: Mon, 10 September 2012 04:31]

Report message to a moderator

Re: sqlloader [message #565978 is a reply to message #565974]

Mon, 10 September 2012 04:42

bhanupratap.s
Messages: 18
Registered: September 2012
Location: chennai

Junior Member

so i have to use like this:

OPTIONS (ERRORS=100000000)
LOAD DATA 
CHARACTERSET UTF8
 
 
INTO TABLE "TARGET_STG_AMC"
TRUNCATE
 
FIELDS TERMINATED BY '|'
TRAILING NULLCOLS
(
GFCID            	"TRIM(:GFCID)",
CUST_NAME        	"TRIM(:CUST_NAME)",
COUNTRY_CODE         "TRIM(:COUNTRY_CODE)",
ISOLEGALNAME         "TRIM(:ISOLEGALNAME)",
COUNTRY_NAME      	"TRIM(:COUNTRY_NAME)",
ABBREVIATION		"TRIM(:ABBREVIATION)",
TRADE_STYLE		"TRIM(:TRADE_STYLE)"
8th column              FILLER,
9th column              FILLER,
.
.
.
.
.
.
99TH column             FILLER,
100th column            
)

or there is any other way.

Report message to a moderator

Re: sqlloader [message #565980 is a reply to message #565978]

Mon, 10 September 2012 04:53

Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

You could try with external table (instead of SQL*Loader), which has its advantages (as well as disadvantages).

Report message to a moderator

Re: sqlloader [message #565982 is a reply to message #565978]

Mon, 10 September 2012 05:09

Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

You have to tell Oracle what it has to do with these fields even if this is to ignore them otherwise how it should know?

Regards
Michel

[Updated on: Mon, 10 September 2012 05:10]

Report message to a moderator

Re: sqlloader [message #565985 is a reply to message #565978]

Mon, 10 September 2012 05:30

bhanupratap.s
Messages: 18
Registered: September 2012
Location: chennai

Junior Member

hi,

Can you explain me from above example.

Report message to a moderator

Re: sqlloader [message #565998 is a reply to message #565985]

Mon, 10 September 2012 06:25

Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

It is anyway the same thing, you have to declare all the fields but just select the ones you want to use.

Regards
Michel

Report message to a moderator

Re: sqlloader [message #566018 is a reply to message #565974]

Mon, 10 September 2012 08:32

joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA

Senior Member

bhanupratap.s wrote on Mon, 10 September 2012 04:55

Hi,

1015298029~PRESTIGE GLOBAL EQUITY SDN BHD~CORP~~1015298029~MY~~~~~~~~~~~~~~~~50 JALAN KHAW SIM BEE

That is not a pipe (|) symbol.

Report message to a moderator

Previous Topic:	oradba in WIN-NT
Next Topic:	importing dump file error

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Feb 11 18:55:30 CST 2025