Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

avastani
Contributor II

Excel WildCard Load from sheets in xlsx

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

5 Replies
marcuspersson
New Contributor III

Re: Excel WildCard Load from sheets in xlsx

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

/Marcus

avastani
Contributor II

Re: Excel WildCard Load from sheets in xlsx

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

Not applicable

Re: Excel WildCard Load from sheets in xlsx

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
Contributor II

Re: Excel WildCard Load from sheets in xlsx

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

Not applicable

Re: Excel WildCard Load from sheets in xlsx

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

Community Browser