Skip to Main Content
  • Questions
  • SQL Loader - Load CSV file, double quoted record, dual double quoted field

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, Mukesh.

Asked: March 08, 2016 - 7:19 pm UTC

Last updated: May 27, 2019 - 6:21 am UTC

Version: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

Viewed 10K+ times! This question is

You Asked

Hello,

Please tell me how to write the control file to load following data (without quotes) using sqlldr in three separate data fields.

We get such a csv file from out client and they can't change the way it generates. This file opens fine in MS Excel (no quotes in data).

"991-xxxxx,""CSW xxxx, DATED xxxxx"",0xxx31"

desired insert of data -
field1 - 991-xxxxx
field2 - CSW xxxx, DATED xxxxx
field3 - 0xxx31

As of now, I could load it after removing very first and very last double quote.
991-xxxxx,""CSW xxxx, DATED xxxxx"",0xxx31

-- test
-- table ddl
CREATE TABLE dataxxl
(
val1 VARCHAR2 (20),
val2 VARCHAR2 (60),
val3 VARCHAR2 (20)
)
/

-- control file
LOAD DATA
INFILE 'E:\log\testdata.csv'
TRUNCATE
INTO TABLE dataxxl
FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '""'
TRAILING NULLCOLS
(
field1 "TRIM(:field1)",
field2 "TRIM(:field2)",
field3 "TRIM(:field3)"
)


-- from log
1 Row successfully loaded.

If I do not remove first and last double quote, data still gets loaded but with quotes.

Thanks

and Connor said...

LOAD DATA
INFILE *
TRUNCATE 
INTO TABLE dataxxl
FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '""'
TRAILING NULLCOLS
(
field1 "LTRIM(RTRIM(:field1,chr(34)),chr(34))",
field2 "LTRIM(RTRIM(:field2,chr(34)),chr(34))",
field3 "LTRIM(RTRIM(:field3,chr(34)),chr(34))"
)
BEGINDATA
"991-xxxxx,""CSW xxxx, DATED xxxxx"",0xxx31"


SQL> select * from dataxxl;

FIELD1               FIELD2                                                       FIELD3
-------------------- ------------------------------------------------------------ -------
991-xxxxx            CSW xxxx, DATED xxxxx                                        0xxx31


Rating

  (3 ratings)

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

Comments

It Works

Mukesh Agrawal, April 18, 2016 - 8:02 pm UTC

I see it works. (Sorry for a late response, I missed the update email)

The only concern is, my files are getting fairly large now (<100 M) and have about 300 fields. Using two functions on each and ever field is probably very expensive?

Is there a more efficient way?

Thanks!
Connor McDonald
April 18, 2016 - 11:36 pm UTC

Yes, there will be a cost, but that seems unavoidable.

You could benchmark whether processing the file at the OS level first is more efficient, and then convert the sqlldr process to be an external table with the pre-processor option.

Hope this helps.

Optionally Enclosed by should typically only have single quote like '"'

Stephen, December 13, 2017 - 3:13 pm UTC

Your review missed the users error.
He had two double-quotes rather than a single double-quote in his optionally enclosed by attribute

If using only 1 single quote would likely have fixed his problem.
I encountered this problem before and was able to resolve.

My bad eyes make it hard to see
the difference between '""' and '"'.

Oracle is awesome.
Connor McDonald
December 14, 2017 - 8:05 am UTC

So...let me get this right. You

1) told us we missed something ('He had two double-quotes rather than a single double-quote') when we didn't, because his data DOES contain embedded *repeated* double quotes.

2) suggested an alternative answer ('If using only 1 single quote would likely have
fixed his problem') which you didn't validate

3) if you *had* tested it, you'd see it was wrong

LOAD DATA
INFILE *
TRUNCATE 
INTO TABLE dataxxl
FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"'  <== your suggestion
TRAILING NULLCOLS
(
field1 "LTRIM(RTRIM(:field1,chr(34)),chr(34))",
field2 "LTRIM(RTRIM(:field2,chr(34)),chr(34))",
field3 "LTRIM(RTRIM(:field3,chr(34)),chr(34))"
)
BEGINDATA
"991-xxxxx,""CSW xxxx, DATED xxxxx"",0xxx31"

C:\temp>sqlldr control=x.ctl userid=mcdonac/*******

SQL*Loader: Release 12.2.0.1.0 - Production on Thu Dec 14 15:59:35 2017

Copyright (c) 1982, 2017, Oracle and/or its affiliates.  All rights reserved.

Path used:      Conventional
Commit point reached - logical record count 2

Table DATAXXL:
  0 Rows successfully loaded.     <==== Nope


and yet *we* are the ones that get 1 star review for providing an answer that was correct.

Not sure how that logic works ... but well, there you go.

Merry Christmas to us I suppose

How to implement the same thing in oracle external table

Suman Panigrahi, May 23, 2019 - 12:44 pm UTC

How to implement the same thing in oracle external table:

this part:

"LTRIM(RTRIM(:field1,chr(34)),chr(34))",
Connor McDonald
May 27, 2019 - 6:21 am UTC

No need - once you've defined the external table, you have the full power of SQL

So you can do:

select ltrim(rtrim(col))
from   my_external_table


and of course, you now have the power of any expression you like.