Read one Excel with one sheet where the sheet name changes

[edwin_isensee]

Hi, I need to read, on a daily basis, one Excel file. The file name is fixed, the columns are fixed and the file contains only one sheet. The problem is: the sheet name contains the number of records on the file, and change from one day to the other day. For example, possible names are 'Rows 1 to 1232', 'Rows 1 to 5429', 'Rows 1 to 15',.... Although I can't say I have an infinite number of sheet name options, it's a pretty big list.

Is there a way to read this file in Alteryx?

Thanks

[jamielaird · Accepted solution]

If your Excel file is an XLSX then you can simply use the 'Read Sheet Names' option in the Input tool to get the sheet name and use this to construct a file path.

If your file is an XLS (which I'm guessing it probably is) then the 'Read Sheet Names' function doesn't exist for this file type, so the solution will require a bit more creativity.

One option is to write a batch script which you can run from a CMD tool within your workflow to invoke a VBA function to rename the sheet directly in your source Excel file (or a copy if you need to preserve the original file).  This would require a bit of coding along these lines:

https://stackoverflow.com/questions/17050457/renaming-and-formatting-excel-worksheet-with-batch-file

There's an Idea thread to allow wildcards in Excel sheet names which is worth an upvote, as this would be an easier solution:

https://community.alteryx.com/t5/Alteryx-Product-Ideas/Allow-the-use-of-Wildcards-on-sheet-names-in-...

Perhaps someone else will be along with a better solution, but I hope that helps.

[edwin_isensee]

The file is XLSX. I will try your suggestion.

Thanks

[Luca]

@jamielaird can you please extend the instruction for your first suggestion? I am new to Alteryx and I don't think I can find a  'Read Sheet Names' option.

Thanks!