Home » RDBMS Server » Server Utilities » sql loader how to ignore error due to quotes (oracle 10g)
sql loader how to ignore error due to quotes [message #454296] Wed, 05 May 2010 00:34 Go to next message
anijan
Messages: 5
Registered: April 2010
Junior Member
Hi,

I have control file written like
LOAD DATA
APPEND
INTO TABLE MYTABLE
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS


but i have data in csv file like

660501,1,"0187591","12"PEGHOOKW/SA",,"04/03/2002",

Since there is an extra double quote (denoting inch) in the third column, im getting an error. Is there any way to avoid this error without modifying the csv file.

[EDITED by LF: removed color formatting and applied bold one]

[Updated on: Wed, 05 May 2010 00:58] by Moderator

Report message to a moderator

Re: sql loader how to ignore error due to quotes [message #454304 is a reply to message #454296] Wed, 05 May 2010 00:53 Go to previous messageGo to next message
Michel Cadot
Messages: 67946
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
1/ Do not use the character you enclose the value inside it
2/ Change the character that enclose the values.

Regards
Michel
Re: sql loader how to ignore error due to quotes [message #454308 is a reply to message #454304] Wed, 05 May 2010 00:57 Go to previous messageGo to next message
anijan
Messages: 5
Registered: April 2010
Junior Member
as per requirement we are not supposed to modify the data in the csv file. Please suggest some solution to load the data into the table.
Re: sql loader how to ignore error due to quotes [message #454309 is a reply to message #454308] Wed, 05 May 2010 00:58 Go to previous messageGo to next message
Michel Cadot
Messages: 67946
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Then you can't do it.

Regards
Michel
Re: sql loader how to ignore error due to quotes [message #454314 is a reply to message #454309] Wed, 05 May 2010 01:07 Go to previous messageGo to next message
Littlefoot
Messages: 21687
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Instead of SQL*Loader, create an external table and read CSV file with (PL/)SQL.
Re: sql loader how to ignore error due to quotes [message #454331 is a reply to message #454314] Wed, 05 May 2010 01:46 Go to previous messageGo to next message
anijan
Messages: 5
Registered: April 2010
Junior Member
or is there a way to use trim function to trim first and last quote ( " ) instead of using OPTIONALY ENCLOSED by ' " '

my control file looks like
LOAD DATA
APPEND
INTO TABLE MYTABLE
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(
LOCATION_NAME       CHAR "decode(:LOCATION_NAME,null,' ',:LOCATION_NAME)",
 
Re: sql loader how to ignore error due to quotes [message #454471 is a reply to message #454331] Wed, 05 May 2010 14:07 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8922
Registered: November 2002
Location: California, USA
Senior Member
anijan wrote on Tue, 04 May 2010 23:46

or is there a way to use trim function to trim first and last quote ( " ) instead of using OPTIONALY ENCLOSED by ' " '


Yes. Please see the demo below.

-- control file with data:
LOAD DATA
INFILE *
APPEND
INTO TABLE MYTABLE
FIELDS TERMINATED BY ','
TRAILING NULLCOLS
(col1
,col2
,col3 "trim (both '\"' from :col3)"
,col4 "trim (both '\"' from :col4)"
,col5
,col6 "to_date (trim (both '\"' from :col6), 'mm/dd/yyyy')"
)
begindata
660501,1,"0187591","12"PEGHOOKW/SA",,"04/03/2002",


-- table, load, and results:
SCOTT@orcl_11g> create table mytable
  2    (col1  number,
  3  	col2  number,
  4  	col3  number,
  5  	col4  varchar2(20),
  6  	col5  varchar2(5),
  7  	col6  date)
  8  /

Table created.

SCOTT@orcl_11g> host sqlldr scott/tiger control=test.ctl

SCOTT@orcl_11g> select * from mytable
  2  /

      COL1       COL2       COL3 COL4                 COL5  COL6
---------- ---------- ---------- -------------------- ----- ---------
    660501          1     187591 12"PEGHOOKW/SA             03-APR-02

SCOTT@orcl_11g> 

Re: sql loader how to ignore error due to quotes [message #454476 is a reply to message #454471] Wed, 05 May 2010 15:13 Go to previous messageGo to next message
Littlefoot
Messages: 21687
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
BB

"trim (both '\"' from :col3)"


Well that's really cool /forum/fa/2115/0/ I'll try to memorize it!
Re: sql loader how to ignore error due to quotes [message #454484 is a reply to message #454476] Wed, 05 May 2010 23:20 Go to previous messageGo to next message
Michel Cadot
Messages: 67946
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Yes, but what happent if there is a comma in the value?
Isn't it the purpose of the enclosed character to be able to have the separator one inside the value?

Regards
Michel
Re: sql loader how to ignore error due to quotes [message #454485 is a reply to message #454484] Wed, 05 May 2010 23:42 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8922
Registered: November 2002
Location: California, USA
Senior Member
Michel Cadot wrote on Wed, 05 May 2010 21:20

Yes, but what happent if there is a comma in the value?


Then it would incorrectly see one field as two fields and throw every thing after that off by one column.

Michel Cadot wrote on Wed, 05 May 2010 21:20

Isn't it the purpose of the enclosed character to be able to have the separator one inside the value?


Yes.

If you have a situation where you have both your separator and your enclosure character within your data, then you have a mess that no standard sql*loader or external table and sql commands would fix. You would need to either switch to fixed format or different separators or different enclosures or clean up your data prior to putting it in a csv file or provide some sort of marker that can be used for intelligent parsing or some such thing.

In the past I have had to deal with massive amounts of unstructured unvalidated data from outside sources, where I had no control over the format I got it in. In one of the biggest messes, the input personnel entered whatever data they wanted in whatever fields without regard to what belonged where. I had to resort to various tests to attempt to determine for each chunk of data whether it might be part of an address or phone number or what and extract what I could from it. You just have to do the best you can with what you've got sometimes. One of the things that I frequently did was to try to load the whole thing the way it should load if the data was right, then use the bad file as the infile for the secondary method and so on. As long as you are allowed plenty of time to sit and play with such things, it can result in some interesting experiments. I also used the Levenshtein distance formula for comparing unvalidated data to lookup tables in our system.










Re: sql loader how to ignore error due to quotes [message #454486 is a reply to message #454485] Wed, 05 May 2010 23:49 Go to previous message
Michel Cadot
Messages: 67946
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Thanks Barbara, this was exactly the precision I'd like to have.

Regards
Michel
Previous Topic: Oracle Directory and Datapump
Next Topic: IMP with different language settings
Goto Forum:
  


Current Time: Fri Sep 17 08:19:44 CDT 2021