Sql loader: Loading .CSV files with embedded \r\n into oracle

Question

I have a .csv file where data in some columns have one or multi \r\n. I need to load them into oracle without these \r\n but SQLLDR gives an error.

below are the sample of records:

R1:

"A", "B", "
CDE
FGH" , ...

R2:

"A", "B", "
CDE

FGH" , ...

R3:

"A", "B", "C
DEF
GH" , ...

R4:

"A", "B", "C
DE" , ...

I used

continueif last != '"' ,
"STR '\r\n'" and
replace (:ColumnName, chr(13) || chr(10), ' '),

in the control file, but these solutions didn't work. So, How can I handle this?

Edit: Here is header of my control file:

OPTIONS (DIRECT=TRUE)
LOAD DATA
INFILE 'E:test.csv'
INTO TABLE TEMP
FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"'
...

use dos2unix to remove carriage returns before the start of processing. — Kaushik Nayak
@KaushikNayak Records are also splitted with \r\n. What happend If I remove that? (Both \r\n between fields and at the end of the record are deleted) — DAVID_ROA
@DAVID_ROA, the link I posted describes also how to define line break characters, and hence it IS relevant to your question (or so I think). — FDavidov

DAVID_ROA DAVID_ROA · Accepted Answer · 2018-05-19T12:08:22

I found it. I should use : CONTINUEIF NEXT PRESERVE(1) != ' " ', because records starts with ' " ' and As long as the next lines are opposed to ' " ', they all count as one record.

Sql loader: Loading .CSV files with embedded \r\n into oracle

1 Answers