Excel WildCard Load from sheets in xlsx

[avastani]

is it still possible to do Wildcard load from xlsx files?

We could do that no problem from xls files but I am having trouble with xlsx files

Anyone have trouble with that

[Anonymous]

I have not encountered any problem with that. Try if this one works for you (see attached file)

/Marcus

[avastani]

wildcard only work for filename. it doesn't work for sheetname anymore.

[#4]

You can use an ODBC connection to connect to the Excel file though and return the list of tables (sheets) from the file. You can then loop through them. Something like this:

ODBC CONNECT TO [Excel Files;DBQ=ExcelFile.xlsx];

XlsTables:

SQLTables;

DISCONNECT;

LET vRows = NoOfRows('XlsTables');

FOR i = 0 TO $(vRows)-1

LET vSheetName = subfield(peek('TABLE_NAME', i,'XlsTables'),'$',1);
     
$(vSheetName):

LOAD *
FROM [ExcelFile.xlsx]
(ooxml, embedded labels, table is '$(sheetName)');

NEXT i

DROP TABLE XlsTables;

[avastani]

that is what i am doing as a workaround but looking for something more efficient and clean

[#6]

I don't believe there is any other way. Wild cards definately don't work within the sheet name.