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

[baarathi]

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

[agigliotti]

Use OLEDB to get Excel sheet names

Re: Looping through Excel Sheets and Columns

[ChennaiahNallani]

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]

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

[agigliotti]

take a look at this thread https://community.qlikview.com/thread/217365

[OmarBenSalem · Accepted solution]

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 :

[Michael_Tarallo]

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

[clemence10]

I did it differently, because I knew the names of each tab (sheet)

In my example, I had an Excel file with the accounting mapping for each company, one sheet per company(a,b,c,d,...). 

Let vPathMapping='lib://ExcelFiles/Mapping.xlsx';

For Each company in 'a','b','c','d','e','f','g','h','i','j'
Mapping:
LOAD
MappingKey,
Account,
AccountName
FROM [$(vPathMapping)]
(ooxml, embedded labels, table is $(company));
next

[rammuthiah]

What if we use web browser for QLik Sense?

Is there any solution