topic Re: How to load EXCEL worksheets and retrieve tab names in QlikView

How to load EXCEL worksheets and retrieve tab names

Fri, 15 Aug 2014 15:11:23 GMT

I have an EXCEL spreadsheet which contains 3 tabs with different names as attached. Those tab names could be changed though in the future. When I load this file, I like to load all of them no matter what the tab names are. But I do need to retrieve the tab names as part of the load script.

The final QV data file I like to see is like following with Tab Name as part of it.

Please advise. Thank you in advance.

Re: How to load EXCEL worksheets and retrieve tab names

maxgro — Fri, 15 Aug 2014 15:33:51 GMT

directory;

For Each vFile in FileList('Load_Test.xlsx')

ODBC CONNECT32 To [Excel Files;DBQ=$(vFile)];

Sheets:

SQLTABLES;

DISCONNECT;

For i = 0 To NoOfRows('Sheets')-1

Let vSheet = left(Peek('TABLE_NAME', i, 'Sheets'), len(Peek('TABLE_NAME', i, 'Sheets'))-1);

LOAD '$(vSheet)' as [Tab Name], *

From [$(vFile)]

(ooxml, embedded labels, table is $(vSheet));

Next;

Re: How to load EXCEL worksheets and retrieve tab names

Fri, 15 Aug 2014 15:55:51 GMT

wow... very impressive.

Thank you so much for your help.

Re: How to load EXCEL worksheets and retrieve tab names

shane_spencer — Fri, 02 Feb 2018 09:49:00 GMT

Thanks. Very useful.

Re: How to load EXCEL worksheets and retrieve tab names

reanfadyl — Wed, 14 Apr 2021 22:33:47 GMT

If you are trying to do this in Qlik Sense the code will need to be modified a little to handle the new style of directory connection.

You will need to create a 'normal' connection to your file and an 'ODBC' connection to the same place. Then you call both in your script e.g.

Directory LIB://MyConnectionNameHere;

For Each vFile in FileList('\*.xlsx')

LIB CONNECT TO 'MyConnectionNameHere ODBC';

Sheets:

SQLTABLES;

DISCONNECT;

For i = 0 To NoOfRows('Sheets')-1

Let vSheet = left(Peek('TABLE_NAME', i, 'Sheets'), len(Peek('TABLE_NAME', i, 'Sheets'))-1);

LOAD '$(vSheet)' as [Tab Name], *

From [$(vFile)]

(ooxml, embedded labels, table is $(vSheet));

Next;