1

I need to import different csv-files into 1 table. I need to use the sqlloader.(Oracle Version 12.1.0.2) This is my control-file:

load data 
append
into table SAMP_TABLE
fields terminated by ','
OPTIONALLY ENCLOSED BY '"' AND '"'
trailing nullcols
           ( 
            FILE_NAME "MyFileName",                    
            INSERT_DATE EXPRESSION "current_timestamp(3)"
           )

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.

My solution so far is to create the control-file on the fly and pass the filename:

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. I would appreciate if someone could help me..when i execute the batch-file it closes automatically again..

NoName123
  • 137
  • 5
  • 20
  • This post,https://stackoverflow.com/questions/1115508/batch-find-and-edit-lines-in-txt-file, would help you with adding the csv file name to the control file. – Patrick Bacon Jul 11 '17 at 18:51
  • Thx, but I am not sure if this works...if I am able to search for a string in a control file (not a text-file). And I dont understand it clearly, since I never used vbscript – NoName123 Jul 11 '17 at 19:04
  • Do you mean that in my for-loop before executing my batch-file, that I need to execute other commands before that...so for example after this line: "for %%F in ("C:\Users\test\*.csv") do (" I have acces to the filename with %%F..then I should first search my control-file and past this filename to the a predefined placeholder and then execute the rest of the code? – NoName123 Jul 11 '17 at 19:18
  • The post provided some different approaches to using a batch file to modify a document, though the most highly accepted answer was vbscript. It looks like it would be ideal to update the control file in the for do loop. – Patrick Bacon Jul 11 '17 at 19:42
  • A different approach would be to create the control-file on the fly and pass the file-name as parameter – NoName123 Jul 11 '17 at 19:49

0 Answers0