topic Re: Constantly Changing Excel Sheet Name in QlikView

Constantly Changing Excel Sheet Name

haymarketpaul — Mon, 07 Sep 2015 14:24:42 GMT

I'm sent an exported Excel file with one sheet in it once a week but there is an ever changing number at the start of the Excel sheet (6612 in my example below).

Is there a way to Wildcard that part of the sheet name? Or another solution to be able to read the sheet regardless of its name.

LOAD

FROM

(ooxml, embedded labels, table is [6612_flash2_gotfile-5]);

Re: Constantly Changing Excel Sheet Name

jonathandienst — Mon, 07 Sep 2015 14:34:01 GMT

I seem to remember that you could wildcard the sheet name for an xls file, but I can't remember if the worked for xlsx as well as xsl format. The wildcard would open the first sheet.

Try table is * or table is

Re: Constantly Changing Excel Sheet Name

jonathandienst — Mon, 07 Sep 2015 14:39:55 GMT

Failing that, you can open the spreadsheet using an Excel ODBC connection, and then use the SqlTables function to return the sheet names:

connect to ......; // ODBC Excel connection to file

Sheets:

sqltables;

Let vFirst = Peek('TABLE_NAME', 0); // assume that you want the first sheet....

DROP Table Sheets;

LOAD * From ????.xlsx

(ooxml, sheet is [$(vFirst)], ...)

Re: Constantly Changing Excel Sheet Name

haymarketpaul — Mon, 07 Sep 2015 14:41:40 GMT

Thanks for reply.

Having since read a couple of other posts it sounds as though that functionality was removed at some stage.

Appears as though i'll have to use ODBC.....and i've just seen your other reply.....thanks i'll give it a shot

Re: Constantly Changing Excel Sheet Name

marcus_sommer — Mon, 07 Sep 2015 14:54:01 GMT

If it's the first sheet you could leave the sheet-name and use (ooxml, embedded labels) as file-format - in older excel with xls as format you could use @n and a number for the sheet which you want - unfortunately is seems that this functionality isn't available with xlsx anymore - unless someone knew it better.

And of course the suggestion from jontydkpi will work and had further possibilties like reading from secured files with user and password, too.

- Marcus

Re: Constantly Changing Excel Sheet Name

haymarketpaul — Mon, 07 Sep 2015 15:02:58 GMT

Perfect Thank You - it is just the first sheet needed and is simpler than using ODBC. Learnt some useful things. Thanks Both.