Dynamic File and Sheet Selection

[Will-G]

Hi,

I was wondering if anyone could help with dynamic file and sheet selection?

I've been asked to create a workflow that reads the most recently created excel file in a folder and the most recently created sheet in that file. The file names are identical except for an ordering number at the start and the date of the file (e.g. Mar 2021) at the end.

I've tried doing this with the directory tool, some sorting and the dynamic input tool (see attachment). Unfortunately I can't quite figure this out and I'm getting an error on the dynamic input tool saying 'Unable to open file for read'.

I'm new to using Alteryx, so any help or suggestions would be great. Thanks! 

[bensilv]

Hi @Will-G, could you send a screenshot of the configuration for the dynamic input tool?

Judging by the error message, it looks like the tool might be pointing towards a folder instead of a file.

[Will-G]

Thanks bensilv. I think you're right that it's pointing towards a folder (see screenshot below).

Is it possible for the dynamic input tool to read the newest file from this folder? This is essentially what I've been tasked with doing (along with the latest sheet).

[bensilv]

Yes absolutely. 

What the dynamic input tool needs is a template to match to and populate. In the configuration you have, you have not selected a valid input data source template - this should be a matching template to the file you're bringing in.

Please see here - https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Input-Latest-File-from-a-folder/m-p/84...

[Will-G]

Thanks again, I think I understand the dynamic input tool more now. I'm now getting a 'no sheet specified' error, which I did have previously.

The workflow I've created looks like the following, but I also need to figure out how to dynamically choose the newest sheet. Could you recommend a way to do this if possible?

[bensilv]

Is the sheet specified in the formula tool? So the field FullPath, does that have a sheet name at the end?

And is the sheet specified in the template?

[Will-G]

I created a new field that has the full file path with '|||' and the sheet name added.

And I think this matches the sheet specified in the template. I selected the 'Mar 21' sheet, which was the sheet name added.

[bensilv]

That format looks to be correct. When you added the template, did it run your through a wizard to select a sheet?

i.e. is the table or query populated here?