facing issue while loading multiple excel file and sheet file from amazon s-3

[sanjeev23]

Hi,

I am trying to load multiple excel file and multiple excel sheet from aws in Qlik sense SaaS. The issue I am Geeting only the name of all the excel file, but data fetched:0, kindly suggest me.

[vinieme12]

firstly avoid using same variable names for path/directory  and file names

refer below

let filePath ='lib://Amazon_S3/';

for each file in filelist('[$(filePath)*.xlsx]')

Load *

From  $(file)(ooxml, embedded labels, table is SheetNAME);

next file ;

[sanjeev23]

Hi,

I have 5 excel file and each excel file has 20 sheets in it. how will i incorporate all the sheet name in it.

after 

Load *

From  $(file)(ooxml, embedded labels, table is SheetNAME);

[vinieme12]

refer this blog

https://community.qlik.com/t5/QlikView-Documents/Loading-Multiple-Excel-Sheets-Dynamically-along-wit...

[sanjeev23]

not working as i am using Qlik cloud...

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)]);

{tables:
SQLtables;
DISCONNECT;: what should i use in this place}

[sanjeev23]

LIB CONNECT TO 'Amazon_S3';

let file ='lib://Amazon_S3/';
//LET sheetName= 'DEL';

//FOR i = 0 to NoOfRows('DEL')-1
LET sheetName = purgeChar(purgeChar(peek('Imp Engines Apr - Final Format.xlsx', i, 'Imp Engines Apr - Final Format.xlsx'), chr(39)), chr(36));
//if isnum(subfield('$(sheetName)', ' ', 1)) and subfield('$(sheetName)', ' ', 2) = 'to' and isnum(subfield('$(sheetName)', ' ', 3)) then

LET sheetName = purgeChar(purgeChar(peek('TABLE_NAME', i, 'tables'), chr(39)), chr(36));

Load * ,
FileBaseName()as FIle,
FileDir() as Dir,
FileName() as File_Name,
'$(sheetName)' as Sheet_name
From $(file)(ooxml, embedded labels, table is [$(sheetName)]);

I am only able to pick only one  excel sheet from the path ..not all of it. kindly suggest