Discarding rows containing empty string in CSV from uploading through SQL Loader control file

Question

I am trying to upload a CSV which may/may not contain empty value for a column in a row. I want to discard the rows that contain empty value from uploading to the DB through SQL Loader.

How can this be handled in ctrl file:

I have tried below conditions in the ctl file :

when String_Value is not null

when String_Value <> ''

but the rows are still getting inserted

Answer 1

This worked for me using either '<>' or '!='. I suspect the order of the clauses was incorrect for you. Note colc (also the third column in the data file) matches the column name in the table.

load data 
infile 'c:\temp\x_test.dat'
TRUNCATE
into table x_test
when colc <> ''
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS 

(
    cola char,
    colb char,
    colc char,
    cold integer external
)