Home » RDBMS Server » Server Utilities » sqlloader
sqlloader [message #565970] Mon, 10 September 2012 03:21 Go to next message
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)"
)

Re: sqlloader [message #565972 is a reply to message #565970] Mon, 10 September 2012 03:38 Go to previous messageGo to next message
John Watson
Messages: 4093
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.
Re: sqlloader [message #565973 is a reply to message #565970] Mon, 10 September 2012 03:39 Go to previous messageGo to next message
dariyoosh
Messages: 513
Registered: March 2009
Location: Iran / 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 Go to previous messageGo to next message
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.





Re: sqlloader [message #565977 is a reply to message #565974] Mon, 10 September 2012 04:31 Go to previous messageGo to next message
Michel Cadot
Messages: 57615
Registered: March 2007
Location: Nanterre, France, http://...
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 Go to previous messageGo to next message
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.




Re: sqlloader [message #565980 is a reply to message #565978] Mon, 10 September 2012 04:53 Go to previous messageGo to next message
Littlefoot
Messages: 18834
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).
Re: sqlloader [message #565982 is a reply to message #565978] Mon, 10 September 2012 05:09 Go to previous messageGo to next message
Michel Cadot
Messages: 57615
Registered: March 2007
Location: Nanterre, France, http://...
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 Go to previous messageGo to next message
bhanupratap.s
Messages: 18
Registered: September 2012
Location: chennai
Junior Member

hi,

Can you explain me from above example.
Re: sqlloader [message #565998 is a reply to message #565985] Mon, 10 September 2012 06:25 Go to previous messageGo to next message
Michel Cadot
Messages: 57615
Registered: March 2007
Location: Nanterre, France, http://...
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
Re: sqlloader [message #566018 is a reply to message #565974] Mon, 10 September 2012 08:32 Go to previous message
joy_division
Messages: 4454
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.
Previous Topic: oradba in WIN-NT
Next Topic: importing dump file error
Goto Forum:
  


Current Time: Mon Apr 21 03:42:00 CDT 2014

Total time taken to generate the page: 0.07719 seconds