Need Help Configuring Directory and Dynamic Input Tools

I have a folder on my C: drive that contains monthly files with a naming convention like "Completed MTD 2024.01.01_EOM_January.xlsm". Each month a new file is saved to this C: drive location with the new date (1st of the month) and the month name (i.e. ....2024.03.01_EOM_February.xlsm, ...2024.04.01_EOM_March.xlsm). In the beginning of each month, I need to have alteryx look into this folder and take the newest file (which will be for the prior month from whatever it is run) and select that file and append the data on the "Data" tab to an Alteryx database file. How do I configure a Directory Tool and a Dynamic Input Tool together to achieve this?

Datasets

Accepted answers

apathetichell

Can you post your workflow - it's actually supposed to be your directory tool/filter/formula outside of your batch macro - with your input data/control parameter/action tool/macro output inside of your batch macro.

formula - will then go into your batch macro and be mapped via control paramter-> action tool-> input data to you the file/sheet combo field.

All comments

apathetichell

I would not use dynamic input - I would use a batch macro.

I would use directory to get the full path.

I would use a formula tool which would look be configured like:

"Completed MTD "+datetimeformat(datetimenow(),"%Y.%m.01")+"_EOM_"+datetimeformat(datetimenow(),"%B")+".xlsm"

- sample output - Completed MTD 2024.09.01_EOM_September.xlsm

next I would add a filter to find which full path from directory includes this name. If this exists -> I'd go back to Formula and add "|||Data" to the fullpath.

Next I'd throw it in a batch macro and get the sheet. I dislike Dynamic Input immensley - so I'd go that route. Batch macro would be:

input data (xlsm) -> macro output.

control parameter (call if path with sheet)-> action tool -> replace the file-value setting -> input data.

interface designer (auto union by name).

I have assumed all files will be XLSM - otherwise you'll need to have a parameter for xlsm vs xlsx an update this in the your batch macro. It's doable. It is not doable in dynamic input.

RCern

@apathetichell Thank you so much! I think I'm following, but I am getting stuck with how to tie the Directory/Formula Tools to the Batch Macro and then feed it into an Input Tool. I saved the entire file as a Batch Macro. Is that correct? Here is how I have it setup so far, I'm just a little confused how to tie it all together. Please see attached.

apathetichell

formula - will then go into your batch macro and be mapped via control paramter-> action tool-> input data to you the file/sheet combo field.

RCern

@apathetichell I got it working! Thank you for the clarification above!

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703