SQL Loader unable to load CSV file data into linux environment correctly

Question

i was able to load the same comma delimited csv file's data into window oracle database correctly but in linux environment, the record being inserted having weird behavior. For example, the data being inserted are having a behavior like \n. i selected the record and paste it out notice that the record is like this

"data
"

the control file i used is as below

Load DATA
REPLACE INTO TABLE TABLE_NM                             
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS

please advice what i can do to make this scenario right. thank you in advance

probably you're using the wrong format. New line character is different in windows and unix. Change the format and try again. — Francesco Serra
– Francesco Serra, Commented Jul 1, 2016 at 11:01
@JSapkota the sample data are as followed a,b,c,d a1,b2,c2,d2 a3,b3,c3,d3 this is what i got for the csv file, this is how it looks like when i try to open the file with notepad++ — Jack
– Jack, Commented Jul 11, 2016 at 7:22

Gary_W · Accepted Answer · 2016-07-01 14:47:14Z

1

Its the classic issue where on *nix systems lines end with a linefeed, but on Windows lines end with a carriage return/linefeed. Since your data ends with carriage return/linefeed it is read fine on Windows, but Linux loads the carriage return.

You can either preprocess the data file and replace the line (record) termination character with a utility like dos2unix or change the control file by adding the STR clause to the INFILE option to set the record termination character to the carriage return:

INFILE "test.dat" "STR x'0D'"

I would opt for running the data through dos2unix to keep the control file more generic and not data filename specific.

edited Jul 1, 2016 at 14:47

answered Jul 1, 2016 at 14:39

Gary_W

10.4k1 gold badge26 silver badges42 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Jack Over a year ago

i did not use INFILE command, where should i put the STR x'OD' thing then?

Racil Hilan · Accepted Answer · 2016-10-12 10:00:31Z

0

After investigation, notice that the root cause of the issues is the feed file is not generated from Linux base environment. So after I manually convert the file into Linux version, the feed file is able to load into DB without any issues.

edited Oct 12, 2016 at 10:00

Racil Hilan

25.4k13 gold badges56 silver badges61 bronze badges

answered Oct 12, 2016 at 9:38

Jack

872 silver badges14 bronze badges

Collectives™ on Stack Overflow

SQL Loader unable to load CSV file data into linux environment correctly

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related