Home » Infrastructure » Windows » sqloader load multiple file into 1 table
sqloader load multiple file into 1 table [message #664274] Mon, 10 July 2017 13:05 Go to next message
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 #664275 is a reply to message #664274] Mon, 10 July 2017 13:42 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
Everything you want to do can be done from OS command line; so OS script is most direct solution.
Solution can easily be implemented in PERL; too.

do the directory & files reside on DB Server itself?

What is Oracle version to 4 decimal places?


Re: sqloader load multiple file into 1 table [message #664276 is a reply to message #664275] Mon, 10 July 2017 13:47 Go to previous messageGo to next message
Thomas1934
Messages: 18
Registered: July 2017
Junior Member
Yeah the files are on the DB server.
Oracle Version 12.1.0.2
Re: sqloader load multiple file into 1 table [message #664277 is a reply to message #664276] Mon, 10 July 2017 13:58 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
https://www.google.com/webhp?hl=en&tab=ww#hl=en&q=oracle+preprocessor+command
Re: sqloader load multiple file into 1 table [message #664278 is a reply to message #664276] Mon, 10 July 2017 14:03 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

You can use, for instance, something like:
for %f in (C:\Users\Desktop\sqloader_files\*.txt) do \
sqlldr 'username/pw@dbip' DATA=%f \
CONTROL=testSmall.ctl LOG=C:\Users\Desktop\sqloader_files\test.log  \
BAD=C:\Users\Desktop\sqloader_files\test.bad skip=1 
And you remove the "infile" line of your control file.

Re: sqloader load multiple file into 1 table [message #664279 is a reply to message #664278] Mon, 10 July 2017 14:11 Go to previous messageGo to next message
Thomas1934
Messages: 18
Registered: July 2017
Junior Member
Michel Cadot wrote on Mon, 10 July 2017 14:03

You can use, for instance, something like:
for %f in (C:\Users\Desktop\sqloader_files\*.txt) do \
sqlldr 'username/pw@dbip' DATA=%f \
CONTROL=testSmall.ctl LOG=C:\Users\Desktop\sqloader_files\test.log  \
BAD=C:\Users\Desktop\sqloader_files\test.bad skip=1 
And you remove the "infile" line of your control file.

Thanks, I will try it tomorrow, since I am not on my machine now.
Didn't know that you can pass a parameter with DATA to the control file
In the end I need to write some cmd-commands to copy all the files from the
existing directory and make a new folder with a timestamp. Because the
main directory will always be updated with new csv-files and I dont want to
import the same files. Do you think this is a good solution? And I think
this should also be no problem just by using cmd-commands. Sry to ask you
that but i am not familiar with windows
Re: sqloader load multiple file into 1 table [message #664288 is a reply to message #664274] Tue, 11 July 2017 02:38 Go to previous messageGo to next message
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 #664289 is a reply to message #664288] Tue, 11 July 2017 03:01 Go to previous messageGo to next message
gazzag
Messages: 1118
Registered: November 2010
Location: Bedwas, UK
Senior Member
Run the batch file from a command prompt and copy and paste the output for us to see it.
Re: sqloader load multiple file into 1 table [message #664290 is a reply to message #664289] Tue, 11 July 2017 03:39 Go to previous messageGo to next message
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 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
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
Check the output in your case, it depends on country and Windows version
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
Use "help set" or search on the web for the details on these commands.


[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 Go to previous messageGo to next message
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
What it does is, it creates 2 folders at the beginning: "csvFiles" (to move the csv-file to this folder after it is loaded) and "logfiles" (to move the created log-file).
(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 #664307 is a reply to message #664306] Tue, 11 July 2017 13:48 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

You can generate the control file on the fly like (here I used the date where you have to use %F%):
E:\>echo load data                                                   >test.ctl
E:\>echo append                                                      >>test.ctl
E:\>echo into table SAMP_TABLE                                       >>test.ctl
E:\>echo fields terminated by ','                                    >>test.ctl
E:\>echo OPTIONALLY ENCLOSED BY '"' AND '"'                          >>test.ctl
E:\>echo trailing nullcols                                           >>test.ctl
E:\>echo            (                                                >>test.ctl
E:\>echo              INSERT_DATE EXPRESSION "current_timestamp(3)", >>test.ctl
E:\>echo              FILE_NAME constant "%DATE%",                   >>test.ctl
E:\>echo              COLUMN1 CHAR(4000)                             >>test.ctl
E:\>echo              COLUMN2 CHAR(4000)                             >>test.ctl
E:\>echo            )                                                >>test.ctl
E:\>type test.ctl
load data
append
into table SAMP_TABLE
fields terminated by ','
OPTIONALLY ENCLOSED BY '"' AND '"'
trailing nullcols
           (
             INSERT_DATE EXPRESSION "current_timestamp(3)",
             FILE_NAME constant "11/07/2017",
             COLUMN1 CHAR(4000)
             COLUMN2 CHAR(4000)
           )
Re: sqloader load multiple file into 1 table [message #664308 is a reply to message #664307] Tue, 11 July 2017 13:55 Go to previous messageGo to next message
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 #664310 is a reply to message #664308] Tue, 11 July 2017 14:21 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Yes, exactly like I did it.



Re: sqloader load multiple file into 1 table [message #664312 is a reply to message #664310] Tue, 11 July 2017 14:25 Go to previous messageGo to next message
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 #664313 is a reply to message #664312] Tue, 11 July 2017 14:28 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
But the problem is now that it will create multiple ctl-files (because we are in a for-loop) right?
No, only one, always the same name.

Quote:
I dont need to use the "echo" command right?
Yes, you need it.
echo ... >file
means create or empty a file and put in it what's inside "echo"
echo ... >>file
means append to the file what's inside "echo".

Quote:
Sry for asking but I dont work on windows..
It is the same thing in Unix (any flavor), Linux, VMS... on what do you work?

Re: sqloader load multiple file into 1 table [message #664315 is a reply to message #664313] Tue, 11 July 2017 14:48 Go to previous messageGo to next message
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 #664318 is a reply to message #664315] Tue, 11 July 2017 15:02 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

It is not %FF% but %%F in your case (must be tested).
You can test the return code or the content of log file or bad file.

Maybe you should investigate in a higher level language like Perl or WSH (Windows Script Host) or Python or Ruby... if you want to do more complex things.

Re: sqloader load multiple file into 1 table [message #664319 is a reply to message #664274] Tue, 11 July 2017 15:10 Go to previous messageGo to next message
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 #664326 is a reply to message #664319] Wed, 12 July 2017 01:17 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
vunfortenately i dont have the time to invest learning a new language
It appears you are currently learning at the same time SQL*Loader and DOS scripting. Smile

Quote:
i know it is possible with sqloader to insert into two different tables.
Yes, there are many examples in our "Utilities" (sub-)forum, use the "Search" link at top of the page adn search for something like "load file into multiple tables".

Quote:
it wont work because i would have an unique key constraint error
Set "errors=1" on your sqlldr command line but it may be another error.

Re: sqloader load multiple file into 1 table [message #664328 is a reply to message #664274] Wed, 12 July 2017 02:03 Go to previous messageGo to next message
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 #664329 is a reply to message #664328] Wed, 12 July 2017 02:49 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

"it is not working" gives no idea what is not working and what it means.

Re: sqloader load multiple file into 1 table [message #664330 is a reply to message #664329] Wed, 12 July 2017 03:16 Go to previous messageGo to next message
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 #664333 is a reply to message #664330] Wed, 12 July 2017 04:03 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

At which step/line have you this message?

Re: sqloader load multiple file into 1 table [message #664334 is a reply to message #664333] Wed, 12 July 2017 04:06 Go to previous messageGo to next message
Thomas1934
Messages: 18
Registered: July 2017
Junior Member
This is the message I get in my command-line

C:\Users\test>for %F in ("C:\Users\test\*.csv") do (
echo load data                                                      1>>test1.ctl
 echo append                                                          1>>test1.ctl
 echo into table SAMP_TABLE                                           1>>test1.ctl
 echo fields terminated by ','                                        1>>test1.ctl
 echo OPTIONALLY ENCLOSED BY '"' AND '"'                              1>>test1.ctl
 echo trailing nullcols                                               1>>test1.ctl
 echo            (                                                    1>>test1.ctl
 echo                    FILE_NAME constant "FF",                       1>>test1.ctl
 echo                 INSERT_DATE EXPRESSION "current_timestamp(3)",  1>>test1.ctl
 echo
) 1>>test1.ctl

C:\Users\test>(
echo load data                                                      1>>test1.ctl
 echo append                                                          1>>test1.ctl
 echo into table SAMP_TABLE                                           1>>test1.ctl
 echo fields terminated by ','                                        1>>test1.ctl
 echo OPTIONALLY ENCLOSED BY '"' AND '"'                              1>>test1.ctl
 echo trailing nullcols                                               1>>test1.ctl
 echo            (                                                    1>>test1.ctl
 echo                    FILE_NAME constant "FF",                       1>>test1.ctl
 echo                 INSERT_DATE EXPRESSION "current_timestamp(3)",  1>>test1.ctl
 echo
) 1>>test1.ctl
The process cannot access the file because it is being used by another process.
Re: sqloader load multiple file into 1 table [message #664335 is a reply to message #664334] Wed, 12 July 2017 04:14 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

The first "echo" line must have only 1 > not 2.

Re: sqloader load multiple file into 1 table [message #664336 is a reply to message #664334] Wed, 12 July 2017 04:14 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

And the whole "do" must not end with >>test1.ctl

Re: sqloader load multiple file into 1 table [message #664337 is a reply to message #664335] Wed, 12 July 2017 04:28 Go to previous messageGo to next message
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

Re: sqloader load multiple file into 1 table [message #664338 is a reply to message #664337] Wed, 12 July 2017 04:38 Go to previous messageGo to next message
Thomas1934
Messages: 18
Registered: July 2017
Junior Member
I have solved the issue...the mistake was because I had a wrong db-ip (I thought I need to check my loop and so on)
But now the other problem is that the code above only created the control file with this content:

      COLUMN1 CHAR(4000
              COLUMN2 CHAR(4000), 							 
              COLUMN3 CHAR(4000)							     
           )  											     

So the parts at the beginning are missing. Do you know maybe why?
Re: sqloader load multiple file into 1 table [message #664339 is a reply to message #664338] Wed, 12 July 2017 05:57 Go to previous messageGo to next message
Thomas1934
Messages: 18
Registered: July 2017
Junior Member
I am stuck, I don't know what to do next.
This is my batch-file:

@echo off
for %%i in ("C:\Users\test\*.csv") do (
SET tmpFile=%%~ni
echo load data                                                      >test1.ctl
echo INFILE 'test1.ctl'											    >>test1.ctl
echo into table TABLE_NAME	                                    >>test1.ctl
echo append                                                         >>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                 FILE_NAME  %tmpFile%             				>>test1.ctl
echo            )                                                   >>test1.ctl
sqlldr db_user/db_pw CONTROL='C:\test\test1.ctl' LOG='C:\Users\test\mylog.log' skip=1
) 
pause

It creates the control-file, however only with this content (the other part is missing):

  COLUMN1 CHAR(4000
              COLUMN2 CHAR(4000), 							 
              COLUMN3 CHAR(4000),
	      FILE_NAME test					     
           )  

And second I get the error in the cmd: The process cannot access the file because it is being used by another process.

Re: sqloader load multiple file into 1 table [message #664349 is a reply to message #664339] Wed, 12 July 2017 08:20 Go to previous messageGo to next message
Thomas1934
Messages: 18
Registered: July 2017
Junior Member
So here is the solution:

@echo off
for %%i in ("C:\Users\test\*.csv") do (
    > "test1.ctl" (
        echo load data
        echo INFILE 'test1.ctl'
        echo into table TABLE_NAME
        echo append
        echo fields terminated by ','
        echo OPTIONALLY ENCLOSED BY '"' AND '"'
        echo trailing nullcols
        echo            ^(
        echo                 COLUMN1 CHAR^(4000^),
        echo                 COLUMN2 CHAR^(4000^),
        echo                 COLUMN3 CHAR^(4000^),
        echo                 FILE_NAME  %%~ni
        echo            ^)
    )
    sqlldr db_user/db_pw CONTROL='C:\test\test1.ctl' LOG='C:\Users\test\mylog.log' skip=1
)
pause
Re: sqloader load multiple file into 1 table [message #664363 is a reply to message #664349] Wed, 12 July 2017 11:38 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Thanks for the feedback and solution.
Are you sure the loader step works correctly?
It seems to me the generated control file is not correct.

Re: sqloader load multiple file into 1 table [message #664364 is a reply to message #664363] Wed, 12 July 2017 12:20 Go to previous messageGo to next message
Thomas1934
Messages: 18
Registered: July 2017
Junior Member
Michel Cadot wrote on Wed, 12 July 2017 11:38

Thanks for the feedback and solution.
Are you sure the loader step works correctly?
It seems to me the generated control file is not correct.

Yeah it works, I did some tests before posting the solution.
Thx very much for helping me
Re: sqloader load multiple file into 1 table [message #664365 is a reply to message #664364] Wed, 12 July 2017 12:28 Go to previous message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

OK, thanks for confirming.

Previous Topic: ODBC Weirdness!
Next Topic: Is there a way custom install 12.1.0.2 EE?
Goto Forum:
  


Current Time: Thu Mar 28 08:00:46 CDT 2024