SQL Loader - needs to load , (comma) from , (Comma) separated file [message #260602] |
Mon, 20 August 2007 07:41 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
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 message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.orafaq.com/forum/images/custom_avatars/72104.gif) |
Littlefoot
Messages: 21818 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 #261129 is a reply to message #260797] |
Tue, 21 August 2007 22:36 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) |
rleishman
Messages: 3728 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
|
|
|