Dynamically Input Last Month's Daily Files to Combine into One Monthly File

Hello!

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?

Workflow

Datasets

Input

Reporting

Macros

Date Time

Common Use Cases

Transformation

Batch Macro

Dynamic Processing

Join

Preparation

Data Investigation

Accepted answers

BrandonB

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.

month extraction.png

month extraction.yxmd

All comments

BrandonB

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.

devonevan

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.

BrandonB

month extraction.png

month extraction.yxmd

BrandonB

In my example, nothing will need changed month over month, because the DateTimeNow() function pulls in the current date time every time the workflow is done. That means your process will be dynamic!

Ksisterhen

hi - have a similar question wanting to combine the data for daily files into 1 and have created the below workflow, the export just shows the file data how to i get an export of the data within each of these files in 1 file?

Capture.PNG

BrandonB

Hi @Ksisterhen you will want to use a batch macro approach. You can read about how to do that here: https://knowledge.alteryx.com/index/s/article/The-Ultimate-Input-Data-Flowchart-1583459854309

Essentially you will pass the column of file names into the macro which will extract the data for each file and stack the data from each file on top of one another.

Ksisterhen

I have gotten the batch macro to work, now have a question on how it works

how does the batch marco layer on? i.e if the files in the folder it is combing goes in decending order is from 12/29-12/1, is it bringing in all the data from the 12/29 file then the 12/28 then the 12/27 and so on?

Each file has a list of unique ids, some carry over from day to day some get added and i am trying to get a unique list of unique ids in the monthly files and the date each one first appeared

for example uid:12345 shows in my 12/4 file and in my 12/5, 12/6 and 12/7 file, in my unique output i would like it to bring the record from the 12/4 file not from another day file.

as well as uid:78923 shows up first on 12/7 then in the 12/8,12/9 file so for this id i want the date from the 12/7 file to show in my unique output file

BrandonB

@Ksisterhen the best way to do this is by selecting the option to pass the file name as a field in the Input Data tool that is within your macro. This will create a new column with the file name. Then after the macro you could do a sort tool on file name to ensure that the data is in the order that you expect, and then use a unique tool after the sort where you check the box for your uid field. This will then take the first instance of every uid field.

Quick Links

This months top contributors

hhaigh1 280

FláviaB 262

JORGE4900 235

caltang 177

DanM 167