Single Excel with multiple sheet names load in to Qlik Sense automatically by month

[24_02_SRR]

Hi All,

I have one Excel file with multiple sheets with month wise. every month one sheet will be added in the same excel.

How do we automate this excel in Qlik Sense?

Sample:

LOAD
ID,
App,
C,
D,
E,
F, G, H

FROM [lib://AttachedFiles/Test.xlsx]
(ooxml, embedded labels, table is [Jan 2022]);

LOAD
ID,
App,
C,
D,
E,
F, G, H

FROM [lib://AttachedFiles/Test.xlsx]
(ooxml, embedded labels, table is [Feb 2022]);

LOAD
ID,
App,
C,
D,
E,
F, G, H

FROM [lib://AttachedFiles/Test.xlsx]
(ooxml, embedded labels, table is [Mar 2022]);

[rubenmarin]

Hi, I think you still haveto do something like this: https://qlikviewcookbook.com/2008/09/loading-multiple-excel-sheets/

[24_02_SRR]

Thanks for your idea, but we are loading the data from google worksheet directly. So is there any other method to automate the load part?

[rubenmarin]

Google worksheets has it's own connector on recent versions of Qlik Sense, for older versions you can use the web connectors. Once connected the procedure is similar, you can follow this video: https://www.youtube.com/watch?v=Q2wbg0G54xY

[marcus_sommer · Accepted solution]

If the sheet-names are known or if they could be derived with any logic you may just loop through these values. It may simply a list like:

for each vSheet in 'Jan 2022', 'Feb 2022', ...

or calculated like:

for i = 0 to (((year(today())*12)+month(today()))-((year(makedate(2022))*12)+month(makedate(2022)))-1)
   let vSheet = date(addmonths(makedate(2022), $(i)), 'MMM YYYY');
   load * from FROM [lib://AttachedFiles/Test.xlsx] (ooxml, embedded labels, table is [$(vSheet)]);
next

[24_02_SRR]

Thanks Marcus. This logic working fine for us.