Qlik Community

QlikView Documents

Documents for QlikView related information.

How to extract Sheet Names from an Excel XLSX-file without ODBC

MVP
MVP

How to extract Sheet Names from an Excel XLSX-file without ODBC

There is a well known technique that use the Excel ODBC driver supplied from Microsoft to get a list of the sheet names of an XLSX-file. It is covered in different discussions on this forum - so I will not cover that here.

Here is an alternative way of getting the sheet names that does not involve the Excel ODBC-driver. It takes the advantage that an XLSX-file is in fact a ZIP-file in disguise. This ZIP-file (XLSX-file) has many XLM-files and more files in a folder structure. By extracting the right XML-file from this you can retrieve the sheet names easily.Another thing that this routine also illustrates is how you can unzip without being dependent on a command line unzip program. It takes advantage of the built-in libraries in any Windows operating system.

Two benefits:

  1. Not needing to install Excel ODBC drivers
  2. Not needing to install an UNZIP program

I have coded a small VBscript function that is easily called from a load script that will do the extraction. The rest is only a matter of loading the XML file with the standard way of doing it in QlikView:

2016-02-10 #1.PNG

2016-02-10 #2.PNG

Comments
ely_malki
Contributor

Hi Petter,

there is an interesting project to achieve same result using Python.

https://openpyxl.readthedocs.org/en/default/index.html#

P.S.: Good luck for your Qliky new adventure

Regards,

Ely

MVP
MVP

Thanks for the link. Actually I have no plans of parsing the Excel XLSX files except from pulling out small bits of useful info like just the names of the Sheets.

The OOXML specification is in itself thousands of pages long so that is no adventure I would gladly spend time on

It is covered in an ECMA standard called ECMA 376: Standard ECMA-376

psankepalli
Contributor III

Nice article Petter!!

MVP
MVP

thanks

Really nice alternative to the ODBC method Petter.

-Rob

eduardo_sommer
Valued Contributor

Thank you for this great post

Eduardo

ecolomer
Honored Contributor II

Good,

Thank's for sharing

Enrique Colomer

derekchen7
New Contributor

Hi Petter,

Thanks for posting this.  The ODBC method does not seem to work in Qlik Sense, so this looks like a good alternative for Sense users.  Would you mind adding some sample documents (qvw/qvf, vbscript, etc.)?

Thanks,

Derek

Version history
Revision #:
1 of 1
Last update:
‎02-10-2016 02:21 AM
Updated by: