Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

psenthilbtech
New Contributor

Loading data from Excel Sheets

Hi Team,

The attached excel  file contains multiple sheets.

1.eSol

2.Data for Field1

3.Data for Field2

4.Data for Field3

5.Data for Field4

My Requirement is my script should load only the sheet names starting with Data for Field


IF i use the below script , it also loads the esol sheet

Script :

FOR EACH file in FileList('lib://int/int.xlsx');


//In order to get the file information from SQLtables command making use of the ODBC connection format

LIB CONNECT TO 'Excel';



tables:

SQLtables;

DISCONNECT;


FOR i = 0 to NoOfRows('tables')-1

//LET sheetName = purgeChar(purgeChar(peek('Data for Field', i, 'tables'), chr(39)), chr(36));


LET sheetName= if(left( purgeChar(purgeChar(peek('TABLE_NAME', i, 'tables'), chr(39)), chr(36)),8)= 'Data for',

purgeChar(purgeChar(peek('TABLE_NAME', i, 'tables'), chr(39)), chr(36)),Null());



tables:

SQLtables;

DISCONNECT;


FOR i = 0 to NoOfRows('tables')-1


LET sheetName= if(left( purgeChar(purgeChar(peek('TABLE_NAME', i, 'tables'), chr(39)), chr(36)),8)= 'Data for',

purgeChar(purgeChar(peek('TABLE_NAME', i, 'tables'), chr(39)), chr(36)),Null());



Table:

Load ID,NAME

From $(file)

(ooxml, embedded labels, table is [$(sheetName)]);


Next i;


Next



Note : The attached image shows that the eSol tab is also been loaded


Regards,

Senthil





2 Replies
undergrinder
Valued Contributor II

Re: Loading data from Excel Sheets

Hi Senthil,

the simplest way is when you drop the unwanted table at the end of script,

I don't know continue next or similer command in QlikSense.

Or you can write an if-then-else block based on the sheetname.

G.

wilsonwebb
New Contributor III

Re: Loading data from Excel Sheets

I think you are complicating it with a loop.

You can use the * next to the file name. Do like this.:

Table:

Load ID,NAME

From $(file)

(ooxml, embedded labels, table is [Data for Field*]);


This would then load all sheets which start with 'Data for Field'