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.

What Oracle version are you using? – cagcowboy Feb 08 '12 at 07:34 — cagcowboy, Feb 08 '12 at 07:34
different versions including < 8 – kenny Feb 08 '12 at 08:25 — kenny, Feb 08 '12 at 08:25

score 3 · Answer 1 · answered Feb 08 '12 at 10:29

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

score 1 · Answer 2 · answered Feb 08 '12 at 10:07

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.

John N · Answer 3 · 2012-02-08T09:09:31.810

One approach would be to use an intermediate or load table, like so:

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

and then call a stored proc afterwards that handles the population of your main table, something like this:

CREATE PROCEDURE pop_table
IS
   CURSOR cur_load_table
   IS
              SELECT column1
                   , column2
                   , column4
                FROM loadtable;
BEGIN
   FOR rec IN cur_load_table
   LOOP
      INSERT INTO table( column1
                       , column2
                       , column4 )
      VALUES ( rec.column1
             , rec.column2
             , rec.column4 );
   END LOOP;
END pop_table;

SQL Loader ctl file - how to skip columns

3 Answers3