Skip to Main Content
  • Questions
  • Loading a Double Quote enclosed csv file into Table where csv file has Newline character itself in the enclosed data

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Soumya.

Asked: April 07, 2021 - 11:45 am UTC

Last updated: April 13, 2021 - 1:42 pm UTC

Version: 18c

Viewed 100+ times

You Asked

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

and we said...

You have a couple of options:

- Use the STR option of INFILE to specify different end-of-record characters (e.g. #EOR#)

https://asktom.oracle.com/pls/apex/f?p=100:11:0::NO::P11_QUESTION_ID:9533874900346022467
https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:9529759800346052607

- Use CONTINUEIF to combine logical records into one

If you're sure a multi-line record only has double quotes at the end of the final line in the record and not the intermediate lines, you could:

CONTINUEIF LAST != '"'


Your example does have this though in record 7, so this may be unworkable for you.

For more discussion on this see:

https://asktom.oracle.com/pls/apex/asktom.search?tag=sql-loader-not-loading-all-the-needed-rows-due-to-new-line-character-and-enclosement-character