Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

baarathi
Contributor II

How to load all sheet in a excel simultaneously using same script

How to load all sheet in a excel simultaneously using same script

1 Solution

Accepted Solutions
OmarBenSalem
Esteemed Contributor

Re: How to load all sheet in a excel simultaneously using same script

Please take a look at this article in which I tried to explain how to handle such a case:

Dynamically Load Multiple Sheets from Excel into Qlik | LinkedIn


Sometimes, we can face a situation where our source is an excel file containing multiple sheets (with the same columns) we want to concatenate into one final table in Qlik.


So in order to do so, we'll have to manually loop the sheets... BOOOOORIIIING !


Another way is to create a generic script that would do this dynamically for us while we're happily laying back in the chair enjoying the show, right?


ps: To use this in Qlik Sense, you'll have to disable the Legacy Mode in order to be able to enter a full path (without being forced to work with Lib statements only):


to disable leagcy mode:

in desktop :

  • Open Settings.ini in a text editor.
  • Change StandardReload=1 to StandardReload=0.
  • Save the file and start Qlik Sense Desktop.

in entreprise:

Editing an engine ‒ Qlik Sense


So without further due, here's a script you can use :


//First of all, specify your file path:

FOR EACH file in FileList('C:\Users\OmarBenSalem\Desktop\Data\Data.xls');

//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

  ID,  NAME

FROM [lib://data/Data.xls]

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

NEXT i

Next


Result:

+

will result into :

6 Replies
agigliotti
Honored Contributor II

Re: How to load all sheet in a excel simultaneously using same script

ChennaiahNallani
Contributor III

Re: How to load all sheet in a excel simultaneously using same script

check this

need to load all the sheets in excel at time ?

and

LET vxlsFolder = 'insert path here ';

TableName:

LOAD *

FROM

$(vxlsFolder)\*.xls

(biff, embedded labels, table is Orders$);

baarathi
Contributor II

Re: How to load all sheet in a excel simultaneously using same script

mto @Chennaiah This is not working in qliksense. Is there any other solution?

agigliotti
Honored Contributor II

Re: How to load all sheet in a excel simultaneously using same script

OmarBenSalem
Esteemed Contributor

Re: How to load all sheet in a excel simultaneously using same script

Please take a look at this article in which I tried to explain how to handle such a case:

Dynamically Load Multiple Sheets from Excel into Qlik | LinkedIn


Sometimes, we can face a situation where our source is an excel file containing multiple sheets (with the same columns) we want to concatenate into one final table in Qlik.


So in order to do so, we'll have to manually loop the sheets... BOOOOORIIIING !


Another way is to create a generic script that would do this dynamically for us while we're happily laying back in the chair enjoying the show, right?


ps: To use this in Qlik Sense, you'll have to disable the Legacy Mode in order to be able to enter a full path (without being forced to work with Lib statements only):


to disable leagcy mode:

in desktop :

  • Open Settings.ini in a text editor.
  • Change StandardReload=1 to StandardReload=0.
  • Save the file and start Qlik Sense Desktop.

in entreprise:

Editing an engine ‒ Qlik Sense


So without further due, here's a script you can use :


//First of all, specify your file path:

FOR EACH file in FileList('C:\Users\OmarBenSalem\Desktop\Data\Data.xls');

//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

  ID,  NAME

FROM [lib://data/Data.xls]

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

NEXT i

Next


Result:

+

will result into :

Employee
Employee

Re: How to load all sheet in a excel simultaneously using same script

Hi Baarathi - I see you have a few replies from our awesome community. Let me know if you need further assistance. In short - this is most likely because Qlik Sense needs to be in Legacy mode - (Disable Standard Mode) - so it can use the legacy file paths.

Check out this resource by our man amz‌ ! - Great article Arturo

Qlik Sense Legacy mode

When applicable please mark the appropriate replies as CORRECT. This will help community members and Qlik Employees know which discussions have already been addressed and have a possible known solution. Please mark threads as HELPFUL if the provided solution is helpful to the problem, but does not necessarily solve the indicated problem. You can mark multiple threads as HELPFUL if you feel additional info is useful to others.

Regards,

Michael Tarallo

Qlik

Regards,
Mike Tarallo
Qlik
Community Browser