Home » RDBMS Server » Server Administration » Importing Excel to Oracle
Importing Excel to Oracle [message #369196] Fri, 07 April 2000 12:35 Go to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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
Re: Importing Excel to Oracle [message #369200 is a reply to message #369199] Fri, 07 April 2000 17:46 Go to previous message
Joe
Messages: 138
Registered: November 1999
Senior Member
click on file, save as..., .csv comma delimited file.
Previous Topic: Re: Crontab - su - oracle problem
Next Topic: Re: SQL Join problem.
Goto Forum:
  


Current Time: Thu Mar 28 10:23:05 CDT 2024