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

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?

Workflow

Datasets

Batch Macro

Dynamic Processing

Preparation

Accepted answers

Qiu

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

Qiu

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

completed_workflow.yxzp

All comments

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.

Multi Sheet Import Example.yxmd

Dynamic Multi File Sheet Names.yxmc

Import Multiple Schema xlsx.yxmc

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.

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703