'SQL Loader skips blank fields surrounded by \t if OPTIONALLY ENCLOSED is on
I am trying to load data from a file to Oracle DB using
SQL*Loader: Release 12.1.0.2.0
Table :
CREATE TABLE TEST_TABLE (
ID NUMBER(38) DEFAULT NULL,
X VARCHAR2(4000) DEFAULT NULL,
NUM NUMBER(38) DEFAULT NULL,
Y VARCHAR2(4000) DEFAULT NULL
);
Data testdata.txt:
ID X NUM Y
1 x1 0 y1
2 x2 0 "y2
."
3 0 y3
4 0 "y4
."
5 x5 0 y5
and written with tabs replaced with \t:
ID\tX\tNUM\tY
1\tx1\t0\ty1
2\tx2\t0\t"y2
."
3\t \t0\ty3
4\t \t0\t"y4
."
5\tx5\t0\ty5
So it is important that lines 3 and 4 (counting header as 0) contain a field that is simply a SPACE and lines 2 and 4 contain a quoted field containing the line separator \n
Control file:
OPTIONS (SKIP=1)LOAD DATA
CHARACTERSET we8iso8859p1
INFILE 'testdata.txt' "STR '\n'"
PRESERVE BLANKS
INTO TABLE TEST_TABLE
FIELDS CSV WITH EMBEDDED TERMINATED BY "\t" OPTIONALLY ENCLOSED BY '"'
(
ID,
X,
NUM,
Y
)
Result:
Record 3: Rejected - Error on table TEST_TABLE, column Y.
Column not found before end of logical record (use TRAILING NULLCOLS)
Record 4: Rejected - Error on table TEST_TABLE, column Y.
Column not found before end of logical record (use TRAILING NULLCOLS)
What I tried:
Using | as field separator makes the data load correctly - unfortunately I don't control the data.
Removing the NUM column makes the problem go away - this is not an option.
Using TRAILING NULLCOLS hides the error and loads faulty data
Not using PRESERVE BLANKS does not solve the problem and also ruins data
Replacing \t with X'09' in the control file changes nothing
Not using CSV WITH EMBEDDED or OPTIONALLY ENCLOSED moves the problem to failing on the quoted fields
Using data types in controlfile does nothing
Using NULLIF X=BLANKS does not solve the problem and would ruin data if it did.
Question:
How do I make SQL Loader read fields containing only BLANKS in a data file with fields separated by TAB and optionally enclosed by '"'
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|
