Dynamic file names

[kimc745]

Greetings all,

I have a scenario where the file names will be a pattern such as Filename MMDDYYYY.csv.  I checked into the Designer > Dynamic Input tool and that won't work as I am not looking at multiple files with same schema - workflows should only process the current file.

Parameters:

Files will be produced on a monthly schedule with a semi-fixed name (file name - date is variable).  There will be multiple files of different schemas.  These files will be placed in a landing zone (either a SharePoint site or a network file share) and then be processed. 

Goal:

Automate as much as we can without being able to schedule.  We are also pretty locked down (no admin privileges on local machine) and I am not sure about the SharePoint connector (any knowledge or guidance would be appreciated on that but not goal for this inquiry).  The requestor is asking if we can version by date the files in event of reprocessing as the landing zone using a fixed file name would overwrite for input and output each time. I have suggested they move into archive input/outputs once run but am getting some resistance to adopt Alteryx for that.  Is there a way to look at a location and select the most current filedate or parameter in the filename?  Likewise, can we create an excel or custom report that suffixes a date to output?

Version:
Alteryx Designer Desktop - current version - no server, no cloud no scheduler or automated insights extensions.

[braveraj · Accepted solution]

So here are 2 scenarios you could possibly use. I know you said that the dynamic input doesn't work for you but not sure why. Anyways...

Scenario 1:

Directory pointed to the file depository path > Sort by created date descending > Sample 1 to top file > change full file path in dynamic input tool

Scenario 2:

Directory pointed to the file depository path > parse out file date from file name > convert file date to date type > sort by date descending > Join back on list of files to get most recent file based on file name date > change full file path in dynamic input tool.

You could also combines these methods so that way you are getting the most recent create date and newest file based on name in case there are multiple files with the same name or what not.

Hopes this helps!

[john_walker · Accepted solution]

Is there a way to look at a location and select the most current filedate or parameter in the filename? To look at a location on your network file share, you should be able to use the Directory tool. This will give you a list of all the files within that directory. From there, you can extract the date from the file name using regular expressions and turn that into an actual date field. From there you can sort descending and use the Sample tool to isolate the most recent file (based on the date field you created). But, you would need to use the Dynamic Input tool so you would need to know the schema. Is it possible to name the files based on the schema so that you could direct the workflow to the correct Dynamic Input tool based on the type of file/schema used?

Likewise, can we create an excel or custom report that suffixes a date to output? Yes, you can use a field from your data stream to name your excel file that you create via the Output tool. You could use a formula tool to create the full path to the file that you need to save. In the output tool, check the "Take File/Table Name from Field"

FilePath formula:

"C:\temp\abc"+DateTimeFormat(DateTimeNow(),"%d%m%Y")+".xlsx|||" + DateTimeFormat(DateTimeNow(),"%d%m%Y")

[kimc745]

Hi Braveraj,

I put this disclaimer on John's post as well. I am only an alteryx user for the last two months and my training has been through Alteryx lessons and online training and trial and error.  So basically, I am learning based on use case. 

Let me try your solutions and see if those work and if not, provide more specifics back. 

Thank you.

[kimc745]

Hi John,

Disclaimer: Very newbie to Alteryx. Have read multiple files (multiple input tools) and various joins to do validations and such; however, still learning the tool (on-the-job-as-I-go-training).  

When you say "need to know the schema", the csv files will have the same structure and column headers (need to keep those once they are loaded and start at a certain row).  Not sure I follow, would I need to create a template of the incoming file structure somewhere?

I will try the suggestions from you and the responder in the interim.

Thanks.

[john_walker]

To inspect a directory and dynamically import/read/open a specific file, you normally use the Directory tool in conjunction with the Dynamic Input tool. The Dynamic Input tools requires that you select a file that contains the structure/schema of the file you will be dynamically reading. In your original post, you referenced that you are working with files that contain different schemas. If you have files with different schemas in the same directory, my thought was maybe the naming convention is slightly different and you could filter based on part of the name so that you could then use the Dynamic Input tool associated with that certain type of file - because you have to specify a file that matches the schema for the file you are dynamically accessing. Hope that helps.

Screenshot showing "Input Data Source Template" - this is where I am specifying the file that matches the schema/structure of the file I am dynamically reading in from the directory tool.

[kimc745]

Okay, so, I was able to get most of solution 1 to work.  The input to Sample tool are the two files  ABC_05-05-2023 and ABC_05-10-2023.  The output are the same two files. 

Select First N rows

N = 1

Then the dynamic input tool I select a file with schema that will be the same (actually the same file as they are consistent named ABC_).  Selected Read a list of data sources.

Field Name = Creation Time

Action = Append Suffix to File/Table name

ERROR:  'ABC_05-10-2023_Sheet1$' 2023-05-10 10_46_39 does not match a sheet or named range in ABC - 05-10-2023.xlsx.  

[john_walker · Accepted solution]

This might help: https://community.alteryx.com/t5/Alteryx-Designer-Desktop-Discussions/Dynamic-Input-Error-quot-Does-...

[kimc745]

@braveraj @john_walker 

Thank you both for helping me with this.  Based on the last post the from John, I resolved the error I was getting when building off the first option provided by Braveraj.  

I so appreciate the patience and the expertise.