Help Needed with Creating Batch Macros for Combining and Cleansing .txt Files

[mohit9garg]

Hello,

I'm relatively new to building Match Macros in Alteryx and would appreciate some guidance.

I'm working on a solution that involves two batch macros:

1. Macro 1: Combines multiple tab-delimited .txt files. These files may have varying numbers of columns.
2. Macro 2: Processes one day's worth of data at a time and applies data cleansing steps.

I've illustrated the problem statement in the Visio diagram below for better clarity.

Any suggestions, examples, or best practices would be greatly appreciated!

Thank you in advance.

[caltang · Accepted solution]

This helps: https://knowledge.alteryx.com/index/s/article/The-Ultimate-Input-Data-Flowchart-1583459854309

[mohit9garg]

Thanks @caltang,
I can read all the files which have different columns using the batch macro you suggested. Now how can I read one day's data at a time?

[caltang]

You can either do an active execution or a passive one.

Active wise, you can create an analytic app to feed the data and execute manually as you would a normal workflow but just with a friendlier UI.

Passive wise, assuming you have the server, and that your server has access to your folder storage, you can create a tweak in the workflow to call data that is matching each new day, and can schedule for its execution according to your needs.

[mohit9garg]

Thanks @caltang for the suggestion.

Could I also implement a batch macro? The batch macro would initially filter the files for one day, and then the above macro (what you suggested) would combine the data. This process would repeat for the second day, and so on. Do you think this approach would be effective?

Regards,

Mohit

[caltang]

You mean building a macro to detect if there’s a new file everyday and execute if there’s a new file? 

That requires more work and tinkering… also need to know if you’re scheduling it via Server or are you designing purely on Desktop? 

If the latter, you can use a Detour tool (stored in a batch macro) with the batch condition created via a Formula tool that helps identify which path of the detour it goes.

Something like my reply here: https://community.alteryx.com/t5/Alteryx-Designer-Desktop-Discussions/Workflow-to-load-files-how-to-...

[mohit9garg]

@caltang 

I need to execute a workflow at the end of the month using data from the entire month. Given the large data size of 10GB per day, I plan to process one day's data at a time to apply filters and other operations. I will then append the filtered data for each day.

I will be running the workflow from my desktop and will not be scheduling it.

[caltang · Accepted solution]

I see. Then I would suggest to have your process inside the batch macro with the first filter after your macro input to be a date filter. 

For your input into the control parameter, I would use a summarize tool to group by all unique dates so that it can feed into the macro by date as you want. 

Make sure to have the batch macro configurations set properly so that they stack up by name as an output