SQL*Loader cannnot load a CSV file that contains multiple lines in one field

Question

I have a CSV file that I'm trying to load into an Oracle table, using SQL*Loader:

a, ab, abcdefg
b, bc, bcdefghij
c, cd, cdefghijk-lmnop
orstuv
wxyz
d, de, defghijk

On row "c" the 3rd column has multiple lines, and when we load this row with sqlldr it cannot load this column into the table - it puts it into the bad file.

I tried to include the quote string for some column like this

a, ab, "abcdefg"
b, bc, "bcdefghij"
c, cd, "cdefghijk-lmnop
orstuv
wxyz"
d, de, "defghijk"

I tried the control file in many formats, including:

load data
CHARACTERSET TH8TISASCII
infile '/data/csv/test3.txt'
APPEND 
PRESERVE BLANKS
into table ITEM
fields terminated by ","
TRAILING NULLCOLS
( col1,
  col2,
  col3,
  col4)

and:

load data
CHARACTERSET TH8TISASCII
infile '/data/csv/test3.txt' "str '\n'"
APPEND 
PRESERVE BLANKS
into table ITEM
fields terminated by ","
TRAILING NULLCOLS
( col1,
  col2,
  col3,
  col4)

and:

load data
CHARACTERSET TH8TISASCII
infile '/data/csv/test3.txt'
APPEND 
PRESERVE BLANKS
into table ITEM
fields terminated by "," optionally enclosed by '"'   
TRAILING NULLCOLS
( col1,
  col2,
  col3,
  col4)

and:

load data
CHARACTERSET TH8TISASCII
infile '/data/csv/test3.txt'
APPEND 
PRESERVE BLANKS
into table ITEM
fields terminated by "," optionally enclosed by '\n' 
TRAILING NULLCOLS
( col1,
  col2,
  col3,
  col4)

But it still doesn't work.

How can I load data from that CSV format?

---

According to Gary_W recommended I tried to add the "str x'0D'" and data can load into the column correctly but it just load only the first row of the CSV file and return the log successfully as below

load data
CHARACTERSET TH8TISASCII
infile '/data/csv/b.csv' "str x'0D'"
APPEND
PRESERVE BLANKS
into table ITEM2
fields terminated by "," optionally enclosed by '"' 
TRAILING NULLCOLS
( col1,
  col2,
  col3,
  col4
)


--Sample CSV file
c, cd, "cdefghijk-lmnop
orstuv
wxyz"
,c
a, ab, "abcdefg", a
b, bc, "bcdefghij", b
d, de, "defghijk",d


--sql loader command
sqlldr USER/PASSWORD readsize=2000000000 bindsize=2000000000 control=file_pointing2.ctl > /data/csv/sqlldr.log

###########################LOG########################
Table ITEM:
1 Row successfully loaded.
0 Rows not loaded due to data errors.
0 Rows not loaded because all WHEN clauses were failed.
0 Rows not loaded because all fields were null.

there have only 1 record.
###########################OUT_PUT########################
col1| col2|         col3            | col4
cd  | cd  |efghijk-lmnoporstuvwxyz  | c

but if I remove the option "str x'0D'", in a case of the normal CSV format all row can load currently expect the abnormal row.

load data
CHARACTERSET TH8TISASCII
infile '/data/csv/b.csv' "str x'0D'"
APPEND
PRESERVE BLANKS
into table ITEM2
fields terminated by "," optionally enclosed by '"' 
TRAILING NULLCOLS
( col1,
  col2,
  col3,
  col4
)
--Sample CSV file
c, cd, "cdefghijk-lmnop
orstuv
wxyz"
,c
a, ab, "abcdefg", a
b, bc, "bcdefghij", b
d, de, "defghijk",d



--sql loader command
sqlldr USER/PASSWORD readsize=2000000000 bindsize=2000000000 control=file_pointing2.ctl > /data/csv/sqlldr.log


there have 4 records.but the record 1(col3) is not correct
###########################OUT_PUT########################
col1| col2|         col3            | col4
c   | cd  |efghijk-lmnop            | c
a   | ab  |abcdefg                  | a
b   | bc  |bcdefghij                | b
d   | de  |defghijk                 | d

Answer 1

Put double-quotes around the third column and you'll need to use the streaming clause on the INFILE line to tell sqlldr the carriage return is the end of record character so line feeds are ignored inside of the double quotes:

infile '/data/csv/test3.txt' "str x'0D'" 

Since I already explained it all in this post, please have a look there for more information: https://stackoverflow.com/a/37216660/2543416