Qlik Community

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

Highlighted
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
MVP
MVP

Re: How to load EXCEL worksheets and retrieve tab names

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

3 Replies
MVP
MVP

Re: How to load EXCEL worksheets and retrieve tab names

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

Re: How to load EXCEL worksheets and retrieve tab names

wow... very impressive.

Thank you so much for your help.

shane_spencer
Valued Contributor

Re: How to load EXCEL worksheets and retrieve tab names

Thanks. Very useful.