sqlldr does not recognize empty tab delimited columns

Question

I am seeing a strange problem when loading my data with sqlldr. Here is my table schema:

CREATE TABLE TEST(
   "COL1" VARCHAR2 (255 BYTE),
   "COL2" VARCHAR2 (255 BYTE),
   "COL3" NUMBER,
   "COL4" VARCHAR2 (255 BYTE)

and here is just one row of data I am trying to ingest from the tab delimited file test.txt:

COL1    COL2    COL3    COL4
        10  17-cc

notice that the first two columns are empty (null). So my row is really:

\t\t10\t17-cc

my loader script:

load data
 infile 'test.txt'
 append into table TEST
 fields terminated by "\t" optionally enclosed by '"'
 TRAILING NULLCOLS  
(COL1,COL2,COL3,COL4)

This will be loaded into my table as:

COL1 COL2  COL3   COL4
10    17-CC (null) (null)

which is incorrect. it seems that the two leading tabs in the data row were ignored and COL3 position (10) was assigned to COL1. However, if I try to import the data as a comma separated file:

COL1,COL2,COL3,COL4
,,10,17-cc

it works as expected. Why does the tab delimited version fails here?

Gary_W Gary_W · Accepted Answer · 2019-03-21T02:19:37

NOTE - Fixed my original wrong answer.

Your TAB is defined just fine. You need the NULLIF statement:

load data
 infile 'test.txt'
 append into table TEST
 fields terminated by "\t" optionally enclosed by '"'
 TRAILING NULLCOLS  
(COL1 NULLIF(COL1=BLANKS),
 COL2 NULLIF(COL2=BLANKS),
 COL3 NULLIF(COL3=BLANKS),
 COL4 NULLIF(COL4=BLANKS)
)

sqlldr does not recognize empty tab delimited columns

1 Answers