load multiple csv into one table by SQLLDR

goldenbutter picture goldenbutter · Jul 1, 2013 · Viewed 31.6k times · Source

I am using SQL LOADER to load multiple csv file in one table. The process I found is very easy like

LOAD
  DATA 
  INFILE '/path/file1.csv'
  INFILE '/path/file2.csv'
  INFILE '/path/file3.csv'
  INFILE '/path/file4.csv'
  APPEND INTO TABLE TBL_DATA_FILE
    EVALUATE CHECK_CONSTRAINTS
    REENABLE DISABLED_CONSTRAINTS
    EXCEPTIONS EXCEPTION_TABLE
  FIELDS TERMINATED BY "," 
  OPTIONALLY ENCLOSED BY '"'
  TRAILING NULLCOLS
  ( 
    COL0,
    COL1,
    COL2,
    COL3,
    COL4
  )

But I don't want to use INFILE multiple time cause if I have more than 1000 files then I have to mention 1000 times INFILE in control file script.

So my question is: is there any other way (like any loop / any *.csv) to load multiple files without using multiple infile?

Thanks, Bithun

Answer

wolφi picture wolφi · Jul 1, 2013

Solution 1: Can you concatenate the 1000 files into on big file, which is then loaded by SQL*Loader. On unix, I'd use something like

cd path
cat file*.csv > all_files.csv