Skip to Main Content
  • Questions

  • Direct PL/SQL access to an Excel spreadsheet

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, Don.

Asked: November 15, 2000 - 7:26 pm UTC

Last updated: July 13, 2004 - 9:03 am UTC

Version: 8.1.6.2

Viewed 1000+ times

You Asked

We would like to run an application implemented in PL/SQL that needs to process an input Excel spreadsheet (referring to an Oracle database for validation of certain parts of the spreadsheet data), and then output a revised spreadsheet that has added information about the processing results.

I suppose one could manually run Excel on both ends to convert to and from something like CSV format. But I was hoping for a way to avoid those extra manual steps.

Also, we would like to avoid solutions that involve Excel VBA programming (though that may be the only realistic option).

So, is there perchance a way for PL/SQL to directly access an Excel spreadsheet (for reading and writing), such as via some exotic ODBC adapter?

and Tom said...

Not that I am aware of. One possibility is to load the spreadsheet as is into the database and have interMedia convert it into HTML -- easily parsable by plsql. plsql can then generate a spreadsheet in sylk format (see

</code> http://asktom.oracle.com/pls/ask/f?p=4950:7:::::F4950_P7_SEARCH_CRITERIA:owa_sylk 
for how to do that).

Otherwise see
http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:243814703172 <code>
for some methods to load excel into the database.







Rating

  (2 ratings)

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

Comments

use ODBC but backwards

Kevin Meade, October 10, 2003 - 12:36 pm UTC

One option is to make Oracle do an ODBC lookup to your external data. Most people take ODBC client apps and do lookups to the Oracle database via an ODBC connection. It is possible to also do the reverse. That is, define a datasource external to Oracle and then let Oracle treat it like a table. I am not refering to external tables that are now available in 9i, but rather the older method (heterogenious this and that stuff). It is however limited in its application; you need to be on an Oracle server platform that has an ODBC filter for your intended data (you likely have to buy one), and you have to do some setup work in tnsnames/listener etc. I did do it once and it did work with MS-Access. My Oracle database got data from MS-Access (as vs. MS-Access getting data from my Oracle database). There is lots of info about this on the web and also in metalink (which is where I figured it all out). It ain't a short thing but it can work, good luck.

A reader, July 13, 2004 - 9:03 am UTC


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 channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.