2

I'm trying to import CSV via Oracle SQL*LOADER, but I have a problem because some data has line break within the double-quotes. For example

"text.csv"

John,123,New York
Tom,456,Paris
Park,789,"Europe
London, City"

I think that SQL*LOADER uses the line break character to separate records. This data generates an error "second enclosure string not present"

I use this control file. (control.txt)

OPTIONS(LOAD=-1, ERRORS=-1)
LOAD DATA
INFILE 'test.csv'
TRUNCATE
INTO TABLE TMP
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
(
 field1,
 field2,
 field3
)

and I execute a command like this

sqlldr 'scott/tiger' control='control.txt' log='result.txt'

I want to import 3 records, not 4 records... can SQL*LOADER ignore line breaks within double-quotes?

Barbaros Özhan
  • 59,113
  • 10
  • 31
  • 55
Sncmlife
  • 31
  • 1

2 Answers2

2

Seems you need to get rid of carriage return and/or line feed characters.

So replace

field3 with

field3 CHAR(4000) "REPLACE(TRIM(:field3),CHR(13)||CHR(10))"'

or

field3 CHAR(4000) "REPLACE(REPLACE(TRIM(:field3),CHR(13)),CHR(10))"'

where using a TRIM() would be useful in order to remove trailing and leading whitespaces.

Barbaros Özhan
  • 59,113
  • 10
  • 31
  • 55
1

In case you would like to preserve the embedded carriage returns, construct the control file like this using the "str" (stream) clause on the infile option line to set the end of record character. It tells sqlldr that hex 0D (carriage return, or ^M) is the record separator (this way it will ignore the linefeeds inside the double-quotes):

INFILE 'test.csv' "str x'0D'"
Gary_W
  • 9,933
  • 1
  • 22
  • 40