| sqlldr not loading some records [message #478812] |
Tue, 12 October 2010 12:48  |
recheverri
Messages: 15
Registered: October 2010
|
Junior Member |
|
|
We are loading our csv comma delimited file using the Oracle sqlldr utility, we are getting some records with a double ("") like this one
Participated in school's marching band. Was selected by staff as most improved rookie.""
The applicant is entering the quotation marks in his description of the activity. Since we do not edit the answers before they are exported, you will have to keep on the lookout for responses such as this is you are using, or you will have to change the way these data elements are imported.
Is there a way that I can change the ctl file to avoid this problem?
I have this in the ctl file
LOAD DATA
CHARACTERSET AL32UTF8
INFILE 'application_data.csv'
BADFILE 'application.bad'
DISCARDFILE 'application.dis'
TRUNCATE
INTO TABLE saturn_midd.szscapd
FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
I am attaching the table description:
-
Attachment: SZSCAPD.sql
(Size: 12.17KB, Downloaded 1756 times)
|
|
|
|
|
|
|
|
| Re: sqlldr not loading some records [message #478847 is a reply to message #478812] |
Tue, 12 October 2010 21:36  |
 |
Barbara Boehmer
Messages: 9101
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
You need to provide a simpler complete test case, example, and clearer explanation. For example, just provide the problem column and perhaps two surrounding columns and remove any schema references that we will not have on our systems, provide a few sample rows of problem data and a sample of the results that you want based on that data. I have provided something similar to what we need to see from you below. I am guessing that you have double quotes within the data and perhaps around the data as well. If you have them within your data, then you cannot use optionally enclosed by with them, because Oracle has no way of telling which is the end of the field and which is quotes within the data. You can load the data as is or you can trim the leading and trailing quotes or you can remove all of the quotes. Below I have demonstrated removing the leading and trailing quotes. If you want them, then don't trim them. If you want to remove the quotes in the middle of the data, then use replace.
-- application_data.csv:
col1 data,some col2 data""more col2 data,col3 data
col1 data,"some col2 data""more col2 data",col3 data
-- test.ctl:
LOAD DATA
CHARACTERSET AL32UTF8
INFILE 'application_data.csv'
BADFILE 'application.bad'
DISCARDFILE 'application.dis'
TRUNCATE
INTO TABLE szscapd
FIELDS TERMINATED BY ","
TRAILING NULLCOLS
(col1,
col2 "LTRIM (RTRIM (:col2, CHR (34)), CHR (34))",
col3)
-- table:
SCOTT@orcl_11gR2> CREATE TABLE SZSCAPD
2 (col1 VARCHAR2 (10),
3 col2 VARCHAR2 (35),
4 col3 VARCHAR2 (10))
5 /
Table created.
-- load:
SCOTT@orcl_11gR2> HOST SQLLDR scott/tiger CONTROL=test.ctl LOG=test.log
-- results:
SCOTT@orcl_11gR2> SELECT * FROM szscapd
2 /
COL1 COL2 COL3
---------- ----------------------------------- ----------
col1 data some col2 data""more col2 data col3 data
col1 data some col2 data""more col2 data col3 data
2 rows selected.
SCOTT@orcl_11gR2>
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
