SQLLDR control file: Loading multiple files

Question

Iam trying to load several data files into a single table. Now the files themselves have the following format:

                          file_uniqueidentifier.dat_date

My control file looks like this

    LOAD DATA
     INFILE '/home/user/file*.dat_*'
       into TABLE NEWFILES
         FIELDS TERMINATED BY ','
          TRAILING NULLCOLS
                (
                     FIRSTNAME  CHAR NULLIF (FIRSTNAME=BLANKS)
                    ,LASTNAME   CHAR NULLIF (LASTNAME=BLANKS)
                             )

My SQLLDR on the other hand looks like this

                sqlldr control=loader.ctl, userid=user/pass@oracle, errors=99999,direct=true

The error produced is SQL*Loader-500 unable to open file (/home/user/file*.dat_*) SQL*Loader-553 file not found

Does anyone have an idea as to how I can deal with this issue?

possible duplicate of [load multiple csv into one table by SQLLDR](http://stackoverflow.com/questions/17405071/load-multiple-csv-into-one-table-by-sqlldr) — RandomSeed, Aug 13 '14 at 07:50

score 2 · Answer 1 · answered Feb 10 '12 at 15:33

2

SQLLDR does not recognize the wildcard. The only way to have it use multiple files to to list them explicitly. You could probably do this using a shell script.

answered Feb 10 '12 at 15:33

Adam Hawkes

7,218
30
57

The files can not be listed explicitly, they are in thousands. – user960740 Feb 10 '12 at 15:42
This is why I suggested using a shell script. – Adam Hawkes Feb 10 '12 at 15:55
1

Use a shell script like this: `filename="$(ls /home/user/file*.dat_*)"; for curr_filename in ${filename[@]} do sqlldr userid/password@oracle control=loader.ctl data=$curr_filename; done;` – Nitish May 23 '12 at 09:28

score 1 · Answer 2 · edited Jul 02 '13 at 16:20

Your file naming convention seem like you can combine those files in to one making that one being used by the sqlldr control file. I don't know how you can combine those files into one file in Unix, but in Windows I can issue this command

copy file*.dat* file.dat

This command will read all the contents of the files that have the names that start with file and extension of dat and put in the file.dat file.

score 0 · Answer 3 · edited Oct 15 '14 at 15:06

I have used this option and this works fine for multiple files uploading into single table.

-- SQL-Loader Basic Control File

options  ( skip=1 )
load data
  infile 'F:\oracle\dbHome\BIN\sqlloader\multi_file_insert\dept1.csv'           
  infile 'F:\oracle\dbHome\BIN\sqlloader\multi_file_insert\dept2.csv'           
  truncate into table   scott.dept2
fields terminated by ","       
optionally enclosed by '"' 
  ( DEPTNO
  , DNAME
  , LOC
  , entdate
  )

SQLLDR control file: Loading multiple files

3 Answers3

-- SQL-Loader Basic Control File