Dynamic Excel sheet names based on month

[niranjana]

Hi,

I have a scenario where I have an excel file with sheet names named in descending order of a financial year...for example- Mar'26, Feb'26....until April'25. Same type of data is present in all these monthly sheets and other extra sheets are also there (which i load separately).

The sheets were named as Jan'25, Mar'26, but apostrophe was causing issue. So in the source file I changed into hyphen '-' like Jan-25,Feb-26 etc...

I tried  for loop method, but though the code runs without error, data is not being pulled. I used the following code:

LET vFilePath = '.lib://.....Feb 2026/Power data base 2025-26.xlsx';

LET vMonth = 'April-25,Mar-25,May-25,June-25,Jul-25,Aug-25,Sep-25,Oct-25,Nov-25,Dec-25,Jan-26';

FOR EACH vMonth IN $(vMonth)

Power_Data:

LOAD

//TableName('$(vMonth)') as Sheetname,
//left(FileBaseName(),3) as Monty,
text('$(vMonth)') as MonthYear,
//month('$(vMonth)') as nummonth,
//'$(vMonth)' & '-' & SubField('$(vMonth)',2) as MonthYear,

field 1,

field 2,

field 3

..and other fields

FROM
['$(vFilePath)'] (ooxml, embedded labels, header is 1 lines,table is '$(vMonth)');

NEXT vMonth;

exit script;

Also, for all these sheets, I dont want to pull rows after "Totals" Row...

Any suggestion/solution is appreciated. Thanks in advance!!

[marcus_sommer]

The syntax of vMonth is wrong because each single parameter needs to be wrapped with single-quotes, like:

vMonth = 'Jan', 'Feb', ...

Further I wouldn't use:

FOR EACH vMonth IN $(vMonth)

respectively naming the iteration-variable equally to the list-variable else rather:

FOR EACH vSheet IN $(vMonth)

[niranjana]

Hi, Thanks for your reply.

When I enclose all months in single quotes, LET ='Jan','Feb',..... shows in red. I used SET instead. But didnt work. Also, I want the sheet name to be captured as "MonthYear" field in front end. 

Would be helpful to know where exactly I have to modify the code.

2) Also, i dont want rows after "total" row......I want to pull rows only above that....

Thanks in advance!

[marcus_sommer]

This variable-assignment syntax should be working:

set vMonth = "'Jan', 'Feb', ...";

which means that the entire string with the n single-quotes is wrapped with double-quotes.

The above is just an example - you could of course use your MonthYear as sheet-name as well as an included information within the load. But like hinted I wouldn't use the same name for the list- and the iteration-variable (never tried that and it may be working but IMO it's rather confusing ...).

[Qrishna · Accepted solution]

//----------------------------------------------------------------------------//
LET vPowerFieldNameCounter = '3'; // Initiate a Power Field counter Outside of the Loop

TRACE Start Loop;
FOR EACH vSheet IN '[Mar-26]','[Feb-26]','[Jan-26]' // Include the New Sheet Name Here, Qlik doesnt allow you to pull Excel Sheet Names Dynamically in this step. you need to create another loop that pulls excel metadata before this loop starts

TRACE Loading sheet: $(vSheet) Data;

Loop:
LOAD RecNo() AS RowOrder,
PurgeChar('$(vSheet)', '[]') AS SheetName,
Text(PurgeChar('$(vSheet)', '[]')) AS MonthYear,
FieldA,
FieldB,
[Power from Source A$(vPowerFieldNameCounter)] AS PowerValue // Your New Requirement
FROM $(vFilePath)
(ooxml, embedded labels, table is $(vSheet));

TotalsRow:
LOAD RowOrder
Resident Loop
Where FieldA = 'Totals';

LET vTotalsRow = Peek('RowOrder', 0, 'TotalsRow'); // Store the Rowno of the row where Where FieldA = 'Totals'

Drop Table TotalsRow;

//NoConcatenate // This is causingissue with concatenation of the succeeding tables after the the loop finishes one round
FinalTable:
Load *,
0 as DummyField // This Dummy field is to avoid Auto Concat of 'FinalTable' to 'Loop' Table
Resident Loop
Where RowOrder < '$(vTotalsRow)';
Drop table Loop;
//Drop Field DummyField;

LET vPowerFieldNameCounter = $(vPowerFieldNameCounter) - 1; // Increment/Decrement the Power Field counter Inside of the Loop

NEXT
TRACE End Loop;
//----------------------------------------------------------------------------//

[niranjana]

Hi Qrishna!

1) Thanks for your reply. Your solution is working for my scenario now!. All the months are getting pulled. But the rows after "Total" are still getting pulled. That alone is not working though the code doesn't throw any error......

2) But I have one more issue. In all these monthly sheets, only one field is different after certain character...for example.... "Power from Source A1" in Jan-26, "Power from Source A2" in Feb-26 and so on....So the field is not getting pulled while completing for loop.....How to pull this field from all sheets in for loop?

3) Also, if in future more monthly sheets are to be added, how to write the code to dynamically pull all sheets (like using wildmatch ? )

Thanks in advance!!

[Qrishna · Accepted solution]

1. Field with Names '"Power from Source A1" in Jan-26, "Power from Source A2" in Feb-26 and so on' new requirement - Updated the code above to include your new dynamic fields.

2. Attach the Excel file with all Sheet Names and some future added dummy sheets - i need to see how the sheet names are changing

[niranjana]

Hi Qrishna, 

Thanks for your reply...

The issue of "Total" is still there......The rows after "Total" are still getting pulled. That alone is not working, though the code doesn't throw any error during load....

Thanks for any solution/help in advance!!

Niranjana

[marcus_sommer]

Not wanted records could be filtered with a where-clause, for example:

... where Field <> 'total';
... where isnum(Field);
... where len(trim(Field));
... where rowno() < X;

or similar queries - maybe also in combination against n fields.

[niranjana]

Hi Marcus,

Thanks for your reply... But I have 5 rows after "Total"  under "Plants" column which I don't want to pull. 

That is all the rows from "Total" and after that should be ignored. I only want to include rows : A,B,C

Plant

A

B

C

Total

Abstract

Source Power

..... etc....

Thanks for any solution in advance!!

Niranjana