topic Re: facing issue while loading multiple excel file and sheet file from amazon s-3 in App Development

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

sanjeev23 — Mon, 28 Nov 2022 06:33:32 GMT

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.

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

vinieme12 — Mon, 28 Nov 2022 07:03:48 GMT

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 ;

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

sanjeev23 — Mon, 28 Nov 2022 10:14:00 GMT

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

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

vinieme12 — Mon, 28 Nov 2022 10:21:33 GMT

refer this blog

https://community.qlik.com/t5/QlikView-Documents/Loading-Multiple-Excel-Sheets-Dynamically-along-with-file-name/ta-p/1481095

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

sanjeev23 — Tue, 29 Nov 2022 10:27:25 GMT

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}

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

sanjeev23 — Tue, 29 Nov 2022 12:38:54 GMT

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