no terminator found after TERMINATED and ENCLOSED field

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?

3 Answers

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

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

I faced similar issue with multiple double quotes in Address field. Following solution worked for me:-

Load Data APPEND INTO TABLE fp_metadata fields terminated by "|" ---- remove " Optionally enclosed by from all column level" TRAILING NULLCOLS ( column1 optionally enclosed by '"', -- Specified "optionally enclosed by" here for all cols HOME_ADD "replace(:HOME_ADD, chr(34))", -- Omitted "enclosed by". replace removes all doublequotes in string value. chr(34) is charcode for doublequote column3 optionally enclosed by '"' )