Read one Excel with one sheet where the sheet name changes

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

Common Use Cases

Accepted answers

jamielaird

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-Excel-Workbooks/idi-p/21442

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

All comments

jamielaird

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.

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-Excel-Workbooks/idi-p/21442

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!

Quick Links

This months top contributors

AkimasaKajitani 387

mceleavey 383

mbarone 337

Hollingsworth 335

LanisC 335