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