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!!
Hi - you have to incorporate the logic that i provided and not directly use the code, as my dummy data is not same as your data. if you need the exact solution, provide the original data or atleast a data set that matches your original data.
The above mentioned filtering goes against the entire record and not only a single field. This means each field could be used to filter against isnum(), len(trim()), ... Beside this also direct value-filters are applicable, maybe with something like:
... where match(Plant, 'Total, 'Abstract', 'Source Power') = 0;
which means an excluding of values. If there are only a few wanted known values within the field these values might be queried as an include-filter.
