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
table 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.
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.