Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Save the Date: QlikWorld Online, June 24-25, 2020. Free global virtual event for data integration and data analytic gurus. Register Today
Highlighted
Contributor III

Constantly Changing Excel Sheet Name

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]);

Tags (2)
1 Solution

Accepted Solutions
Highlighted
MVP & Luminary
MVP & Luminary

Re: Constantly Changing Excel Sheet Name

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

View solution in original post

5 Replies
Highlighted
MVP
MVP

Re: Constantly Changing Excel Sheet Name

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

  • .
  • Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
    Highlighted
    MVP
    MVP

    Re: Constantly Changing Excel Sheet Name

    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)], ...)

    Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
    Contributor III

    Re: Constantly Changing Excel Sheet Name

    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

    Highlighted
    MVP & Luminary
    MVP & Luminary

    Re: Constantly Changing Excel Sheet Name

    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

    View solution in original post

    Highlighted
    Contributor III

    Re: Constantly Changing Excel Sheet Name

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