Load data from *.xlsx or *.xls to Oracle table fro

Question

I need some help loading data from Excel Files (*.xlsx, *.xls)to Oracle 11 using an Oracle Apex 4.0.2 User Account.

I found and modified a process to load *.cvs files. This process works as follows:

1. Load the *.csv files into a BLOB column.
2. After insert into the table, I execute the
3. This process inserts every row to the table

I want you to help me to code a similar code, but instead of loading *.csv I want it to work with Excel files.

Oracle Apex 4.0.2 has a Workshop to load but it is only allowed to Developers, and I want to create this process in order to allow Users to load data by themselves.

Answer 1

Excel2Collection plugin is hard to beat. That's Anton's solution bundled into an APEX plugin. This way you can feed the file browse item into a process and have it spit out APEX Collection records

Demo: https://apex.oracle.com/pls/apex/f?p=52007:6:::NO:::

Plugin: http://www.apex-plugin.com/oracle-apex-plugins/process-type-plugin/excel2collections_271.html

Answer 2

Any reason why you don't upgrade to apex 5.0, or at the very least 4.2? The "Data Loading" components have been added in 4.1 and offer you a fast way of developing this exact requirement. 4.0 is ancient by now and no longer supported.

Loading an xlsx or xls is vastly, vastly different from loading a CSV. Again, I'd ask, why not update. There are plugins available which process excel files to collections, even XLSX. Again though, your apex version is too low. You're shooting yourself in the foot here. Not that there aren't coding options available, but still.

If you still don't want to upgrade for some arbitrary reason, focus perhaps on xlsx files. They're essentially zip-files with a bunch of files. I wrote a blog on this back in 2012. http://tpetrus.blogspot.be/2012/09/a-non-standard-export-to-excel-2010-xlsx.html

It's still relevant and I'm only linking it because it contains all the guts of the code. It's not infallible and has limits though. There's probably other ways, too. Less so for 4.0.

Answer 3

There is a great package developed by Anton Scheffer that allows you to work with xls or xlsx files (once uploaded as blobs) as tables and perform query operations like:

-- every sheet and every cell
    select *
    from table( as_read_xlsx.read( as_read_xlsx.file2blob( 'DOC', 'Book1.xlsx' ) ) )
--
-- cell A3 from the first and the second sheet
    select *
    from table( as_read_xlsx.read( as_read_xlsx.file2blob( 'DOC', 'Book1.xlsx' ), '1:2', 'A3' ) )
--
-- every cell from the sheet with the name "Sheet3"
    select *
    from table( as_read_xlsx.read( as_read_xlsx.file2blob( 'DOC', 'Book1.xlsx' ), 'Sheet3' ) )

Then you could use the data as needed.

Here's the code.

Answer 4

Yes. someday's ago I done something like this. I was upload some Excel Files (*.xlsx, *.xls) in oracle database table using oracle apex by a Apex plugin call "Excel2collection". Actually first I was load the excel file into apex collection table then I move it from collection to oracle database. Here I provide a link of my blog where I write the full process step by step. Link: qaiumer.blogspot.com