SQL Loader ctl file - how to skip columns

Question

Let's say in my database 4 columns:

Column1, Column2, Column3, Column4 

My data file is CSV file (comma delimited, length of column is unknown):

xxx,yyy,zzz,000  
a,bb,ccccc,ddddddd  
1,2,3,4  

The CTL will be:

LOAD DATA
INTO TABLE "TABLE" 
FIELDS TERMINATED BY ',' TRAILING NULLCOLS 
(
    Column1,
    Column2,
    Column3,
    Column4     
)

I want to skip Column3, how can I do that? I know about FILLER but it doesn't work for old oracle versions.

Solution 1

If that's an option just drop the third column with a shell tool. That's just works.

E.g. with awk:

awk 'BEGIN { FS="," } { print $1 FS $2 FS $4 }' INFILE > TMPOUTFILE

Or with sed:

sed 's/^\([^,]\+,[^,]\+,\)[^,]\+,/\1/' INFILE > TMPOUTFILE

(and you can pick several other tools (e.g. cut...)

Solution 2

I really don't know if this is supported in early versions, but in later ones you can user an SQL operator:

LOAD DATA
INTO TABLE "TABLE" 
FIELDS TERMINATED BY ',' TRAILING NULLCOLS 
(
    Column1,
    Column2,
    Column3 "decode(:Column3,null,null,null)",
    Column4
)

... which will set Column3 to null whether it has a value in the data file or not.