Hi Sir,

I have a csv (Say Dummy.csv) file like the below :


"D_ID","D_DATA_A","D_DATA_B"
"1","Normal Data","This is a Dummy Data"
"2","Data with Comma","This is a Dummy Data, with Comma"
"3","Data with New Line","This is a Dummy Data
Along with New Line"
"4","Data with Double Quote","This is a ""Dummy Data"" with Double Quote"
"5","Data with all of the above","This is a ""Dummy Data
"" with Double Quote,
and Comma
With Newline"
"6","Test Data with Null",""
"7","Data with really big dummy data","ABCD , EFGH "" IJKL
MNOP"" QRST , UVWX""
""YZ AB, ""CDEF"",""GHIJ
KL

.
.

MNOP, QRST "" UVWX"

Now I need to load this in a table say Dummy_Load table having the definition :

D_ID NUMBER(2)
D_DATA_A VARCHAR2(100)
D_DATA_B VARCHAR2(1000)

While using sqlldr for this with the control file as follows:

LOAD DATA
INFILE '<root_path>/Dummy.csv'
INSERT INTO TABLE Dummy_Load
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(
d_id, 
d_data_a, 
d_data_b
)

all the rows are not getting loaded due to the new line characters throwing the below error in log

Record 5: Rejected - Error on table DUMMY_LOAD, column D_DATA_B.
second enclosure string not present
Record 8: Rejected - Error on table DUMMY_LOAD, column D_DATA_B.
second enclosure string not present
Record 9: Rejected - Error on table DUMMY_LOAD, column D_ID.
no terminator found after TERMINATED and ENCLOSED field
Record 12: Rejected - Error on table DUMMY_LOAD, column D_DATA_B.
second enclosure string not present
Record 14: Rejected - Error on table DUMMY_LOAD, column D_ID.
no terminator found after TERMINATED and ENCLOSED field
Record 16: Discarded - all columns null.
Record 21: Discarded - all columns null.
Record 22: Discarded - all columns null.
Record 23: Discarded - all columns null.


What am I really missing here? Is it not possible to import a csv where newline is present in the data itself in table?

Thanks in advance.

Regards,

Soumya

CONTINUEIF LAST != '"'