SQL*Loader and Double Quotes

Angus Tracy

unread,

Mar 4, 2003, 5:11:19 AM3/4/03

to

I'm having problems loading data containing double quotes using
SQL*Loader.

For example, in the following two lines of my data file, the second
one is being rejected.

AB3019A2,"CAMBRIDGE ROAD"
GR7013B5,"NEXT TO THE"RED LION""

I've tried a few things already, including using the REPLACE function
as shown in the control file below, but not had much success. Does
anyone have any suggestions ?

OPTIONS (DIRECT=FALSE,SKIP=3,READSIZE=1000000,ERRORS=1000000,BINDSIZE=1000000,ROWS=1000,SILENT=(ALL))
LOAD DATA
REPLACE
INTO TABLE RAW_ADDRESS
FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(S_REF CHAR
,STREET CHAR "REPLACE(:STREET, CHR(34))" )

Olav M.J. Christiansen

unread,

Mar 4, 2003, 5:31:03 AM3/4/03

to

"Angus Tracy" <angus...@ntlworld.com> wrote in a message
news:f22fbfbc.03030...@posting.google.com...

> I'm having problems loading data containing
> double quotes using SQL*Loader.

[snip]

Have you tried replacing the inner double quotes with single quotes?

--
Regards
Olav
http://www.experit.dk

FC

unread,

Mar 4, 2003, 1:44:17 PM3/4/03

to

For what I've learnt from personal experience with SQL*Loader,
the best options you have are as follows:

1. do not enclose strings using double quotes, choose another character or
combination of characters, for instance the 'pipe' character ( | ). Of
course this is prone to further problems if your file includes the pipe
character in some place, other than expected.

2. change any occurrence of the double quotes inside the strings by
transforming them into something like "<quote>" or any other tag of your own
choosing and replace it during the data load using the SQL REPLACE function.
This is probably the more "robust" solution, albeit a bit more expensive in
terms of performance.

The problem is SQL Loader does not handle as you expect the double quotes,
it starts from the first occurrence and when it arrives at the second quote
character interprets it as the closing character, thereafter it raises an
error because it didn't find the record terminator or the field terminator,
whichever comes first. As far as I know there is no workaround apart from
changing in some way the input file format.

Bye,
Flavio

"Angus Tracy" <angus...@ntlworld.com> wrote in message
news:f22fbfbc.03030...@posting.google.com...

Angus Tracy

unread,

Mar 4, 2003, 2:44:20 PM3/4/03

to

> Have you tried replacing the inner double quotes with single quotes?

If you mean in the data file, then no.
This is not a one-off load operation, and therefore I would like to
find a way to solve it in SQL*Loader, rather than manually edit the
data file as you suggest.

Richard Kuhler

unread,

Mar 4, 2003, 7:20:58 PM3/4/03

to

It's worth noting that that's not a valid CSV file. Try and load it
into Excel and you won't get the right results either. To have an
embedded double quote within a quote enclosed field you have to escape
the double quotes by using two double quotes (similar to SQL and single
quotes). The valid version of that data should be ...

AB3019A2,"CAMBRIDGE ROAD"
GR7013B5,"NEXT TO THE""RED LION"""

With that data, the SQL*Loader control file you have should work fine.
Now if you're saying you can't get the producer of that file to conform
to the CSV standard then I guess you've got some problems.

Good Luck,
Richard