Solved

Infile oracle loop and load files in powershell

Posted on 2015-02-08
8
249 Views
Last Modified: 2015-02-12
Hi,

I would like to load files with powershell as my environment is windows so can't do it in shell....

OPTIONS(DIRECT=TRUE,ROWS=100,BINDSIZE=209700000,readsize=209700000)
load data 
[b]infile 'd:\test.DH[/b]' 
"str '\n'"
append
into table name
FIELDS TERMINATED by '!'
OPTIONALLY ENCLOSED by '"'
trailing nullcols
(filename)

Open in new window


I have around 27K files and this is everyday operation... I need a powershell to loop through files in put them in INFILE ... after load is done it will move them at archive folder.... or it will load the file name at the end of each record...  or load the files name in another table ....

thanks for help...
0
Comment
Question by:hi4ppl
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
  • 2
8 Comments
 
LVL 35

Expert Comment

by:johnsone
ID: 40596977
I am not a Powershell pro, so I don't think that I can help you with the actual script.

From the SQL*Loader side, what I would recommend is not to put the name of the file to be loaded in the control file.  Instead, specify the name of the file on the command line with the DATA parameter.  That takes the step of  having to try to modify the file out of the equation.
0
 
LVL 1

Author Comment

by:hi4ppl
ID: 40596996
hi,

can you give example of what you mean? you mean to put it as variable in sql*loader?
0
 
LVL 35

Expert Comment

by:johnsone
ID: 40597002
Your sample control file changes to:
OPTIONS(DIRECT=TRUE,ROWS=100,BINDSIZE=209700000,readsize=209700000)
load data 
append
into table name
FIELDS TERMINATED by '!'
OPTIONALLY ENCLOSED by '"'
trailing nullcols
(filename)

Open in new window

Then when calling SQL*Loader, your command line looks like:

sqlldr control=control.ctl user=u/p data="d:\test.DH"
0
MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

 
LVL 69

Expert Comment

by:Qlemo
ID: 40597119
I would have to look that up, so just asking: what does (filename) mean here?
0
 
LVL 1

Author Comment

by:hi4ppl
ID: 40597623
Hi, file name is static, where I will put fields of tables that can be as it's... the only part is where INFILE to loop through files in directory and put the files one at a time ...
0
 
LVL 1

Author Comment

by:hi4ppl
ID: 40597801
hi and also data="d:\test.DH" is not working for me since I have multiple files not only one file
0
 
LVL 35

Expert Comment

by:johnsone
ID: 40598064
I am very confused here.

You say file name is static, then loop through files in directory.  If the file name is static, then what is the loop doing?  You cannot have multiple files with the same name.

Pseudo code:

for file in *
   sqlldr control=control.ctl user=u/p data=$file
done

You call sqlldr once for each file.  Using a script to edit a file is a real pain to do.
0
 
LVL 69

Accepted Solution

by:
Qlemo earned 500 total points
ID: 40598950
And this part is very unprecise, too:

after load is done it will move them at archive folder.... or it will load the file name at the end of each record...  or load the files name in another table ....
You might think too complicated here. The way I get it, the PowerShell code is:
push-location C:\Import\Data
dir *.dh | % {
  sqlldr control=control.ctl user=usr/pwd data=$_.Name
  move-item $_ C:\Import\Archive\
}
pop-location

Open in new window

0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

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.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This script can help you clean up your user profile database by comparing profiles to Active Directory users in a particular OU, and removing the profiles that don't match.
The following article is intended as a guide to using PowerShell as a more versatile and reliable form of application detection in SCCM.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…

733 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question