This site uses different types of cookies, including analytics and functional cookies (its own and from other sites). To change your cookie settings or find out more, click here. If you continue browsing our website, you accept these cookies.
I am trying to get Alteryx to dynamically combine the last month of daily .txt files into one monthly file.
Each day, a report is automatically dropped into a folder. This folder holds daily files from an entire year (so, a rolling 365 files always). I need to look at July 2020's daily files and combine them into one "monthly" file. I will need to do this for every month going forward. My goal is to automate this process on the 1st day of every month. So, on September 1, alteryx would automatically create this monthly file for August.
For example, the 7/1/2020 file name would look like "Report_20200701_to_20200701.txt" (I did not choose this name and can't change it unfortunately). The date in the file name will correspond to the date it was created. The CreationTime field, of course, would also correspond. The number of files for that month would equal the number of days in the month. For July, I would want the 31 files, starting with "Report_20200701_to_20200701" through "Report_20200731_to_20200731". Then, these files would be combined into "Report_072020" (in txt or xlsx) or something. I don't even care if they're written in order, although that could be nice.
What is the best way to get Alteryx to look into that folder path, find the most recent month's files, and combine them into one?
You can use a directory tool to pull in the list of file names in a folder, extract the date from the file name, use a date time tool to convert to a date, filter date for the current month, and then feed the results into a dynamic input tool. This should stack all of the data that you are looking for.
Thank you for answering Brandon!! I appreciate your help. How would I extract the date from the file name? I'm also not sure I understand what the formula would look like to pull current month. I want to be able to run it every month without editing this formula, if possible.
Here you go. Basically you just use a formula tool to pull the date from the file name using Left and Right functions. Then you use a date time tool to convert the extract to a date. Then you dynamically pull in the number of last month using DateTimeMonth(DateTimeNow()) - 1. Then you take another DateTimeMonth and apply it to the date of the file. Finally a filter where last month = month of file will only allow files to pass through from the previous month. You then feed in the list of file paths into a dynamic input tool and it will stack them all. Workflow is attached for reference.
This worked perfectly! Some of my files ended with .txt and some with .txt.gz so I just duplicated the workflow, tweaked the date extract formula for one of the flows, and then used the union tool to bring the flows together into one file. Thank you so much!!!!