search

OraOpenSource / oos-utils

Common PL/SQL utility scripts
MIT License
204 stars 73 forks source link

Read XLSX files #177

Open martindsouza opened 6 years ago

martindsouza commented 6 years ago

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

antonscheffer commented 6 years ago

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 commented 6 years ago

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 commented 6 years ago

@mongolu can you please provide your SQL statement?

azeemshabbir commented 5 years ago

Hi Mongolu,

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

mongolu commented 5 years ago

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 commented 5 years ago

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.