SQL*Loader - How can i ignore certain rows with a specific charactre

Question

Hi guys,

If i have a CSV file that is in the following format

"fd!","sdf","dsfds","dsfd"
"fd!","asdf","dsfds","dsfd"
"fd","sdf","rdsfds","dsfd"
"fdd!","sdf","dsfds","fdsfd"
"fd!","sdf","dsfds","dsfd"
"fd","sdf","tdsfds","dsfd"
"fd!","sdf","dsfds","dsfd"

Is it possible to exclude any row where the first column has an exclamation mark at the end of the string. i.e. it should only load the following rows

"fd","sdf","rdsfds","dsfd"
"fd","sdf","tdsfds","dsfd"

Thanks

Answer 1

According to the Loading Records Based on a Condition section of the SQL*Loader Control File Reference (11g):

"You can choose to load or discard a logical record by using the WHEN clause to test a condition in the record."

So you'd need something like this:

LOAD DATA ... INSERT INTO TABLE mytable   WHEN mycol1 NOT LIKE '%!' 
(mycol1..  ,mycol2 ..)

But the LIKE operator is not available! You only have = and !=

Maybe you could try an External Table instead.

Answer 2

I'd stick a CONSTRAINT on the table, and just let them be rejected. Maybe delete them after load. Or a unix "grep -v" to clear them out the file.