Read XLSX files

[martindsouza]

https://technology.amis.nl/2013/01/19/read-a-excel-xlsx-with-plsql/

[antonscheffer]

Here is some code you can use for reading xlsx files. Free to use for everyone, without a license. If you want I have code for reading the older xls format too.
as_read_xlsx10_clean.txt

[mongolu]

Hello Martin, Anton.

I want to take the opportunity and thank Anton for this package (AS_READ_XLSX).
Thank you Anton for this package and all you shared with us, the PL/SQL community.

I want to share with all of us another method, based on Anton's logic, which is faster at reading big XLSX files.

My inspiration was Anton's package, AS_READ_XLSX (before 19-07-2017 version) which I used to compose another way to meet our need: be faster.
I mainly translated the code to mostly pure SQL and XMLTable.

So, for a quick comparison, **AS_READ_XLSX took 00:02:40.57, new method took 00:00:10.80, both for 200469 not null cells (sheet with 10.000 rows and 34 columns) **.

A quick description of the method I used is "make use mostly of SQL and make use of XMLTable".
I'm on Oracle 11.2.0.4.

[martindsouza]

@mongolu can you please provide your SQL statement?

[azeemshabbir]

Hi Mongolu,

appreciate if you share your code with us so that we can also get benefits of your efforts.

[mongolu]

Hi Mongolu,

appreciate if you share your code with us so that we can also get benefits of your efforts.

Hi guys. Sorry for not responding earlier, didn't see the mail from dozens of daily mail.
I will try these days to put up an APEX app into the cloud and give the link.

[mongolu]

Hello.
I am trying to put the package into APEX cloud app but I think it's too big because it throws INTERNAL ERROR.
It took me a great deal of time initially, familiarizing with XLSX structure.
It makes use of APEX_ZIP package which means 5.0.
It is functional but, hey! Things could always be improved. Hoping for bug-free. :-)
I'm sorry for couldn't make the demo app.
Here are the files:
XLSX_QUERY.pks.txt
XLSX_QUERY.pkb.txt
If you have comments or questions, i'll try to be here.