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

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
Asked:
hi4ppl
  • 3
  • 2
  • 2
  • +1
1 Solution
 
slightwv (䄆 Netminder) Commented:
SQL*Loader will load the data inside the file.

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
 
ThomasMcA2Commented:
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

)

Open in new window

0
 
gatorvipCommented:
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\

Open in new window

0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
hi4pplAuthor 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

Open in new window


But I need similar for windows how to do the something in windows for oracle
regards
0
 
slightwv (䄆 Netminder) Commented:
What is psql?  From a quick Google it is Postgres.  This was posted in an Oracle zone.  Is this Oracle or Postgres?
0
 
hi4pplAuthor 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
 
slightwv (䄆 Netminder) 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.

http://docs.oracle.com/cd/E11882_01/server.112/e25494/tables013.htm#ADMIN12896
0
 
gatorvipCommented:
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
                                            

Open in new window


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,
  ...
)

Open in new window


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')"
)

Open in new window


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.

Join & Write a Comment

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

  • 3
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now