topic Re: Excel WildCard Load from sheets in xlsx in QlikView

Excel WildCard Load from sheets in xlsx

avastani — Sat, 03 Mar 2012 02:59:32 GMT

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

Re: Excel WildCard Load from sheets in xlsx

Anonymous — Sat, 03 Mar 2012 15:27:13 GMT

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

/Marcus

Re: Excel WildCard Load from sheets in xlsx

avastani — Sat, 03 Mar 2012 15:28:56 GMT

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

Re: Excel WildCard Load from sheets in xlsx

Sat, 03 Mar 2012 19:05:01 GMT

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;

Re: Excel WildCard Load from sheets in xlsx

avastani — Sat, 03 Mar 2012 19:06:22 GMT

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

Re: Excel WildCard Load from sheets in xlsx

Sat, 03 Mar 2012 19:40:29 GMT

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