Solved

Infile oracle loop and load files in powershell

Posted on 2015-02-08
8
230 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
Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

 
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

Is Your AD Toolbox Looking More Like a Toybox?

Managing Active Directory can get complicated.  Often, the native tools for managing AD are just not up to the task.  The largest Active Directory installations in the world have relied on one tool to manage their day-to-day administration tasks: Hyena. Start your trial today.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
powershell code to list items in dropdown 6 66
Oracle DB monitor SW 21 50
Passing Credentials into a command line 13 35
Powershell File Sort 8 31
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
"Migrate" an SMTP relay receive connector to a new server using info from an old server.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.

832 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