Re: SQL*Loader and Double Quotes

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

Angus Tracy wrote:
>
> 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))" )
Received on Tue Mar 04 2003 - 18:20:58 CST