How do I load data from Excel into an Oracle table?

I have a compressed Excel file and I want to extract the file and load the data in to an Oracle table. Is there any utility or package available in pl/sql that will do this?

There are a few ways this could be done:
  • It is possible to connect directly to an Oracle database from an Excel spreadsheet using VBA but the performance may not be that great and this would not be an automated solution

  • You could use Oracle Apex (Application Express) to read from/write to an Excel spreadsheet via the GUI interface. Again you may not be able to automate this.

  • Probably a better option would be to use the external
    feature of Oracle databases which allows you to read (only) from a file created by the operating system. With Oracle 11g you can also specify a preprocessor - a program to (in this case) uncompress the file before it is accessed by the Oracle software. You can also automate the whole process by specify a job to be run by using thedbms_scheduler package.

See Oracle Concepts for more details on external tables.

If you're interested in formal Oracle training either on-line or in person see our Oracle training page for more details.

Click here to post comments

Join in and write your own page! It's easy to do. How? Simply click here to return to Oracle Questions.