Discussion board where members can get started with Qlik Sense.
I have multiple excels and each excel is having at least two files in it,
is there a way i can do it in Qlik sense
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.
thanks for the response.
Bt is there a way i can put the names of the tables dynamically in the above script.
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:
Loading Multiple Excel Sheets Dynamically along with file name and sheet name
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)];
tables: SQLtables; DISCONNECT; FOR i = 0 to NoOfRows('tables')-1 LET sheetName = purgeChar(purgeChar(peek('TABLE_NAME', i, 'tables'), chr(39)), chr(36)); Table: Load * , FileBaseName()as FIle, FileDir() as Dir, FileName() as File_Name, '$(sheetName)' as Sheet_name From $(file)(ooxml, embedded labels, table is [$(sheetName)]); NEXT i Next
It works fine in qlikView but not in qliksense