CODEWITHSUNDEEP
oracle-databasesql-loader
user1363308
user1363308

Reputation: 968

Sqlloader miss the double quote while loading data into database

I have text file and try to load into oracle database and it is able to load into oracle database successfully.Whenever it loads into oracle database it ignore the double quote(") which is within around double quotes. 

Current sql loader status

input file: 
 "A",165,"TAKE ONE IN THE MORNING (ROUND BLUE TABLET, MARKED ""LT"")"
 output file: 
 "A",165,"TAKE ONE IN THE MORNING (ROUND BLUE TABLET, MARKED "LT")"

Expected output sql loader status

input file: 
 "A",165,"TAKE ONE IN THE MORNING (ROUND BLUE TABLET, MARKED ""LT"")"
 output file: 
 "A",165,"TAKE ONE IN THE MORNING (ROUND BLUE TABLET, MARKED ""LT"")"

Control file :-

load data
CHARACTERSET UTF8
into table TK_000153_2016052029235
fields terminated by "," optionally enclosed by '"' TRAILING NULLCOLS
(Status,Code,Comment)

Table status :-

   Status   Code  Comment
    A       165   TAKE ONE IN THE MORNING (ROUND BLUE TABLET, MARKED "LT")

Upvotes: 2

Views: 5869

Answers (1)

Gary_W
Gary_W

Reputation: 10360

My understanding is you want to load the data into the table while preserving the double-quotes. Since the delimiter is a comma, and the comment field could contain a comma, you need to use the optionally enclosed by '"' clause which has the side effect of not only removing the surrounding double-quotes from around the entire comment but causes sqlldr to interpret the contiguous double-quotes inside the comment as a "quoted" double-quote and reduces them to one. If these assumptions are correct, please read on.

For fields like status, where the field will not contain a comma, make the control file add the double-quotes by concatenating them back on. Note you need to escape them from sqlldr.

For the comment field, it's a tad trickier as you'll need to also replace single double-quotes (inside the comment, which will be AFTER they have been reduced to one due to the actions of the optionally enclosed by clause) with 2 double-quotes before tacking the double-quotes on to each end.

load data
CHARACTERSET UTF8
into table TK_000153_2016052029235
fields terminated by "," optionally enclosed by '"' TRAILING NULLCOLS
( Status " '\"' || :Status || '\"' "
 ,Code
 ,Comment " '\"' || replace(:Comment , '\"', '\"\"') || '\"' "
)

This is kind of ugly but I believe if I understand what you are wanting to do will load the data while keeping the double-quotes as you expect, while keeping it in the sqlldr part of your processing.

Well, technically you are not really "keeping" the double-quotes, but adding them back where you want them after the "optionally enclosed by" clause removes them.

Upvotes: 1

Related Questions