| sql loader how to ignore error due to quotes [message #454296] |
Wed, 05 May 2010 00:34  |
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 #454331 is a reply to message #454314] |
Wed, 05 May 2010 01:46  |
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 |
 |
Barbara Boehmer
Messages: 9101
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 #454485 is a reply to message #454484] |
Wed, 05 May 2010 23:42 |
|
Barbara Boehmer
Messages: 9101
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.
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
