topic Single Excel with multiple sheet names load in to Qlik Sense automatically by month in Connectivity & Data Prep

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

24_02_SRR — Thu, 06 Apr 2023 18:19:24 GMT

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]);

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

rubenmarin — Fri, 07 Apr 2023 08:16:16 GMT

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

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

24_02_SRR — Thu, 13 Apr 2023 10:55:52 GMT

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?

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

rubenmarin — Thu, 13 Apr 2023 11:08:26 GMT

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

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

marcus_sommer — Thu, 13 Apr 2023 12:45:38 GMT

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

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

24_02_SRR — Thu, 13 Apr 2023 18:22:25 GMT

Thanks Marcus. This logic working fine for us.