Load an Excel with different table names by using a for each in a variable which is an array?

[DiegoF]

Hello,

I have this code that loads right now an Excel with 2 similar table names: Calendario Horno 9 and Calendario Horno 10, in which I create a list manually so it loops this 2 times and loads all the data:

FOR Each Hoja In '[Calendario Horno 9]', '[Calendario Horno 10]'

EXCEL:
LOAD 
	*
FROM $(vRutaEXCEL)CalendarioHornos.xlsx 
(ooxml, no labels, header is 2 lines, table is $(Hoja));
NEXT;

I want to know if I can use a RegEx in the list, for example, FOR Each Hoja In WildMatch($(Hoja), '[Calendario Horno *])

or I can also create an array and store it into a variable that has those 2 or more values, by loading them before from another table in the same Excel, but I don´t know if that is possible. Or is there another way? At the end, I need to automatize the From in Excel with changing name values that have a very similar name (table 1, table 2, table 3, usually has a common string but different value, so table *?)

Thanks in regards.

[Mark_Little]

You can loop through file in a similar way with 

for each file in filelist('lib://YourConnectionName\*.qvd')

    YourTableName:

    load

    YourField1,

    YourField2

    from [$(file)](qvd)

next file

[Mario_De_Felipe]

Hi @DiegoF ,

yo can reference Excel sheets by their number using @n like this:

LOAD *

FROM [myfile.xls] (biff, no labels, table is @2);

Best regards,

Mario

[DiegoF · Accepted solution]

Hi fellas,

I got a solution for my problem. So I wrote down the names of the sheets in a new sheet, and load it first in a table with the dimension as ListSheets, and then, in the For Each, I used in FieldValueList('ListSheets'), so it iterates over this dimension I have previously loaded, so something like this:

TABLE_1:
LOAD
     Sheets        AS ListSheets
 
FROM EXCEL1.xlsx
(ooxml, embedded labels, table is Indice);

// Then we use it in the for each
For Each Hoja in FieldValueList('ListSheets')

xx xx

From EXCEL1.xlsx
(ooxml, embedded labels, table is $(Hoja));

NEXT;