Home » SQL & PL/SQL » SQL & PL/SQL » Import Excel File into an SQL Table
Import Excel File into an SQL Table [message #280092] Mon, 12 November 2007 08:05 Go to next message
pcd_lou
Messages: 63
Registered: October 2007
Location: Florida
Member
I have a need to read an Excel Spreadsheet and load an Oracle table. This is a conversion process and will need to be done several times.

Do I have to export the Excel file to a CSV File, or can PL/SQL read an Excel file?

And Can I use the Excel Headings to as column names on the table?

I would also like to automate the Table Create and Load process this way.

Can this be done like this? If so, does anyone have sample code?

Thanks,
Lou

Re: Import Excel File into an SQL Table [message #280093 is a reply to message #280092] Mon, 12 November 2007 08:13 Go to previous messageGo to next message
Michel Cadot
Messages: 64117
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:

Do I have to export the Excel file to a CSV File, or can PL/SQL read an Excel file?

Yes, use an external table.

Quote:

Can I use the Excel Headings to as column names on the table?

Yes if you program it to in your process.

Quote:

Can this be done like this?

Like what? You can do it with your scripting language.

Regards
Michel
Re: Import Excel File into an SQL Table [message #280094 is a reply to message #280092] Mon, 12 November 2007 08:13 Go to previous messageGo to next message
skooman
Messages: 912
Registered: March 2005
Location: Netherlands
Senior Member
I know the express edition (that free, smaller edition of the database) has a very nice userinterface including an out-of-the-box import excel utility.

So, if that's option you might want to use the express edition.
Re: Import Excel File into an SQL Table [message #280095 is a reply to message #280093] Mon, 12 November 2007 08:15 Go to previous messageGo to next message
pcd_lou
Messages: 63
Registered: October 2007
Location: Florida
Member
Yes. I'm using PL/SQL.

I would want to read the CSV file first line (with the heading lines) and use them to create the column names.

Thanks,
Lou
Re: Import Excel File into an SQL Table [message #280096 is a reply to message #280094] Mon, 12 November 2007 08:20 Go to previous messageGo to next message
pcd_lou
Messages: 63
Registered: October 2007
Location: Florida
Member
I've got Golden 32 for a query tester or utility to view or run queries.

I would like to use a PL/SQL Program because I have lots of Excel files to Load and they are different formats. I need to run the programs multiple times (drop and create table) to test my conversion programs.

Any ideas?

Thanks,
Lou
Re: Import Excel File into an SQL Table [message #280098 is a reply to message #280096] Mon, 12 November 2007 08:29 Go to previous messageGo to next message
Michel Cadot
Messages: 64117
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:

drop and create table

Why?

Regards
Michel
Re: Import Excel File into an SQL Table [message #280099 is a reply to message #280094] Mon, 12 November 2007 08:29 Go to previous messageGo to next message
pcd_lou
Messages: 63
Registered: October 2007
Location: Florida
Member
How do I execute this utility? I am not a DBA.

Re: Import Excel File into an SQL Table [message #280102 is a reply to message #280098] Mon, 12 November 2007 08:34 Go to previous messageGo to next message
pcd_lou
Messages: 63
Registered: October 2007
Location: Florida
Member
I will be getting a new files a couple of times with additional data. I would also like to test the next steps in the conversion process. The load would be into a temp table and then read into a PL/SQL program to validate the data before writing it to the application database.
Re: Import Excel File into an SQL Table [message #280103 is a reply to message #280102] Mon, 12 November 2007 08:35 Go to previous messageGo to next message
Michel Cadot
Messages: 64117
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:

The load would be into a temp table and then read into a PL/SQL program to validate the data before writing it to the application database.

External tables are made for this!

Regards
Michel
Re: Import Excel File into an SQL Table [message #280104 is a reply to message #280103] Mon, 12 November 2007 08:37 Go to previous messageGo to next message
pcd_lou
Messages: 63
Registered: October 2007
Location: Florida
Member
Ok. Is there a good reference on the web that I can use to define and use External Tables?
Re: Import Excel File into an SQL Table [message #280105 is a reply to message #280099] Mon, 12 November 2007 08:40 Go to previous messageGo to next message
skooman
Messages: 912
Registered: March 2005
Location: Netherlands
Senior Member
pcd_lou wrote on Mon, 12 November 2007 09:59

How do I execute this utility?


If you are referring to the Express edition utility I mentioned:

I don't recall the exact steps but it's really something like "install, start database, start user interface, log in, choose Import, point to Excel file and choose GO".

Not for serious/major imports/conversions (on the other hand, how serious/major can an Excel file be??). It is to be used on your desktop, no multi user, advanced logging, etc. But it works fast and easy. And if you are happy with the result (all those Excel files are nicely stored in your own local database), then create a database link to your target database and create all the tables there (as select * from).

[Updated on: Mon, 12 November 2007 09:49]

Report message to a moderator

Re: Import Excel File into an SQL Table [message #280106 is a reply to message #280105] Mon, 12 November 2007 08:45 Go to previous messageGo to next message
pcd_lou
Messages: 63
Registered: October 2007
Location: Florida
Member
I don't have that kind of acceess to Oracle. I can use PL/SQL and Golden where I can execute SQL Commands and programs. I'm on a network and I do not have NetAdmin nor the DBA privalges.
Re: Import Excel File into an SQL Table [message #280111 is a reply to message #280106] Mon, 12 November 2007 08:57 Go to previous messageGo to next message
jheronimus
Messages: 11
Registered: March 2007
Location: The Netherlands, Groninge...
Junior Member
If you have the file in cvs format, then you can also use the good old SQL Loader.

The advantance comparing to external tables is that you don't have to ftp your file to the database server first. Since you're not a dba, you probally don't have access to the filesystem of the database server.

Greetings,
Jeroen de Jong



Re: Import Excel File into an SQL Table [message #280216 is a reply to message #280106] Mon, 12 November 2007 18:51 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
Another option, you can consider setting up an ODBC Data Source to the Excel file and then configure Oracle's Hetergeneous Services (HSODBC) to query from the Excel file directly.

However, that being said, your limited privileges may prevent you from being able to do the complete configuration.
Re: Import Excel File into an SQL Table [message #280349 is a reply to message #280216] Tue, 13 November 2007 05:17 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
I recently used the inserter.xls excel file to generate DML for me. It is posted here somewhere. I believe it was JRowbottom that posted it. Not sure though.

MHE
Re: Import Excel File into an SQL Table [message #280390 is a reply to message #280349] Tue, 13 November 2007 06:56 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
Maaher, is this file from Ross the one that you are referring to?
Re: Import Excel File into an SQL Table [message #280395 is a reply to message #280390] Tue, 13 November 2007 07:02 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
yes! that's it allright. Sorry ross Embarassed. I thought it was posted by someone else. We have used it to fill our translation tables. Most people responsible for translation know how to work with Excel and we don't have to convert it to scripts by hand. It is very nice...

MHE
Re: Import Excel File into an SQL Table [message #280960 is a reply to message #280092] Thu, 15 November 2007 04:45 Go to previous message
rak007
Messages: 107
Registered: October 2006
Location: Mumbai / Pune, India
Senior Member
You can use the utility TOAD to import data from excel into an SQL table. There is a ready to use tool available in this.
Previous Topic: simplify this query
Next Topic: begginers to sql
Goto Forum:
  


Current Time: Tue Dec 06 04:41:34 CST 2016

Total time taken to generate the page: 0.24318 seconds