Oracle Sql Loader skip option for multiple infiles

Question

When using SQL Loader control file as following:

OPTIONS(**skip=1**,bindsize=1048576,rows=1024)
LOAD DATA
INFILE 'C:\Documents and Settings\FIRST.CSV'
INFILE 'C:\Documents and Settings\SECOND.CSV'
APPEND
INTO TABLE table_name
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'  TRAILING NULLCOLS
(
fields
)

it skips the header row for FIRST.CSV file, but it loads the header row from SECOND.CSV into Oracle table. My solution is to break this control file into two separate files. Any way to comply with one control file?

Answer 1

You could do with one control file, but it would still require you to run sqlldr twice:

Control file:

OPTIONS(skip=1,bindsize=1048576,rows=1024)
LOAD DATA
APPEND
INTO TABLE table_name
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' TRAILING NULLCOLS
(
  fields
)

And then run sqlldr like this:

sqlldr control=control.ctl data=FIRST.CSV
sqlldr control=control.ctl data=SECOND.CSV

Another option that just occurred to me is that you may be able to check a record with a WHEN clause:

OPTIONS(bindsize=1048576,rows=1024)
LOAD DATA
INFILE 'C:\Documents and Settings\FIRST.CSV'
INFILE 'C:\Documents and Settings\SECOND.CSV'
APPEND
INTO TABLE table_name
WHEN (field1 <> 'ContentsOfField1InHeaderRow')
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' TRAILING NULLCOLS
(
  fields
)

If your header always contains fixed text, you could skip it based on content of (one of) the fields. Using WHEN may have an impact on performance though - depending on size of the files you may be better off with two calls to sqlldr.