Hi,
I am trying to use SQL*Loader to put data into my table. The data is comma separated and each field has double quotes around it to handle the embedded commas in the data. But my problem is that the data fields can also have double quotes embedded. I tried a solution provided in one of the archives but I am not able to load the data. SQL Loader is throwing an error.
Please advise.
Thanks
Rajesh Nair.
==================
Supporting Data
==================
t.ctl
----------
OPTIONS (SKIP=1)
load data
TRUNCATE
into table t
fields terminated by ','
TRAILING NULLCOLS
(a "TRIM(:a)",
b "TRIM (both '\"' from :b)",
c "TRIM(:c)"
)
t.csv
-----
"column_a","column_b","column_c"
"fdafdad","daaafdaa"fdafdas","1"
"2","test 2","2"
"3","test,3","3"
"4","test,4","4"
SQL> Select * from t;
no rows selected
LOG File
------------
SQL*Loader: Release 8.0.6.3.0 - Production on Wed Sep 6 05:04:53 2017
(c) Copyright 1999 Oracle Corporation. All rights reserved.
Control File: t.ctl
Data File: t.csv
Bad File: t.bad
Discard File: none specified
(Allow all discards)
Number to load: ALL
Number to skip: 1
Errors allowed: 1000000
Bind array: 64 rows, maximum of 90000 bytes
Continuation: none specified
Path used: Conventional
Table T, 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
------------------------------ ---------- ----- ---- ---- ---------------------
A FIRST * , CHARACTER
B NEXT * , CHARACTER
C NEXT * , CHARACTER
Column A had SQL string
"TRIM(:a)"
applied to it.
Column B had SQL string
"TRIM (both '"' from :b)"
applied to it.
Column C had SQL string
"TRIM(:c)"
applied to it.
Record 1: Rejected - Error on table T.
ORA-01008: not all variables bound
Record 2: Rejected - Error on table T.
ORA-01008: not all variables bound
Record 3: Rejected - Error on table T.
ORA-01008: not all variables bound
Record 4: Rejected - Error on table T.
ORA-01008: not all variables bound
Table T:
0 Rows successfully loaded.
4 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: 49536 bytes(64 rows)
Space allocated for memory besides bind array: 0 bytes
Total logical records skipped: 1
Total logical records read: 4
Total logical records rejected: 4
Total logical records discarded: 0
Run began on Wed Sep 06 05:04:53 2017
Run ended on Wed Sep 06 05:04:53 2017
Elapsed time was: 00:00:00.17
CPU time was: 00:00:00.00
SQL*Loader: Release 8.0.6.3.0 - ProductionAre you really using version
8 of SQL Loader?!
If so you should probably upgrade. Your file works fine for me:
create table t (
a varchar2(30),
b varchar2(30),
c varchar2(30)
);
C:\Users\csaxon\Documents\Scripts>type sqlldr.ctl
OPTIONS (SKIP=1)
load data
infile *
TRUNCATE
into table t
fields terminated by ','
TRAILING NULLCOLS
(a "TRIM(:a)",
b "TRIM (both '\"' from :b)",
c "TRIM(:c)"
)
begindata
"column_a","column_b","column_c"
"fdafdad","daaafdaa"fdafdas","1"
"2","test 2","2"
"3","test,3","3"
"4","test,4","4"
C:\Users\csaxon\Documents\Scripts>sqlldr userid=chris/xxxx@db control=sqlldr.ctl
SQL*Loader: Release 12.2.0.1.0 - Production on Wed Sep 6 17:59:38 2017
Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.
Path used: Conventional
Commit point reached - logical record count 3
Commit point reached - logical record count 4
Table T:
4 Rows successfully loaded.
Check the log file:
sqlldr.log
for more information about the load.