no terminator found after TERMINATED and ENCLOSED field

Question

I am trying to load some data into Oracle DB using Oracle Sql Loader utility, but I am getting the 'no terminator found after TERMINATED and ENCLOSED field' exception for the case below:

My sqlldr control file is:

load data
append
into table fp_metadata
fields terminated by "|" optionally enclosed by '"'
TRAILING NULLCOLS
(
 TABLE_NAME CHAR(30),
 FIELD_NAME CHAR(60),
 CURINDICATOR FLOAT,
 SPLITINDICATOR CHAR(4),
 UNITFACTOR FLOAT,
 DESCRIPTION CHAR(450)
)

Error is:

   Column Name                  Position   Len  Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
TABLE_NAME                          FIRST    30   |  O(") CHARACTER            
FIELD_NAME                           NEXT    60   |  O(") CHARACTER            
CURINDICATOR                         NEXT     4           FLOAT                
SPLITINDICATOR                       NEXT     4   |  O(") CHARACTER            
UNITFACTOR                           NEXT     4           FLOAT                
SQL string for column : "NVL(:UNITFACTOR,NULL)"
DESCRIPTION                          NEXT   450   |  O(") CHARACTER            

value used for ROWS parameter changed from 100000 to 65534
Record 16: Rejected - Error on table FP_METADATA, column SPLITINDICATOR.
no terminator found after TERMINATED and ENCLOSED field

Errorneus record is:

"a"|"zzz"|0|""||"test0"
"b"|"xxx"|0|""||"test1"
"c"|"yyy"|1|"P"|1|"test2"   <--! here sqlldr has been failing

UPD:

Another example of errorneus input line is:

"fp_basic_bd"|"p_price"|1|"P"|1|"Price - Closing"

Could you, please, point on my mistake if any?

Solution 1

Just a guess from your error, but try either removing double quotes in the data or changing 1 double quote with 2 double quotes. You may be able to do this in the control file itself. Try (untested):

LOAD DATA
...
(
...
splitindicator nullif splitindicator = blanks "replace(:splitindicator, '"', '""')"
...
)

You should be able to remove double quotes using:

splitindicator nullif splitindicator = blanks "replace(:splitindicator, '"', '')"

If this doesn't work, you may need to fix the data file itself (or use the replace function on the extract if the source of the data is from some other SQL)

Hope that works

Solution 2

I 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 '"'
)

Comments

[daniilyar]

I have tried to remove double quotes using 'SPLITINDICATOR nullif SPLITINDICATOR = blanks "replace(:SPLITINDICATOR, '\"', '')"', but it still fails with the same exception (here is the full trace of failure with errorneus data)

[tbone]

I don't think the erroneous data line is from Oracle, which I assume you pasted into the log. Is it possible that there is a non-printable character (a null char for example) that is causing problems? Can you view the raw file in a hex editor (at least the problem lines) and see if this is the case?

[daniilyar]

Just checked, the whole line is from utf-8 - symbols