Concatenate multiple Excel tabs

[DanielH1]

Hello,

I need to concatenate different Excel sheets that have the same structure.

To concatenate the name of the sheet I know how to do it with the code by using '*' as it always start with the same text:

FROM [lib://Folder/ExcelFile*.xlsx]

However, each Excel file has a different sheet name depending on the month: "Sheet February 2021", "Sheet March 2021" and so on.

I tried to concatenate the table name as with the name of the Excel file but it doesn't work:

(ooxml, embedded labels, table is Sheet*);

Is there a solution for this issue?

Thanks a lot for your help!

[Taoufiq_Zarra]

@DanielH1  if you can use ODBC

try this :

FOR EACH file in FileList('filepath\*.xlsx');

//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 * ,
FileBaseName()as FIle,
FileDir() as Dir,
FileName() as File_Name,
'$(sheetName)' as Sheet_name
From $(file)(ooxml, embedded labels, table is [$(sheetName)]);

NEXT i

drop table tables;

Next

from https://community.qlik.com/t5/QlikView-Documents/Loading-Multiple-Excel-Sheets-Dynamically-along-wit...

[DanielH1]

Hello,

Thanks for your proposal but it doesn't seem to work:

Is there a non ODBC alternative, with the code?

The following error occurred:

CONNECTs other than LIB CONNECT are not available in this script mode.

The error occurred here:

ODBC CONNECT32 TO ***

[DanielH1]

Hello,

Thanks for your proposal but it doesn't seem to work:

Is there a non ODBC alternative, with the code?

The following error occurred:

CONNECTs other than LIB CONNECT are not available in this script mode.

The error occurred here:

ODBC CONNECT32 TO ***

Thanks !