my answer in other thread may help you
"sub ReadMultipleExcel (Root)
For Each File in filelist (Root&'\*.xlsx')
Load * FROM [$(File)] (ooxml, embedded labels, table is YourSheet)
Call ReadMultipleExcel ('lib://YourFiles')
It will loop through every xlsx file and read it's content."
you have multiple sheet, if you have a rules for sheet names, you can build in the script logic, the snippet above could be a template for it.
So, in this script you can use variable in sheet name as well.
Load * FROM [$(File)] (ooxml, embedded labels, table is $( YourSheet))
One pitfall is you should know the name of the sheets.
If you know exactly the file names - sheet names, you can create a table with tablename-sheetname and use it in variable.
table | sheet
a.xlsx | sheet1
b.xlsx | sheet1
FOR i=0 to $(NumRows)-1
Load * FROM [$(vtableName)] (ooxml, embedded labels, table is $(vSheetName))
If you need that dynamically, without knowledge of sheetname, try this:
It's QlikView, I never tried it in Sense.
May be like this:
This document helps you in loading multiple excels and excel sheets with the name of the sheet and data.
//to read each file from the specified folder
FOR EACH file in FileList('filepath\*.xlsx');
//In order to get the file information from SQLtables command making use of the ODBC connection format
ODBC CONNECT32 TO [Excel Files;DBQ=$(file)];
FOR i = 0 to NoOfRows('tables')-1
LET sheetName = purgeChar(purgeChar(peek('TABLE_NAME', i, 'tables'), chr(39)), chr(36));
Load * ,
FileDir() as Dir,
FileName() as File_Name,
'$(sheetName)' as Sheet_name
From $(file)(ooxml, embedded labels, table is [$(sheetName)]);