Load multiple sheets from Excel XLSX dynamically without ODBC

[wildrain]

Hi All,

I would like to load data from an Excel file which has multiple sheets. As an example, I have included a sample of the file. Essentially, there will be multiple such files where the first sheet will be a Summary sheet and will have the sheet name and description of the other sheets in the file.

This what I have so far.

SOURCE_FILE:
LOAD [Sheet Name] as Sheet_Name,
[Sheet Description]
FROM
[QV Lookups - Test.xlsx]
(ooxml, embedded labels, table is Summary);

NoConcatenate
TEMP1:
LOAD
concat(Sheet_Name,',') as ConcatSheetNames
resident SOURCE_FILE;

LET vString = Peek('ConcatSheetNames',0,TEMP1);
LET vString1 = Chr(39) & $(vString) & Chr(39); <-- not working

I had seen a code which would allow me to capture the data in each sheet provided the sheet names are in single quotes.

LET vExcelFileName = 'QV Lookups - Test';
SET i = '2020-01-01','SILVER','20200331';

for each m in $(i);

LOAD
Date,
Description,
Amount,
'$(m)' AS SheetName
FROM [$(vExcelFileName).xlsx]
(ooxml, embedded labels, table is [$(m)]);

next

So I wanted to try and get the concatenated sheet names in the format ('Summary','2020-01-01','SILVER','20200331') however I am not able to do so and it is throwing error messages on that line of the concatenation.

Can anyone please help? Alternatively if there is a better way to do what I am trying to do without ODBC, I am more than happy to learn.

[Frank_Hartmann · Accepted solution]

see attached files.

how it works:

the excel-summary tab is generated by a little macro inside module. so you can just delete it in your local excelfile. after  macro execution the neccessary information (which we generated by macro) can be used in further load script so that all the data on all sheets can be loaded within a simple loop without knowing the names of the exceltabs. Please adapt the path to your Excelfile inside module.

if you have further questions feel free to ask.

hope this helps

[wildrain]

Hi Frank,

Thanks a lot for your response. I had a look through the files and it makes sense. Couple of follow up questions.
1. I would definitely require the Summary tab since it would contain notes of what the other tabs contain. That's how we have agreed to from a business standpoint. So that way, any user who opens the file can recognise the contents of the file without visiting the other tabs. I will read up about the modules though - never seen it before.
2. There would be multiple files involved so in the module, can the below line be modified to take it a parameter or does the file name need to be hard-coded in it?
set XLDOC = XLApp.Workbooks.Open ("C:\Users\Frank\Desktop\QV Lookups - Test.xlsx")
3. Before this line in the code "for each i in $(vFields)", I tried to add the lines
NoConcatenate
Data_File:
and Qlik threw an error. Just wondering why would it do that? I eventually had to resident load the data into the data file like below.

NoConcatenate
Data_File:
LOAD
*
Resident Temptable;
drop table Temptable;

So I am trying to understand why one method is working while the other is failing.

Thanks.

[Frank_Hartmann]

1. Summary Tab is generated automatically by macro. so each time you restart the QV script,
the macro generates the Summary tab. if you delete the tab and restart the script,
the Tab should be present again.

2. I guess you need to hardcode it as long as you want to start the macro by script. If you start the macro
by Frontend Button you can use a QV Variable (including your path) and use this variable in your macro as a placeholder.

3. Im not sure what you are trying to achieve by adding a noconcatenate statement to the loop because your fieldnames are identically on all 3 tabs
which will force QV to autoconcatenate the tables. if you need to seperate the 3 tabs in QV Frontend just set a flag for filtering:

for each i in $(vFields)
Load *, '$(i)' as flag

[wildrain]

1. Understood. So if the macro were to be removed, the Summary tab would stay permanently unless it was already deleted.

2. I will try this method. I have multiple files and the file names vary so I would like to pass this name as a parameter to the macro. That way irrespective of the file, the macro will run and create the Summary tab.

3. I was planning to add the statement before the loop but now I realise that it may have been redundant and that is why Qlik threw the error.

Thanks again for all your help.