Importing Excel to Oracle [message #369196] |
Fri, 07 April 2000 12:35 |
Chris
Messages: 128 Registered: November 1998
|
Senior Member |
|
|
How do I transfer data from an MS Excel sheet to an Oracle database in Unix without ever setting foot in a windows environment?
I know I can save the files from excel as a text file manually, but I want to automate the imports.
|
|
|
Re: Importing Excel to Oracle [message #369197 is a reply to message #369196] |
Fri, 07 April 2000 12:58 |
RJohnson
Messages: 3 Registered: March 2000
|
Junior Member |
|
|
I'm not sure how you would automate the pumping out of the data from Excel, but once you have your comma delimited file it is really quite easy.
1) get the file to unix. If a non-technical user is doing this, you can get them to send the file as an attachment to a unix user on the box you want to run the scripts from. On the unix side you can redirect the incoming file (via a .forward file) into a script that will strip off the attachment and put it in the load dir. If the user is comfortable with ftp'ing, have them ftp to the script directory where you will do your load stuff from.
Use sqlloader to import the file. Once you biuld your control file you can automate the process with a cron job that calls a master script. For this type of thing I usually have a temp_load table in oracle into which the excel info is loaded. You can then use stored proceedures to validate and manipulate the information before you merge it into your application.
Just a quick dump on what can be done. I have to get back to work.
HTH,
Reade
|
|
|
Re: Importing Excel to Oracle [message #369198 is a reply to message #369197] |
Fri, 07 April 2000 13:11 |
Chris
Messages: 128 Registered: November 1998
|
Senior Member |
|
|
I already have everything you mentioned set up (the .forward putting it to unix and the sqloader to import it to oracle) its just making the delimited file I have a problem with.
|
|
|
Re: Importing Excel to Oracle [message #369199 is a reply to message #369198] |
Fri, 07 April 2000 13:30 |
RJohnson
Messages: 3 Registered: March 2000
|
Junior Member |
|
|
Sorry, I misunderstood the intention of your original post. I thought you wanted to automate the imports... To automate the exports from excel? I talked with a M$ guy here and he mentioned that you could use VB using COM... but I have no real clue on that end of things.
Sorry I couldn't help,
-R
|
|
|
|