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?

Polyandrous

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.

Indigestible

Answer 2

I just used a WHEN clause to skip the header
I will use a column name of column1 as an example
column1_DB is the column name in the database and column1_CSV is the column name in the csv file
this will work if you're sure none of the values in column1 will be the same as the column header, if that's the case you can choose any other column where you are sure that the values will never match the headers.

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
WHEN column1_DB <> 'column1_CSV'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'  TRAILING NULLCOLS
(
column1_DB CHAR(4000),
column2_DB CHAR(4000)
)

Comprehension