Skip to main content

QlikView Documents

QlikView documentation and resources.

Announcements
Document boards are being consolidated, this board no longer allows NEW documents READ MORE

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

cancel
Showing results for 
Search instead for 
Did you mean: 
petter
Partner - Champion III
Partner - Champion III

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
elyM
Partner - Creator
Partner - Creator

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

petter
Partner - Champion III
Partner - Champion III

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

0 Likes
psankepalli
Partner - Creator III
Partner - Creator III

Nice article Petter!!

petter
Partner - Champion III
Partner - Champion III

thanks

0 Likes
rwunderlich

Really nice alternative to the ODBC method Petter.

-Rob

eduardo_sommer
Partner - Specialist
Partner - Specialist

Thank you for this great post

Eduardo

ecolomer
Master II
Master II

Good,

Thank's for sharing

Enrique Colomer

derekchen7
Contributor
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
Last update:
a week ago
Updated by:
Contributors