• Status: Solved
• Priority: Medium
• Security: Public
• Views: 1150

# Batch file to load data in windows oracle

Hi guys,

I have files that are generated from server and their extension is .ul but inside the file the data is separated by "|" so I need a batch script to do the following for me:

- if the .ul is not recognized then the scrip just need to rename it to .csv or something
- pick the files and load them into the table,
- load the name of files to a separate table I have in  with their timestamp
- move the files to another folder so to avoid duplicate loads.

and yeah the database is in oracle, and the system is windows so I need a windows script a batch, already have that in linux but having issue creating the same in DOS

thanks for help
0
hi4ppl
• 3
• 2
• 2
• +1
1 Solution

Commented:

You will need some way to add the filename.  Possible a simple echo to add the name to the end of the file.

http://docs.oracle.com/cd/E11882_01/server.112/e22490/ldr_concepts.htm#i1005437

The rest is simple copy commands.
0

Commented:
Use a "for" loop:

for %%file in (c:\path\to\files\*.ul) do (

REM run your sqlldr command, and put %%file in the file name parm

REM save the file name and date:
echo %%file %date% %time% >> your_log

REM rename your file so it doesn't get processed again
ren %%file %%file.bak

)

0

Commented:
ThomasMcA2 already covered it, but here is part of a script I use:

for /f %%a in ('dir /b *.dat') do sqlldr user/pwd control=t.ctl data=%%a
move *.dat archive\

0

Author Commented:
Hi guys thanks but I need something like this that i have created on linux:

#!/bin/bash
PGBINDIR=/usr/bin
cd .
if [ ! -d ../archive ]; then
mkdir ../archive
fi
for f in *.csv
do
echo "Processing File - $f"$PGBINDIR/psql -c "\copy table_x from '$f' with csv" test1$PGBINDIR/psql -c "insert into table_y values ( '$f', now() )" test1 mv$f ../archive
done


But I need similar for windows how to do the something in windows for oracle
regards
0

Commented:
What is psql?  From a quick Google it is Postgres.  This was posted in an Oracle zone.  Is this Oracle or Postgres?
0

Author Commented:
Hi yeah that script is postures I and I need same for windows platform for oracle, I posted that so people can read and get what I want,

I want a clone of that for oracle in windows platform

regards
0

Commented:
There are several ways to load a delimited file into Oracle.

I would suggest the original method I posted:  SQL Loader.

Others have posted ways to rename the file when done.

I'm not sure what level of error checking psql has so you might need some additional logic to make sure all the records were loaded and everything is actually complete.

You might also look into using External Tables.  These basically follow sql loader syntax but can be queried directly from inside the database.  This can give you more control when processing.

0

Commented:
The script you posted is not that difficult to convert  as long as your files are consistent. Something like this should work (I adapted my code and ThomasMcA2's), but there are other possibilities too:

cd .
IF NOT EXIST "../archive" md "../archive"

for /f %%a in ('dir /b *.csv') do
(
sqlldr user/pwd control=t1.ctl data=%%a

echo %%a "," %date% %time% >> your_log

move *.csv ../archive
)

sqlldr user/pwd control=t2.ctl data=your_log

del your_log /y



where t1.ctl and t2.ctl have the following contents

t1.ctl
LOAD DATA
APPEND
INTO TABLE table_x
FIELDS TERMINATED BY "|"
(
your_field1,
your_field2,
...
)


t2.ctl
LOAD DATA
APPEND
INTO TABLE table_y
FIELDS TERMINATED BY ","
(
filename,
ts "to_timestamp(:ts,'DY MM/DD/YYYY, HH:MI:SS.FF')"
)


In the last line, you should modify the timestamp format according to your environment. Another way you can insert the timestamp is by using a trigger inside the table itself.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.