-
1. Re: Insert data from excel file (.xls) to a Database Table
JuanM Aug 9, 2016 4:26 PM (in response to 2808179)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
-
2. Re: Insert data from excel file (.xls) to a Database Table
Mike Kutz Aug 9, 2016 5:17 PM (in response to JuanM)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
-
3. Re: Insert data from excel file (.xls) to a Database Table
Stew Ashton Aug 9, 2016 7:26 PM (in response to 2808179)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/
-
4. Re: Insert data from excel file (.xls) to a Database Table
Mike Kutz Aug 9, 2016 9:35 PM (in response to Stew Ashton)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
-
5. Re: Insert data from excel file (.xls) to a Database Table
JonWat Aug 10, 2016 5:32 PM (in response to 2808179)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.
-
6. Re: Insert data from excel file (.xls) to a Database Table
Sven W. Aug 11, 2016 8:42 AM (in response to JonWat)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.