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

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

Diego_780 — Mon, 17 Oct 2022 07:44:11 GMT

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.

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

Mark_Little — Tue, 25 Oct 2022 06:20:45 GMT

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

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

Mario_De_Felipe — Tue, 25 Oct 2022 06:51:01 GMT

Hi @Diego_780 ,

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

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

Diego_780 — Tue, 29 Nov 2022 09:52:01 GMT

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;