load multiple csv into one table by SQLLDR

Question

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

How big are the files, do you have space to store them 2x? Are the files reachable from the database server? — wolφi, Jul 01 '13 at 13:09
yes... files are reachable... i execute my code and it is loaded successfully.....but i want to know whether there are any other way to load data.....withount using INFILE multiple times... — goldenbutter, Jul 01 '13 at 13:17

score 11 · Accepted Answer · answered Jul 01 '13 at 13:53

11

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

answered Jul 01 '13 at 13:53

wolφi

8,091
2
35
64

score 4 · Answer 2 · answered Jul 01 '13 at 13:55

Solution 2: Use external tables and load the data using a PL/SQL procedure:

CREATE PROCEDURE myload AS
BEGIN
  FOR i IN 1 .. 1000 LOOP
    EXECUTE IMMEDIATE 'ALTER TABLE xtable LOCATION ('''||to_char(i,'FM9999')||'.csv'')';
    INSERT INTO mytable SELECT * FROM xtable;
  END LOOP;
END;

score 4 · Answer 3 · answered Apr 26 '18 at 15:23

4

You can use a wildcards (? for a single character, * for any number) like this:

infile 'file?.csv'

;)

answered Apr 26 '18 at 15:23

Paulo Alexandre Machado

101
1
5

I tried using wildcard "?", but it didn't work. ```SQL*Loader-500: Unable to open file (/tmp/csv/?_act.csv) SQL*Loader-553: file not found SQL*Loader-509: System error: No such file or directory SQL*Loader-2026: the load was aborted because SQL Loader cannot continue. ``` – Roger Jul 29 '21 at 17:12

score 1 · Answer 4 · answered Aug 13 '14 at 08:00

1

Loop over the files from the shell:

#!/bin/bash
for csvFile in `ls file*.csv`
do
    ln -s $csvFile tmpFile.csv
    sqlldr control=file_pointing_at_tmpFile.ctl
    rm tmpFile.csv
done

answered Aug 13 '14 at 08:00

RandomSeed

29,301
6
52
87

score -2 · Answer 5 · edited Apr 08 '16 at 11:30

-2

OPTIONS (skip=1)
LOAD DATA

INFILE /export/home/applmgr1/chalam/Upload/*.csv

REPLACE INTO TABLE XX_TEST_FTP_UP
FIELDS TERMINATED BY ','
TRAILING NULLCOLS
(FULL_NAME,EMPLOYEE_NUMBER)

whether it will check all the CSV and load the data or not

edited Apr 08 '16 at 11:30

Mihriban Minaz

3,043
2
32
52

answered Apr 08 '16 at 10:45

Chalam

1

load multiple csv into one table by SQLLDR

5 Answers5

Linked