Skip to Main Content
  • Questions
  • Quoted csv file into a table using SQL Loader.

Breadcrumb

May 4th

Question and Answer

Chris Saxon

Thanks for the question, Rajesh.

Asked: September 06, 2017 - 3:10 am UTC

Last updated: September 07, 2017 - 11:56 am UTC

Version: Oracle Database 10g Enterprise Edition Release 10.2.0.2.0

Viewed 10K+ times! This question is

You Asked

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    



and Chris said...

SQL*Loader: Release 8.0.6.3.0 - Production

Are 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.

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Thanks for the response, still an issue with data in SQL Loader

Rajesh Nair, September 07, 2017 - 4:29 am UTC

Hi,

Thanks for the response. Checked with a new version of sql*loader and now I am not getting the error, but the data is not getting loaded properly as required.

SQL> select * from t;
A B C
---------- ---------------- ---
"fdafdad" daaafdaa"fdafdas "1"
"2" test 2 "2"
"3" test 3"
"4" test 4"

If you check record 3 and 4,
"3","test,3","3"
"4","test,4","4"

Column B value for record 3 should be test,3
column C value for record 3 should be "3"

Column B value for record 4 should be test,4
column C value for record 4 should be "4"

The loader identified " as field separator for these 2 lines

Please advise.




Chris Saxon
September 07, 2017 - 11:56 am UTC

More to Explore

Utilities

All of the database utilities are explained in the Utilities guide.