Sql loader - second enclosure string not present

Question

I am loading a .csv file data into oracle table through sql loader. One of the fields has a new line character (CRLF) in its data and so, am getting the below error:

second enclosure string not present

This is my control file

load data
characterset UTF8
infile 'C:\Users\lab.csv'
truncate 
into table test_labinal
fields terminated by ";" optionally enclosed by '"' 
TRAILING NULLCOLS
      ( 

    STATEMENT_STATUS ,
    MANDATORY_TASK ,
    COMMENTS CHAR(9999)  "SubStr(:Comments, 0, 1000)"
)

The field COMMENTS has a new line character in one of its records. Can any one suggest a solution for this.

Thanks

Answer 1

If your last field is always present (though trailing nullcols suggests it isn't) and you have some control over the formatting, you can use the CONTINUEIF directive to treat the second line as part of the same logical record.

If the comments field is always present and enclosed in double-quotes then you can do:

...
truncate
continueif last != x'22'
into table ...

Which would handle data records like:

S;Y;"Test 1"
F;N;"Test 2"
P;Y;"Test with
new line"
P;N;""

Or if you always have a delimiter after the comments field, whether it is populated or not:

...
truncate
continueif last != ';'
into table ...

Which would handle:

S;Y;Test 1;
F;N;"Test 2";
P;Y;Test with
new line;
P;N;;

Both ways will load the data as:

S M COMMENTS
- - ------------------------------
S Y Test 1
F N Test 2
P Y Test withnew line
P N

But this loses the new line from the data. To keep that you need the terminating field delimiter to be present, and instead of CONTINUEIF you can change the record separator using the stream record format:

...
infile 'C:\Users\lab.csv' "str ';\n'"
truncate
into table ...

The "str ';\n'" defines the terminator as the combination of the field terminator and a new line character. Your split comment only has that combination on the final line. With the same data file as the previous version, this gives:

S M COMMENTS
- - ------------------------------
S Y Test 1
F N Test 2
P Y Test with
    new line

P N

4 rows selected.

Since you're on Windows you might have to include \r in the format as well, e.g. "str ';\r\n'", but I'm not able to check that.