Sqlldr- No terminator found after terminated and e

问题:

I use Oracle 11g.
My data file looks like below:

1|"\a\ab\"|"do not "clean" needles"|"@"

2|"\b\bg\"|"wall "69" side to end"|"@"

My control file is:

load data
infile 'short.txt'
CONTINUEIF LAST <> '"'
into table "PORTAL"."US_FULL" 
fields terminated by "|" OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(
  u_hlevel,
  u_fullname NULLIF u_fullname=BLANKS,
  u_name char(2000)  NULLIF c_name=BLANKS ,
  u_no NULLIF u_no=BLANKS
)

While loading data through sqlldr, a .bad file is created and .log file contains error message stating "No terminator found after terminated and enclosed field"

Double quotes starting and ending are not in my data, however I would need double quotes withing the data like in above example surrounding clean and 69. Ex: My data file after loading should look like:

1, \a\ab\, do not "clean" needles, @

2, \b\bg\ , wall "69" side to end , @

How to accomplish this?

回答1:

I'm afraid since the fields are surrounded by double-quotes the double-quotes you want to preserve need to be escaped by adding another double-quote in front like this:

1|"\a\ab\"|"do not ""clean"" needles"|"@"

Alternately if you can get the data without the fields being surrounded by double-quotes, this would work too:

1|\a\ab\|do not "clean" needles|@

If you can't get the data provider to format the data as needed (i.e. search for double-quotes and replace with 2 double-quotes before extracting to the file), you will have to pre-process the file to set up double quotes one of these ways so the data will load as you expect.

回答2:

Asking your provider to correct the data file may not be an option, but I ultimately found a solution that requires you to update your control file slightly to specify your "enclosed by" character for each field instead of for all fields.

For my case, I had an issue where if [first_name] field came in with double-quotes wrapping a nickname it would not load. (EG: Jonathon "Jon"). In the data file the name was shown as "Jonathon "Jon"" . So the "enclosed by" was throwing an error because there were double quotes around the value and double quotes around part of the value ("Jon"). So instead of specifying that the value should be enclosed by double quotes, I omitted that and just manually removed the quotes from the string.

Load Data
APPEND
INTO TABLE MyDataTable
fields terminated by ","     ---- Noticed i omitted the "enclosed by"
TRAILING NULLCOLS
(
  column1 enclosed by '"',   --- Specified "enclosed by" here for all cols
  column2 enclosed by '"',
  FIRST_NAME "replace(substr(:FIRST_NAME,2, length(:FIRST_NAME)-2), chr(34) || chr(34), chr(34))", -- Omitted "enclosed by".  substr removes doublequotes, replace fixes double quotes showing up twice.  chr(34) is charcode for doublequote
  column4 enclosed by '"',
  column5 enclosed by '"'
)