SQLOADER load multple files into one table

Question

I am using SQLOADER to load multiple csv-files into 1 table. This is the content of my ctl-file

load data 
append
into table SAMP_TABLE
fields terminated by ','
OPTIONALLY ENCLOSED BY '"' AND '"'
trailing nullcols
           ( COLUMN1 CHAR(4000),
             COLUMN2 CHAR(4000),
             COLUMN3 CHAR(4000)
           )

And this is my batch file

@echo off
for %%F in ("C:\Users\test\*.csv") do (
sqlldr username/pw@dbip CONTROL='C:\Users\test\test2.ctl' LOG='C:\Users\test\TEST.log' "DATA=%%F" 
)
pause

All my csv-files, control-file and the batch-file are in the same directory. I have two csv-files with the same columns only different content. The problem is now that it imports only the first csv-file not the second one and i dont know why..? I would appreciate if someone could tell me what i am doing wrong

As I'm sure you know, this isn't intended to be a free code writing service. You could do this with pretty much any scripting language you wanted. My suggestion would be to look into Python simply because it's one of the most popular languages for this sort of work. — Jacobm001, Jul 10 '17 at 15:35
Sure, assuming you have a mysql client installed to interface with it. — Jacobm001, Jul 10 '17 at 16:02
i am using oracle..an i am not sure if linux commans will be correctly executed on windows or path-directories? — NoName123, Jul 10 '17 at 16:11

Sudipta Mondal · Answer 1 · 2017-07-10T15:44:22.003

0

You just need to give multiple infile requests

load data 
infile 'data1.csv'
infile 'data2.csv'
...
infile 'datan.csv'
append
into table TABLE1
fields terminated by ','
OPTIONALLY ENCLOSED BY '"' AND '"'
trailing nullcols
           ( COLUMN1 CHAR(4000),
             COLUMN2 CHAR(4000),
             COLUMN3 CHAR(4000)
           )

If the files are always present in the same folder

infile /path/*.csv

edited Jul 10 '17 at 15:44

answered Jul 10 '17 at 15:38

Sudipta Mondal

2,550
1
19
20

the problem is that, the are not a static numbers of files...the folder where the csv-files are located will be updated..so i need to get always all files.. – NoName123 Jul 10 '17 at 15:43
@NoName123 if you are running from the same folder, you can just give `*.csv` – Sudipta Mondal Jul 10 '17 at 15:45
if i have 10 csv-files in my folder then I import all of them, however then the folder will be updated and contains 1 additional file (so there are 11 csv-files now), then I only want to import the latest added file (not import all files again)...can I achieve something like this with the sqloader? – NoName123 Jul 10 '17 at 15:47
in that case your only option is - in your batch file. you will need to iterate over all the files, and then call sql loader for each file within the loop and move the data file to a different folder. – Sudipta Mondal Jul 10 '17 at 15:56
@NoName123 have a look at [FOR loop in CMD](https://ss64.com/nt/for.html) – Sudipta Mondal Jul 10 '17 at 15:57
Then i need to create a control file for each csv-file? And loop through all my control-files and then call sql-loader for each control file? It is not possible to pass my control-file name the csv-file name?..because I only have 1 table..dont need multiple control files..I have multiple csv-file which should be load in a table – NoName123 Jul 10 '17 at 16:25
@NoName123 you can pass data= when you call sqlloader – Sudipta Mondal Jul 11 '17 at 07:40
@NoName123 Check [this answer on stackoverflow](https://stackoverflow.com/questions/25894457/sql-loader-how-to-pass-data-file-as-a-variable) – Sudipta Mondal Jul 11 '17 at 07:41
I really appreciate for helping me..I have edited my question..I tried the solution you posted but it doesnt load all my csv-files only 1..see my question above – NoName123 Jul 11 '17 at 08:08
@NoName123 remove the `goto` why would you want to do that in your loop, it will break away from the loop and not process all of them. you can remove the 'goto Afterloop' and `:Afterloop`. – Sudipta Mondal Jul 11 '17 at 08:11
Aha ok now I understand what goto does..I edited my question..however I get the same result..it processes only 1 csv-file – NoName123 Jul 11 '17 at 08:18
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/148859/discussion-between-sudipta-mondal-and-noname123). – Sudipta Mondal Jul 11 '17 at 08:18

score 0 · Accepted Answer · answered Jul 11 '17 at 12:41

Hello this is the solution of my problem:

@echo off

    IF NOT EXIST C:\Users\test\%date:~-10,2%"-"%date:~-7,2%"-"%date:~-4,4% md C:\Users\test\%date:~-10,2%"-"%date:~-7,2%"-"%date:~-4,4%
    for %%F in ("C:\Users\test\*.csv") do (
    sqlldr dbuser/dbpw@dbip CONTROL='C:\Users\test.ctl' LOG='C:\Users\test\%date:~-10,2%"-"%date:~-7,2%"-"%date:~-4,4%\%date:~-10,2%"-"%date:~-7,2%"-"%date:~-4,4%.log' "DATA=%%F" skip=1  
    move %%F C:\Users\test\%date:~-10,2%"-"%date:~-7,2%"-"%date:~-4,4%
    )
    pause

SQLOADER load multple files into one table

2 Answers2