How to retain double quotes in a column while loading a file using SQL Loader

Question

I am trying to load a txt file with | (pipe) delimiter to an Oracle table via SQL loader utility. All the fields are enclosed with double quotes. But there are some text fields in the files that have additional double quotes in addition to the enclosed ones that needs to be retained. All the table columns are defined as VARCHAR. Here's the control parameters am using

OPTIONS (DIRECT=TRUE,SKIP=1)
LOAD DATA
CHARACTERSET UTF8
INFILE aaa.txt
APPEND INTO TABLE info_table
FIELDS TERMINATED BY "|"
OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS

This is my sample file

"1"|"High "Gold Tip" Tea, 600"
"2"|""10000 Beers, Wines & Spirits""

Table should be loaded with the below details

Record 1:

Column 1 - 1
Column 2 - High "Gold Tip" Tea, 600

Record 2:

Column 1 - 2
Column 2 - 10000 Beers, Wines & Spirits

Littlefoot Littlefoot · Accepted Answer · 2020-04-28T10:34:15

Unfortunately, there's nothing much to be said.

File format is bad. You can't enclose values into characters that are used in those fields themselves. As data contain double quotes, you'll have to optionally enclose values into something else, not double quotes.

However, as you already split values with pipe characters, what do you need double quotes to optionally enclose those field values? Omit them from the file and you won't have any problem (of such kind, of course; who knows what might come next, but that's another story).

How to retain double quotes in a column while loading a file using SQL Loader

1 Answers