Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Read about the latest Qlik Community enhancements on the Community News blog!
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

View solution in original post

Not applicable
Author

wow... very impressive.

Thank you so much for your help.

shane_spencer
Specialist
Specialist

Thanks. Very useful.

reanfadyl
Partner
Partner

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;