Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, m.

Asked: December 02, 2016 - 4:10 pm UTC

Last updated: October 17, 2018 - 5:36 am UTC

Version: 10G

Viewed 10K+ times! This question is

You Asked

Hi

I need all-time executable SP that can import data from an Excel and do some DML operation on live tables.

First of all, is that possible? IF so
1. what are the many ways that we can do that?
2. How do we do that using a Stored Procedure. And is that easier for Oracle Dummy's like me.

Thanks

and Chris said...

If you can save your Excel file as CSV, you can import it easily using external tables. These enable you to read the file using SQL like on a normal table.

For further explanation, see:

http://www.orafaq.com/node/848

If the file is XLS and you can't change the format, you could try reading it as an ODBC source as described at:

https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:4406709207206#18830681837358

Or you could check out this package from Anton Scheffer to enable you to read these files:

https://technology.amis.nl/2013/01/19/read-a-excel-xlsx-with-plsql/

Addenda:

Check here for the source

https://github.com/OraOpenSource/oos-utils/issues/177

Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Excel via Heterogeneous Services

Duke Ganote, December 05, 2016 - 4:15 pm UTC

As Raymond de Vries shows (and I've done in times past), you can read an Excel file using a database link -- using ODBC and Oracle heterogeneous services.
https://technology.amis.nl/2006/08/04/using-an-oracle-database-link-to-connect-to-excel/

I recall exactly it as he describes "If you’re querying from an excel file, the worksheets are seen as the tables and you need to add a $ to the end of the names. If you have lower case names in your Access database or Excel file (table or column names) you need to use lower case in the query and place it between quotes."
Chris Saxon
December 07, 2016 - 9:52 am UTC

Thanks for sharing.

One of the links below is no longer available

A reader, October 17, 2018 - 4:22 am UTC

One of the links below is no longer available:

https://technology.amis.nl/2013/01/19/read-a-excel-xlsx-with-plsql/


Connor McDonald
October 17, 2018 - 5:36 am UTC

Check out here

https://github.com/OraOpenSource/oos-utils/issues/177


more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Utilities

All of the database utilities are explained in the Utilities guide.