Loading data from Excel Sheets

[psenthilbtech]

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

[undergrinder]

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]

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'