Home » RDBMS Server » Server Utilities » SQL Loader - needs to load , (comma) from , (Comma) separated file
SQL Loader - needs to load , (comma) from , (Comma) separated file [message #260602] Mon, 20 August 2007 07:41 Go to next message
rahul_bahulekar
Messages: 18
Registered: January 2007
Junior Member
Hi All,

I am loading data from , (comma) separated file using SQL Loader. But for few records , (Comma) is the part of the record like –

1450,DV2Y44,VIDEOS "R" US, LTD.,1987-05-08 00:00:00,V320,VIDEOSRUSLTD

In above mention example, (comma) is the part of the name which is VIDEOS "R" US, LTD.

Plz let me know how I can load these types of records.

Regards

Rahul Bahulekar
Re: SQL Loader - needs to load , (comma) from , (Comma) separated file [message #260609 is a reply to message #260602] Mon, 20 August 2007 07:58 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Could you change 'comma' into, for example, 'semi-colon' separated values file? If not, fixed record length could also help.

Having records as you do makes things difficult as it isn't easy to decode which comma separates columns and which does not. Perhaps SQL*Loader can do that, but I wouldn't know how.

Other option might be external table, where you'd parse the whole record and - depending on "VIDEOS "R" US, LTD." (and similar) string's environment (string in front of it, date behind) write a PL/SQL which would extract required data.
Re: SQL Loader - needs to load , (comma) from , (Comma) separated file [message #260795 is a reply to message #260609] Mon, 20 August 2007 23:23 Go to previous messageGo to next message
rahul_bahulekar
Messages: 18
Registered: January 2007
Junior Member
thankx for the reply...!!!

but instead of writing PL/SQL can't we manage these sort of records from SQL Loader itself??

is there any parameter we can pass in the control (SQL Loader) file for these type of requirement???
Re: SQL Loader - needs to load , (comma) from , (Comma) separated file [message #260797 is a reply to message #260602] Mon, 20 August 2007 23:37 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
>is there any parameter we can pass in the control (SQL Loader) file for these type of requirement???
The parameter is make_sqlldr_know_difference_between_valid_coma_and_not=true
Re: SQL Loader - needs to load , (comma) from , (Comma) separated file [message #261129 is a reply to message #260797] Tue, 21 August 2007 22:36 Go to previous message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
The generally accepted standard for CSV is to enclose fields that may contain delimites (say in double-quotes) and to double embedded enclosure characters. SQL*Loader supports this standard.
1450,DV2Y44,"VIDEOS ""R"" US, LTD.",1987-05-08 00:00:00,V320,VIDEOSRUSLTD


Ross Leishman
Previous Topic: SQL Loader - Can I use 'fix11' through command line instead of INFILE?
Next Topic: Trasporting data between servers
Goto Forum:
  


Current Time: Sat Dec 10 18:43:03 CST 2016

Total time taken to generate the page: 0.07172 seconds