Input multiple excel files, perform same transformations, write to separate output files

[akshatrajj]

I have multiple excel files in a folder, each having different structure (number and names of columns). I want to read these files one-by-one, perform same specific transformations on each of them (like transpose- all the columns, sort, etc.) and then output the results in separate txt/excel files, one-by-one. 

How can I automate this entire process?

[BrandonB]

Have you built a macro before? You can add a control parameter and an action tool to your input and then feed in a list of file paths with a directory tool. You will also want to use use the option in the output data tool to take file/table name from field so that you can dynamically generate output files. Here is a training video on building your first macro:

https://community.alteryx.com/t5/Videos/Build-Your-First-Batch-Macro/td-p/52900

[Qiu]

@akshatrajj 
Just to add to @BrandonB , there are several macros/sample I picked up from here for you to real multi Excel files for different situations.

[akshatrajj]

I have built a macro before through resources available on community. Please refer the screenshot below:

Using this macro, I'm able to dynamically read various input files. But before I can apply the transformations, I find that the data from the different files is combined into one dataset (Please refer the results of the output tool). In this manner, the transformations are all applied to the combined data instead of each dataset separately. 

[Qiu]

@akshatrajj 
How about put an Output tool inside your Macro?

[akshatrajj]

In that case, I would be unable to apply the transformations that I require to be applied to the data before it is dumped in the output files.

[Qiu · Accepted solution]

@akshatrajj 
I mean all the necessary action to be included into macro and output of Macro is only dummy.

[Qiu · Accepted solution]

@akshatrajj 
Modified the workflow by @BrandonB  so maybe you can get the picture here.