Dynamic File and Sheet Selection
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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!
- Labels:
- Dynamic Processing
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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).
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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...
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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?
