Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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;
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;
wow... very impressive.
Thank you so much for your help.
Thanks. Very useful.
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;