Insert data from excel file (.xls) to a Database Table

2808179 wrote:

 

Appreciate your support if someone can help me to create a Procedure or package using PL/SQL to read and insert data from .xls file to a DataBase Table

here some links retrieved seraching in this forums

How to read xls or xlsx file from pl/sql

Re: 5. How do I read or write an Excel file?

In addition to Juan's links...

If you are using APEX, your choices are:

• XLS2Collection (built into ORDS)
• Excel2Collection (free APEX Plugin written by the same person mentioned in the FAQ)

Once the data is in an APEX Collection, you just need to do an INSERT-SELECT.

MK

Marc Bleron (user Odie_63) recently published a package to do this. I have not tried it yet but all the work I have seen from Marc is excellent.

https://odieweblog.wordpress.com/2016/06/21/reading-an-excel-file-xlsx-as-an-external-table/

Stew Ashton wrote:

 

Marc Bleron (user Odie_63) recently published a package to do this. I have not tried it yet but all the work I have seen from Marc is excellent.

 

https://odieweblog.wordpress.com/2016/06/21/reading-an-excel-file-xlsx-as-an-external-table/

In a related note, if you would like a function like this to be supported by Oracle, please up-vote this Database Ideas :

parse CLOBS/BLOBS using "external table" capability

(note: odie_63 had posted the same URL there.)

MK

Here's the answer to a different question (How do I get data from an Excel spreadsheet into a database table?), which may be useful to you, or not.

Using SQL developer, log onto your account. Click on "Tables" in the tree. Right click. Choose Import data and follow the wizard steps.

The advantage of this is:

     you are using a client-side tool, so your excel file only needs to be where sqldeveloper can see it, not where the server can see it

     the wizard will even create the table and columns for you

The disadvantage is:

     it's not reusable code that you can run periodically or for multiple files just by calling a procedure and passing a file name

In my experience it works better with the more modern Excel files (.xlsx) than with the old .xls files.

JonWat wrote:

 

Here's the answer to a different question (How do I get data from an Excel spreadsheet into a database table?), which may be useful to you, or not.

 

Using SQL developer, log onto your account. Click on "Tables" in the tree. Right click. Choose Import data and follow the wizard steps.

 

The advantage of this is:

you are using a client-side tool, so your excel file only needs to be where sqldeveloper can see it, not where the server can see it

the wizard will even create the table and columns for you

The disadvantage is:

it's not reusable code that you can run periodically or for multiple files just by calling a procedure and passing a file name

 

In my experience it works better with the more modern Excel files (.xlsx) than with the old .xls files.

Good thing to point out!

Also this is reusable for the next time you load the same structured excel file.

See here for an explanation: http://www.thatjeffsmith.com/archive/2014/12/sql-developer-4-1-easier-excel-imports/

And using SQLDeveloper 4.1 and the new command line interface you can even automate those imports using a script! I admit I never tried it thou.