topic Re: Get the worksheet names in an Excel Workbook in Connectivity & Data Prep

Get the worksheet names in an Excel Workbook

Thu, 13 Nov 2014 13:28:37 GMT

In Qlik View, I can use ODBC to get a list of worksheet names inside an Excel workbook using this script

ODBC CONNECT32 TO [Excel Files; DBQ=$(vFileName)];
WorksheetNames:
SQLtables;
DISCONNECT;

I try to do the same in Qlik Sense but it does not work, how can I get a list of worksheet names in Qlik Sense?

Thanks,
Matthew

Re: Get the worksheet names in an Excel Workbook

JonnyPoole — Sat, 15 Nov 2014 02:03:29 GMT

Most of the script is still value but the connection strings are different in Qlik Sense

In the below example, i first had to create an ODBC data source called 'Excel' which points to a fixed excel document.

Then i created a connection in Sense and added that connection to the script followed by the other script commands.

Will this work ? Its not dynamic and you can't pass an excel file name to it as a variable...

Re: Get the worksheet names in an Excel Workbook

Sun, 16 Nov 2014 20:57:25 GMT

Thanks Jonathan. Hardcoding of the Excel DSN is not an option for me. I need to loop through the Excel files in a directory and then import selected worksheets in the workbook.