Input Excel File Based on Name

Question

Hello,

I have a folder in Windows where excel files with the same format / structure are deposited but the name changes each day as this file is refreshed daily. For example, the name of the excel file is BS_D_25-Apr-2024 today, will be BS_D_26-Apr-2024 tomorrow etc.

What I want to do is to bring in the latest file each day, so on 25th I want to bring in the BS_D_25-Apr-2024 file then tomorrow when I run I want to bring in the BS_D_26-Apr-2024 in an automated way rather than manually importing - is there a way to do this?

I looked online and was directed to the Dynamic Input tool. I got a directory tool then used a dynamic input tool but struggle to use this has I don't have a data source template.

Another thing I tried was to get the file path name of the latest file in the folder however once I have this path I can't seem to tell the dynamic input too that this is the file that I want to import. To do this, I used the directory tool, then a sort tool to have the last write time in descending order so I can see the latest file. Then I used the select sample type too to get the first row and then a select tool now gives me the full path of the file I need. However I am stuck at this point as don't know how to tell Alteryx to bring in this file

Can anyone help me please? I'm sure there must be a simple solution to this as it can be done in Power Query in Excel so I would have thought Alteryx would have a way to do it?

Thanks!

Gaurav_Dhama_ · Answer

Your tools setup should look something like this (appologies I cannot post a snip of it)

[Directory tool] > [filter tool] > [dynamic input tool]

as mentioned in the above replies, directory tool will get all the files in the directory, filter tool with formula as mentioned by aatalai. Dynamic input tool you can configure to change the entire file path.

rnp90 · Answer

Hello,

Thanks for your response. I got up to the formula part so I now have a full file path.

It is the next step now that I still don't understand (I am relatively new to Alteryx so apologies if this sounds silly).

Now, you can use the file path obtained from the previous steps as the input source for the Dynamic Input tool. Connect the Dynamic Input tool to the Input Data tool to read the latest Excel file dynamically

- I added an Dynamic Input tool after where I just got the full file path but the input source only says "Connect a File or Database". When I try writing the column name of the full file path which I have now nothing happens, it seems like it is asking for a file?

- Also my input tool only as a connection to the right, it doesn't allow any connections to come into it? Do you mean I add an old template to the input tool then the dynamic tool will amend this to the latest version?

aatalai · Answer

setup a filter after the directory tool with the following expression

contains( [field], datetimeformat(datetimenow(),"%d-%b-%Y")