Solved

Infile oracle loop and load files in powershell

Posted on 2015-02-08
8
242 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
  • 3
  • 3
  • 2
8 Comments
 
LVL 34

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 34

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
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 
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 34

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

Does Powershell have you tied up in knots?

Managing Active Directory does not always have to be complicated.  If you are spending more time trying instead of doing, then it's time to look at something else. For nearly 20 years, AD admins around the world have used one tool for day-to-day AD management: Hyena. Discover why

Question has a verified solution.

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

Suggested Solutions

This article explains how to prepare an HTML email signature template file containing dynamic placeholders for users' Azure AD data. Furthermore, it explains how to use this file to remotely set up a department-wide email signature policy in Office …
The Nano Server Image Builder helps you create a custom Nano Server image and bootable USB media with the aid of a graphical interface. Based on the inputs you provide, it generates images for deployment and creates reusable PowerShell scripts that …
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.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

839 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