Constantly Changing Excel Sheet Name

[haymarketpaul]

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

[jonathandienst]

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

.

[jonathandienst]

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

[haymarketpaul]

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

[marcus_sommer · Accepted solution]

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

[haymarketpaul]

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