| sqloader load multiple file into 1 table [message #664274] |
Mon, 10 July 2017 13:05  |
 |
Thomas1934
Messages: 18
Registered: July 2017
|
Junior Member |
|
|
Hello,
I have multiple csv files in a directory. This directory will be updated with new csv-files. I need to load all csv files with sqloader in 1 table. So all the files have the same columns only different data.
This is how my control file looks:
load data
infile 'test.csv' "str '\r\n'"
append
into table TABLE1
fields terminated by ','
OPTIONALLY ENCLOSED BY '"' AND '"'
trailing nullcols
( COLUMN1 CHAR(4000),
COLUMN2 CHAR(4000),
COLUMN3 CHAR(4000)
)
And here is my batch file (used generic names for demostration purposes):
@echo off
sqlldr 'username/pw@dbip'
CONTROL=testSmall.ctl LOG=C:\Users\Desktop\sqloader_files\test.log
BAD=C:\Users\Desktop\sqloader_files\test.bad skip=1
pause
This works fine whith one csv file, however i need to iterate through all files and call sql loader for each file.When i am finished, i just move all the csv files in a different folder and then execute the batch file again when new files come along. Do you think this is a good solution?
I need to do this in Windows, however i am not familiar with any scripting language. Is it possible to do this only with a for loop in cmd? Or do we need to write a script with powershell or some other scripting language in windows? Would appreciate if someone could help me
thx in advanve
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: sqloader load multiple file into 1 table [message #664288 is a reply to message #664274] |
Tue, 11 July 2017 02:38  |
|
Thomas1934
Messages: 18
Registered: July 2017
|
Junior Member |
|
|
I have tried the solution above, but it doesnt work.
When I execute my batch-file it opens and closes automatically, even if
i have a pause command
Here is my ctl-file (I deleted the infile-statement, since I pass it as paramater when i execute the batch-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 here is my batch-file:
@echo off
for %f in (*.csv) do \
sqlldr 'username/pw@dbip' DATA=%f \ CONTROL=mycontrolfile.ctl
LOG=C:\Users\test\test.log \
BAD=C:\Users\test\test.bad skip=1
pause
the batch-file, control-file and the csv-file are in the same directory
[Updated on: Tue, 11 July 2017 02:39] Report message to a moderator |
|
|
|
|
|
| Re: sqloader load multiple file into 1 table [message #664290 is a reply to message #664289] |
Tue, 11 July 2017 03:39 |
|
Thomas1934
Messages: 18
Registered: July 2017
|
Junior Member |
|
|
I have solver the problem. This is the solution;
@echo off
for %%F in ("C:\Users\test\*.csv") do (
IF NOT EXIST C:...\archive md C:..\archive
sqlldr username/pw@dbip CONTROL='C:\Users\test\test2.ctl' LOG='C:\Users\test\TEST.log' "DATA=%%F"
move %%F C: ..\archive
)
this is my solution. I only create 1 folder...it would be nice to create different folder with a timestamp after each import of all csv-files (for example today I import 10 csv-files..then i create a folder with the actual date and move all csv-files there, then the next day i do the same if knew csv-files come along)..do you know how to do that? is it possible in cmd?
[Updated on: Tue, 11 July 2017 03:54] Report message to a moderator |
|
|
|
| Re: sqloader load multiple file into 1 table [message #664305 is a reply to message #664290] |
Tue, 11 July 2017 12:19 |
 |
Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...
|
Senior Member
Account Moderator |
|
|
You can use %DATE% and %TIME% pseudo-variables:
E:\>echo %date%
11/07/2017
E:\>echo %time%
19:13:30,70
You can use shell extension to format these variables:
C:\>echo %date%
11/07/2017
C:\>echo %date:~6,4%%date:~3,2%%date:~0,2%
20170711
[Updated on: Tue, 11 July 2017 12:20] Report message to a moderator |
|
|
|
| Re: sqloader load multiple file into 1 table [message #664306 is a reply to message #664305] |
Tue, 11 July 2017 13:35 |
|
Thomas1934
Messages: 18
Registered: July 2017
|
Junior Member |
|
|
Thx for the answers, here is my solution:
This is my control-file:
load data
append
into table SAMP_TABLE
fields terminated by ','
OPTIONALLY ENCLOSED BY '"' AND '"'
trailing nullcols
(
INSERT_DATE EXPRESSION "current_timestamp(3)",
FILE_NAME CHAR(4000),
COLUMN1 CHAR(4000)
COLUMN2 CHAR(4000)
)
And this is my batch-file (I need to do it in a windows-machine):
@echo off
IF NOT EXIST C:\Users\test\csvFiles
IF NOT EXIST C:\Users\test\logfiles
for %%F in ("C:\Users\test\*.csv") do (
SET tmpFile=%%F
SET newFile=%tmpFile:~0,-3%.log
sqlldr db_user/db_pw@db_ip CONTROL='C:\Users\test.ctl' LOG='C:\Users\logfiles\%newFile%' "DATA=%%F" skip=1
move %%F C:\Users\test\csvFiles
)
pause
(With DATA=%%F i can pass the csv-file to the control-file)
You can see that the first 2 columns on my control-file are "INSERT DATE" which inserts the current_timestamp and "FILE_NAME".
Now my problem now is that I dont know how to pass the file-name of the csv-file(which will be loaded) to the control-file. I want for each import csv-file to insert the file-name of this csv-file into this column. I searched for some solutions but some of them are in UNIX, however I need to do it in Windows.
I would appreciate if someone could help me, since I am not very familiar with batch-scripting or scripting in general.
[Updated on: Tue, 11 July 2017 13:37] Report message to a moderator |
|
|
|
|
|
| Re: sqloader load multiple file into 1 table [message #664308 is a reply to message #664307] |
Tue, 11 July 2017 13:55 |
|
Thomas1934
Messages: 18
Registered: July 2017
|
Junior Member |
|
|
This is an interesting approach, never thought of that.
However I am not very familiar with the cmd-commands...
can u elaborate on your solution because I dont understand it...how do I write your code in the batch-file...i am a bit confused
I need to insert your after this line in my batch-file right?:
for %%F in ("C:\Users\test\*.csv") do (
The file will be automatically created? Can I then just afterwards execute the sqlldr command?
[Updated on: Tue, 11 July 2017 14:23] Report message to a moderator |
|
|
|
|
|
| Re: sqloader load multiple file into 1 table [message #664312 is a reply to message #664310] |
Tue, 11 July 2017 14:25 |
|
Thomas1934
Messages: 18
Registered: July 2017
|
Junior Member |
|
|
Michel Cadot wrote on Tue, 11 July 2017 14:21
Yes, exactly like I did it.
I edited my answer before. I have an additional question.
But the problem is now that it will create multiple ctl-files (because we are in a for-loop) right?
The file will be automatically created in the loop? Can I then just afterwards execute the sqlldr command?
And another maybe very noob question. I dont need to use the "echo" command right?
Sry for asking but I dont work on windows...but echo means it just prints it out on the console right?
[Updated on: Tue, 11 July 2017 14:28] Report message to a moderator |
|
|
|
|
|
| Re: sqloader load multiple file into 1 table [message #664315 is a reply to message #664313] |
Tue, 11 July 2017 14:48 |
|
Thomas1934
Messages: 18
Registered: July 2017
|
Junior Member |
|
|
Thx for your help, I really appreciate it. (I am a junior java dev without scripting skills or very low)
Just an additional question. If I want to load the data into 2 different tables.
So I have 1 csv-file and 2 tables.
1-Table has the following columns:
----------------------------------
FILE_NAME(PK)
INSERT_DATE
2-Table has the following columns:
----------------------------------
PK(sequence)
FILE-NAME(FK)
COLUMN1
COLUMN2
The first and second table would have a 1-n relationship.
I just want to avoid loading the same csv-file (all csv-file are unique by their name)
I would do it like this after your input:
@echo off
IF NOT EXIST C:\Users\test\csvFiles
IF NOT EXIST C:\Users\test\logfiles
for %%F in ("C:\Users\test\*.csv") do (
echo load data >test1.ctl
echo append >>test1.ctl
echo into table SAMP_TABLE >>test1.ctl
echo fields terminated by ',' >>test1.ctl
echo OPTIONALLY ENCLOSED BY '"' AND '"' >>test1.ctl
echo trailing nullcols >>test1.ctl
echo ( >>test1.ctl
echo FILE_NAME constant "%FF%", >>test1.ctl
echo INSERT_DATE EXPRESSION "current_timestamp(3)", >>test1.ctl
echo >>test1.ctl
echo )
SET tmpFile=%%F
SET newFile=%tmpFile:~0,-3%.log
sqlldr db_user/db_pw@db_ip CONTROL='C:\Users\test1.ctl' LOG='C:\Users\logfiles\%newFile%' "DATA=%%F" skip=1
move %%F C:\Users\test\csvFiles
echo load data >test2.ctl
echo append >>test2.ctl
echo into table SAMP_TABLE >>test2.ctl
echo fields terminated by ',' >>test2.ctl
echo OPTIONALLY ENCLOSED BY '"' AND '"' >>test2.ctl
echo trailing nullcols >>test2.ctl
echo ( >>test2.ctl
echo FILE_NAME constant "%FF%", >>test2.ctl
echo COLUMN1 CHAR(4000), >>test2.ctl
echo OLUMN2 CHAR(4000) >>test2.ctl
echo )
SET tmpFile=%%F
SET newFile=%tmpFile:~0,-3%.log
sqlldr db_user/db_pw@db_ip CONTROL='C:\Users\test2.ctl' LOG='C:\Users\logfiles\%newFile%' "DATA=%%F" skip=1
move %%F C:\Users\test\csvFiles
)
pause
What I am trying to achieve is that, first it will import into the first table and then if the file-name is already present in the table (it means that i has been alread imported)
then it should stop. Maybe I also need to create different log-file names for both imports
[Updated on: Tue, 11 July 2017 14:50] Report message to a moderator |
|
|
|
|
|
| Re: sqloader load multiple file into 1 table [message #664319 is a reply to message #664274] |
Tue, 11 July 2017 15:10 |
|
Thomas1934
Messages: 18
Registered: July 2017
|
Junior Member |
|
|
Yeah, you are right, however unfortenately i dont have the time to invest learning a new language...but as far as i know it is possible with sqloader to insert into two different tables.
i need to create the ctl-files on the fly it makes things more complicated..i just want to make sure to add the file name and date into a seperate table and make the file name a pk..so when the file with the same file name is imported the second time than it wont work because i would have an unique key constraint error
[Updated on: Tue, 11 July 2017 15:11] Report message to a moderator |
|
|
|
|
|
| Re: sqloader load multiple file into 1 table [message #664328 is a reply to message #664274] |
Wed, 12 July 2017 02:03 |
|
Thomas1934
Messages: 18
Registered: July 2017
|
Junior Member |
|
|
I am trying to create the control-file on the fly but it is not working.
Here is my batch-file ( I have csv-files in my directory so the loop should work):
IF NOT EXIST C:\Users\test\csvFiles
IF NOT EXIST C:\Users\test\logfiles
for %%F in ("C:\Users\test\*.csv") do (
echo load data >test1.ctl
echo append >>test1.ctl
echo into table SAMP_TABLE >>test1.ctl
echo fields terminated by ',' >>test1.ctl
echo OPTIONALLY ENCLOSED BY '"' AND '"' >>test1.ctl
echo trailing nullcols >>test1.ctl
echo ( >>test1.ctl
echo FILE_NAME constant "%FF", >>test1.ctl
echo INSERT_DATE EXPRESSION "current_timestamp(3)", >>test1.ctl
echo >>test1.ctl
echo ) >>test1.ctl
SET tmpFile=%%F
SET newFile=%tmpFile:~0,-3%.log
sqlldr db_user/db_pw@db_ip CONTROL='C:\Users\test1.ctl' LOG='C:\Users\logfiles\%newFile%' "DATA=%%F" skip=1
move %%F C:\Users\test\csvFiles
)
pause
It works only without a for-loop, i dont know why...
[Updated on: Wed, 12 July 2017 02:11] Report message to a moderator |
|
|
|
|
|
| Re: sqloader load multiple file into 1 table [message #664330 is a reply to message #664329] |
Wed, 12 July 2017 03:16 |
|
Thomas1934
Messages: 18
Registered: July 2017
|
Junior Member |
|
|
Michel Cadot wrote on Wed, 12 July 2017 02:49
"it is not working" gives no idea what is not working and what it means.
Sry you are right...The control-file won't be created...
After I execute the bat-file it opens and closes automatically.
I think I have solved it partly(will post the solution if it is complete).
Now the problem is that after the control-file is created in the loop..
it cannot create it again because i get the error message that the file
is locked by another process..which means by my own bat-script..
The original message is="The process cannot access the file because it is being used by another process"
do you know
how to solve this issue?
[Updated on: Wed, 12 July 2017 03:51] Report message to a moderator |
|
|
|
|
|
|
|
|
|
|
|
| Re: sqloader load multiple file into 1 table [message #664337 is a reply to message #664335] |
Wed, 12 July 2017 04:28 |
|
Thomas1934
Messages: 18
Registered: July 2017
|
Junior Member |
|
|
Michel Cadot wrote on Wed, 12 July 2017 04:14
The first "echo" line must have only 1 > not 2.
Sry this was a type. Here is my file and I think it is correct (The for-loop ends at the end of the file before the pause)
(Just ignore the different names for the columns..this is just for demonstration purposes)
for %%F in ("C:\test\*.csv") do (
echo load data >test1.ctl
echo append >>test1.ctl
echo into table TAB_NAME >>test1.ctl
echo fields terminated by ',' >>test1.ctl
echo OPTIONALLY ENCLOSED BY '"' AND '"' >>test1.ctl
echo trailing nullcols >>test1.ctl
echo ( >>test1.ctl
echo COLUMN1 CHAR(4000), >>test1.ctl
echo COLUMN2 CHAR(4000), >>test1.ctl
echo COLUMN3 CHAR(4000) >>test1.ctl
echo ) >>test1.ctl
SET tmpFile=%%F
SET newFile=%tmpFile:~0,-3%.log
sqlldr db_user/db_pw@db_ip CONTROL='C:\test\test1.ctl' LOG='C:\test\logs\%newFile%' "DATA=%%F" skip=1
)
pause[Updated on: Wed, 12 July 2017 04:29] Report message to a moderator |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
