Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

How to load EXCEL worksheets and retrieve tab names

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.

1 Solution

Accepted Solutions
maxgro
MVP
MVP

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;

Next;

1.png

View solution in original post

4 Replies
maxgro
MVP
MVP

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;

Next;

1.png

Not applicable
Author

wow... very impressive.

Thank you so much for your help.

shane_spencer
Specialist
Specialist

Thanks. Very useful.

reanfadyl
Partner - Creator
Partner - Creator

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;

Next;