SQLLDR and NULL Field detection in column to be imported

SQLLDR and CTL file

Got a CSV file to import.

I want to specify in my CTL file to insert only into my table when I've got an empty value in a certain column in my CSV file. E.G I want to upload only user's records who don't have a termination date.

I've tried various options

• When TERMINATIONDATE = ''
• When TERMINATIONDATE = ""
• When TERMINATIONDATE = null
• When TERMINATIONDATE = 'null'

It only works when I'm using

• When TERMINATIONDATE != '' then i get all my employees that have been terminated. But i want the opposite...

Doesn't look like it's possible after all?

my CTL file

OPTIONS (SKIP=1)
LOAD DATA
INFILE 'C:\temp\users.csv' 
BADFILE 'C:\temp\users.bad'
DISCARDFILE 'C:\t开发者_Go百科emp\users.dsc'
TRUNCATE
INTO TABLE "alawakaba"."users"
When TERMINATIONDATE = ''
FIELDS TERMINATED BY '|'
OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(
ID,
FIRSTNAME,
LASTNAME,
FUNCTION,
DEPARTMENT,
COSTCENTER,
HIREDATE,
Field8 FILLER,
TERMINATIONDATE,
LOCCODE
)

(Column8 is just one I don't need so I'm skipping that)

It should work with the following clause:

WHEN TERMINATIONDATE = BLANKS

It's not the ultimate solution and has some overhead but I can do it in 2 runs:

a) Run as CTL above. Change the
When TERMINATIONDATE = ''
to
When TERMINATIONDATE != ''

The discarded records will be injected into the DISCARDFILE 'C:\temp\users.dsc' now my discarded file will contain all the records i wanted to upload

b) Run a second CTL command to pickup the DSC file as input, don't skip records anymore and don't specify the when clause neither

LOAD DATA
INFILE 'C:\temp\users.dsc' 
BADFILE 'C:\temp\users.bad'
DISCARDFILE 'C:\temp\users-run2.dsc'
TRUNCATE
INTO TABLE "alawakaba"."users"
FIELDS TERMINATED BY '|'
OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(
ID,
FIRSTNAME,
LASTNAME,
FUNCTION,
DEPARTMENT,
COSTCENTER,
HIREDATE,
Field8 FILLER,
TERMINATIONDATE,
LOCCODE
)

Not the best or quickest one around but it does the trick.

Can't believe that such a simple clause is not working from the start in SQLLDR.