Loading data from Excel Sheets

psenthilbtech — Thu, 14 Jun 2018 09:48:33 GMT

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;

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

Regards,

Senthil

Re: Loading data from Excel Sheets

undergrinder — Fri, 15 Jun 2018 09:50:53 GMT

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.

Re: Loading data from Excel Sheets

wilsonwebb — Fri, 15 Jun 2018 11:53:09 GMT

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'

topic Loading data from Excel Sheets in App Development

Loading data from Excel Sheets

Re: Loading data from Excel Sheets

Re: Loading data from Excel Sheets