batch script to execute sqloader

Question

I need to use the sql-loader to import some data into a table. Here is my batch-script:

@echo off
for %%i in ("C:\Users\test\*.csv") do (
SET tmpFile=%%~ni
echo load data                                                      >controlfile.ctl
echo INFILE 'controlfile.ctl'                                               >>controlfile.ctl
echo into table TABLE_NAME                                          >>controlfile.ctl
echo append                                                         >>controlfile.ctl
echo fields terminated by ','                                       >>controlfile.ctl
echo OPTIONALLY ENCLOSED BY '"' AND '"'                             >>controlfile.ctl
echo trailing nullcols                                              >>controlfile.ctl
echo            (                                                   >>controlfile.ctl
echo                 COLUMN1 CHAR(4000),                                >>controlfile.ctl
echo                 COLUMN2 CHAR(4000),                            >>controlfile.ctl
echo                 COLUMN3 CHAR(4000),                                >>controlfile.ctl
echo                 FILE_NAME  %tmpFile%                           >>controlfile.ctl
echo            )                                                   >>controlfile.ctl
sqlldr db_user/db_pw CONTROL='C:\test\controlfile.ctl' LOG='C:\Users\test\mylog.log' skip=1
) 
pause

Normally you have a control-file and a script that executes this file, however you cannot pass parameter to the control-file. IN my case, I want to save the fileName on the column in the table. Therefore I need to create the control-file on the fly, because on this way I can pass the fileName as a parameter (tmpFile)

However, I am stuck and cannot go further.

First, the control-file will be generated however most of the content is missing:

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

And secondly, I get this message on my cmd:

The process cannot access the file because it is being used by another process.

Ignore for a moment the second part (or maybe you know exactly why I get this error)...Why is most of the content missing in my control-file? Can someone pls help me...I am stuck and don't know what to do

Yeah, I will have only 1 csv-file each day to load...my problem is that i cannot create the control-file dynamically...half of the content is missing in the file after it is created — NoName123
– NoName123, Commented Jul 12, 2017 at 12:49

aschipfl · Accepted Answer · 2017-07-13 16:19:58Z

You need to escape the closing parentheses in the echoed texts like ^). Otherwise, the opening parenthesis of for ... do ( becomes closed at an unexpected point, because the first unescaped ) is considered as the closing one. The echoed opening ones can be escaped too (like ^(), but it is not necessary (although I prefer it for cosmetical reasons).
Then do not store %%~ni in a variable, simply echo %%~ni directly later.
The trailing SPACEs and TABs are also echoed and become therefore written to the file controlfile.ctl, unless you remove them, you change the syntax to > "controlfile.ctl" echo Text, or you implement the suggestion in the next item.
You may also place all echo lines in another pair of parentheses and redirect (write) this entire block once by > "controlfile.ctl".

So here is the fixed code:

@echo off
for %%i in ("C:\Users\test\*.csv") do (
    > "controlfile.ctl" (
        echo load data
        echo INFILE 'controlfile.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\controlfile.ctl' LOG='C:\Users\test\mylog.log' skip=1
)
pause

Collectives™ on Stack Overflow

batch script to execute sqloader

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related