You Asked
Tom,
Hopefully I have provided all the statements you'll need to duplicate this problem below. I am trying to use SQL*Loader to put data into my table. The data is comma separated and each data field has double quotes around it. The double quoting is done because some of the fields have embedded commas. SQL*Loader seems to strip off the quotes first and then separate into fields which means that any embedded commas throw things off by one.
The sample data file (2 records) has the first record with no embedded commas, the second record with an embedded comma in the address field.
Are there any workarounds for this problem?
Thanks,
Steve
========================
supporting data below
========================
CREATE TABLE Import_Test (
IT_ID number(6) NOT NULL,
FNAME varchar2(10) NULL,
LNAME varchar2(10) NULL,
ADDRESS varchar2(30) NULL,
EXTRA_1 varchar2(10) NULL,
EXTRA_2 varchar2(10) NULL,
CONSTRAINT PK_Import_Test PRIMARY KEY (IT_ID)
)
/
CREATE SEQUENCE Seq_Import_Test_IT_ID
START WITH 1
INCREMENT BY 1
ORDER
NOCYCLE
/
CREATE TRIGGER Trg_Import_Test_IT_ID
BEFORE INSERT ON Import_Test
FOR EACH ROW
DECLARE
next_id NUMBER;
BEGIN
SELECT Seq_Import_Test_IT_ID.NEXTVAL
INTO next_id FROM DUAL;
:new.IT_ID := next_id;
END;
/
==============================================================
-- quoted, comma separated data
"FName","LName","Address","Extra_1","Extra_2"
"A","Smith","Address 1","e1e1e1e1e1","e2e2e2e2e2"
"B","Jones","Address 2, Suite 100","e1e1e1e1e1","e2e2e2e2e2"
==============================================================
-- SQL*Loader control file sqlload_1234.ctl
LOAD DATA
INFILE '/sqlload/quoted_csv.txt'
BADFILE '/sqlload/sqlload_1234.bad'
DISCARDFILE '/sqlload/sqlload_1234.dsc'
Truncate
INTO TABLE IMPORT_TEST
FIELDS TERMINATED BY ','
TRAILING NULLCOLS
(fname "rtrim(trim( chr(34) from :fname),chr(10)||chr(13)|| chr(34) )" ,
lname "rtrim(trim( chr(34) from :lname),chr(10)||chr(13)|| chr(34) )" ,
address "rtrim(trim( chr(34) from :address),chr(10)||chr(13)|| chr(34) )" ,
extra_1 "rtrim(trim( chr(34) from :extra_1),chr(10)||chr(13)|| chr(34) )" ,
extra_2 "rtrim(trim( chr(34) from :extra_2),chr(10)||chr(13)|| chr(34) )"
)
==============================================================
$ORACLE_BIN/sqlldr UID\@DSN/PWD control=sqlload/sqlload_1234.ctl log=sqlload/sqlload_1234.log
skip=1 ERRORS=999999999
==============================================================
-- sqlload_1234.log
SQL*Loader: Release 8.1.7.0.1 - Production on Fri Sep 17 10:38:12 2004
(c) Copyright 2000 Oracle Corporation. All rights reserved.
Control File: /sqlload/sqlload_1234.ctl
Data File: /sqlload/quoted_csv.txt
Bad File: /sqlload/sqlload_1234.bad
Discard File: /sqlload/sqlload_1234.dsc
(Allow all discards)
Number to load: ALL
Number to skip: 1
Errors allowed: 999999999
Bind array: 64 rows, maximum of 65536 bytes
Continuation: none specified
Path used: Conventional
Table IMPORT_TEST, loaded from every logical record.
Insert option in effect for this table: TRUNCATE
TRAILING NULLCOLS option in effect
Column Name Position Len Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
FNAME FIRST * , CHARACTER
SQL string for column : "rtrim(trim( chr(34) from :fname),chr(10)||chr(13)|| chr(34) )"
LNAME NEXT * , CHARACTER
SQL string for column : "rtrim(trim( chr(34) from :lname),chr(10)||chr(13)|| chr(34) )"
ADDRESS NEXT * , CHARACTER
SQL string for column : "rtrim(trim( chr(34) from :address),chr(10)||chr(13)|| chr(34) )"
EXTRA_1 NEXT * , CHARACTER
SQL string for column : "rtrim(trim( chr(34) from :extra_1),chr(10)||chr(13)|| chr(34) )"
EXTRA_2 NEXT * , CHARACTER
SQL string for column : "rtrim(trim( chr(34) from :extra_2),chr(10)||chr(13)|| chr(34) )"
Table IMPORT_TEST:
2 Rows successfully loaded.
0 Rows not loaded due to data errors.
0 Rows not loaded because all WHEN clauses were failed.
0 Rows not loaded because all fields were null.
Space allocated for bind array: 64500 bytes(50 rows)
Space allocated for memory besides bind array: 0 bytes
Total logical records skipped: 1
Total logical records read: 2
Total logical records rejected: 0
Total logical records discarded: 0
Run began on Fri Sep 17 10:38:12 2004
Run ended on Fri Sep 17 10:38:14 2004
Elapsed time was: 00:00:02.65
CPU time was: 00:00:00.01
==============================================================
SQL> select * from import_test;
IT_ID FNAME LNAME ADDRESS EXTRA_1 EXTRA_2
--------- ---------- ---------- ------------------------------ ---------- ----------
1 A Smith Address 1 e1e1e1e1e1 e2e2e2e2e2
2 B Jones Address 2 Suite 100 e1e1e1e1e1
and Tom said...
You don't need sql functions, you just want "optionally enclosed by"
ops$tkyte@ORA9IR2> select * from import_test;
no rows selected
ops$tkyte@ORA9IR2> !sqlldr / t
SQL*Loader: Release 9.2.0.5.0 - Production on Fri Sep 17 14:50:40 2004
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Commit point reached - logical record count 3
ops$tkyte@ORA9IR2> select * from import_test;
IT_ID FNAME LNAME ADDRESS EXTRA_1
---------- ---------- ---------- ------------------------------ ----------
EXTRA_2
----------
1 FName LName Address Extra_1
Extra_2
2 A Smith Address 1 e1e1e1e1e1
e2e2e2e2e2
3 B Jones Address 2, Suite 100 e1e1e1e1e1
e2e2e2e2e2
ops$tkyte@ORA9IR2> !cat t.ctl
LOAD DATA
INFILE *
Truncate
INTO TABLE IMPORT_TEST
FIELDS TERMINATED BY ',' optionally enclosed by '"'
TRAILING NULLCOLS
(fname ,
lname ,
address ,
extra_1 ,
extra_2
)
begindata
"FName","LName","Address","Extra_1","Extra_2"
"A","Smith","Address 1","e1e1e1e1e1","e2e2e2e2e2"
"B","Jones","Address 2, Suite 100","e1e1e1e1e1","e2e2e2e2e2"
Rating
(9 ratings)
Is this answer out of date? If it is, please let us know via a Comment