Search code examples
sqloraclesql-loader

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

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 '"'
...
Solution

  • 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.