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

This can only mean the values are not the empty string in the CSV. Maybe a space, a newline or other control character? — marekful, Jan 16 '18 at 10:45
@marekful: the values are definitely empty; the row in the csv for the column shows value like: ' "YYY","","XXX" ', so the middle column has empty value — Saurabh Mehta, Jan 16 '18 at 10:50
Show us a full [MCVE](https://stackoverflow.com/help/mcve). It works as you expect so you're doing something different in your control file, or are getting something different from what you've described, and we can't guess what. — Alex Poole, Jan 16 '18 at 11:24

score 0 · Answer 1 · answered Jan 16 '18 at 15:20

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
)

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

1 Answers1