SQL Loader ctl file - how to skip columns

sql oracle sql-loader ctl

kenny · Feb 8, 2012 · Viewed 10.3k times · Source

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.

Answer

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...)