Inputting the latest file (excel) in a folder based on Modified Date

Question

Hello

Apologies if this is a simple task I am very new to Alteryx and trying to understand it's capabilities, anyways what I would like to accomplish is to extract the latest file version (by modified date) from a folder which has multiple files with similar names.  It would be even better if it could create a folder named "Archive" for all of the other versions.

The reason I would like to do this is because our client sends us monthly data sets which includes multiple versions of the same data and I would like to only pull out the latest one.  Here is samples of the file names (below), I have been using the input data tool with the wildcard * but it pulls all the data leading to duplicates.

In this case I would like to only pull out "01_Cost v2" and "02_Revenue Fixed" with the other files being archived.

01_CostModified date May 101_Cost Feb 14Modified date May 501_Cost v2Modified date May 2002_RevenueModified date May 102_Revenue FixedModified date May 20

grossal · Accepted Answer

Hi @nguy6184.

you can use the Directory tool to help accomplish this. It basically lists all files of a folder with including all the metadata. This also include the LastAccessTime and LastWriteTime.

I would recommend to use a formula tool to minimize the file name to the first letters (e.g. Left([Filename],4)). You could than sort the data by the time and use a Sample Tool with "Last N Rows" set to 1 and Group By option on the new field. This will remove all rows except the rows with the lastest files.

If the structure of all files is the same (what it likely is, because you were able to import it with the wildcard), you could use a dynamic input to read in all the files.

Another option would be the wildcard import you already do with the "File Name Option".

It will still import everything, but you could use the File name in combination with the process from the Directory-Tool to get rid of the data from the "old files".

Let me know if this helps 😃

Best

Alex