Automatically Load all the tab details in Google sheet/Excel in Qliksense

[SK28]

Hi,

Can anyone help me with this requirement.

I have more than 30+ Excel tabs with Name of the Month-Year & their respective Data in the tabs.

I have to automatically loa

d the excel tabs & create a name of the tab as Month -Year

Tab Name - Jan -2021

Customer	Value
A	123
B	345
C	567

Tab Name - Feb - 2021

Customer	Value
D	1
E	2
F	3

Tab Name - Mar - 2021

Customer	Value
G	987
H	765
I	67

here is the required Output, Can anyone please help me with this

Output:

Customer	Value	Month Year
A	123	Jan-21
B	345	Jan-21
C	567	Jan-21
D	1	Feb-21
E	2	Feb-21
F	3	Feb-21
G	987	Mar-21
H	765	Mar-21
I	67	Mar-21

[justISO]

Hi, there are a lot similar topics in forum how to loop load through all excel sheets. For example:
https://community.qlik.com/t5/New-to-Qlik-Sense/Load-multiple-sheets-from-Excel-Files/td-p/1595476
Trickiest part for you is to have list of sheet names, as Qlik is hardly can fetch it from excel. But if your sheet names are consistent and have 'MMM - YYYY' format it possible to generate that, and loop through all of them with something like this:

Set ErrorMode = 0; //ignore errors if there will be no sheets anymore

LET vCalendarStart = Date('2021-01-01'); // Static start, 
LET vCalendarEnd = Date('2023-12-31'); //or you can make it dynamic with yearend(addyears(today(),-2))
LET vCalendarLength = Num(Date#(vCalendarEnd)) - Num(Date#(vCalendarStart)) + 1;

Calendar_temp:
LOAD DISTINCT
date(floor(MonthEnd(Date)), 'MMM - YYYY') as sheet_date; //your sheet name format
LOAD
Date($(#vCalendarStart) + RecNo()-1) AS Date 
AutoGenerate $(#vCalendarLength);

Calendar: 
LOAD
RowNo() as row, //adding number for easier loop
sheet_date
RESIDENT Calendar_temp;

DROP TABLE Calendar_temp;

LET vTotal_rows = NoOfRows('Calendar');

//LOOP THROUGH SHEETS
FOR sheet = 1 to $(vTotal_rows)
//TAKING SHEET WHICH WE WILL LOOK FOR
    sh:
      LOAD
        sheet_date
    RESIDENT Calendar
    WHERE row = $(sheet);

    LET vSheetName = peek('sheet_date','0','sh');

    DROP TABLE sh;
//LOAD DATA FROM THAT SHEET
    main:
    Load
    	*,
        '$(vSheetName)' as MonthYear
    FROM [your excel.xlsx]
    (ooxml, embedded labels, header is 1 lines, table is [$(vSheetName)]);

NEXT sheet;