Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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 ;
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);
refer this blog
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}
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