Alteryx batch macro to process Excel files and create multi-sheet outputs

[GiuseppeMarrucci]

Hi everyone,
I’m trying to build a workflow in Alteryx that should automatically process Excel files contained in a folder. The output of each processed file should be a separate Excel file that contains multiple sheets (one for each workflow), and the process should run for each file individually. Once the process finishes for one file, it should move on to the next file in the folder.

What I want to do:

1. Read Excel files from a folder: I want the workflow to read each Excel file present in a folder (one at a time) and process it individually. The folder might contain multiple files (for example, 10-20 Excel files), and each one should be processed separately.

2. Process each file: I already have a workflow in Alteryx that performs various operations on each file (about 5-6 processing steps). Each workflow produces part of the processing, and the result should be written to a separate sheet in the same output Excel file.

3. Create a unique Excel file for each input file: For each processed file, I want a new Excel file to be created with the same name as the input file (for example, if the input file is file1 the output file should be file1_output).

4. Generate multiple sheets for each file: Each output Excel file should contain as many sheets as there are processing workflows in my project (5-6 sheets). Each sheet should be the result of a separate workflow. For example, the output file file1_output might have the following sheets:

   • Flow_1

   • Flow_2

   • Flow_3

   • ...

5. Automate the process: I would like Alteryx to automatically run the workflow for all the files in the folder, and for each file, create its own output file with multiple sheets. Once the processing for one file is done, Alteryx should move to the next file and generate the corresponding Excel file with the same workflows.

The problems I'm encountering:

1. Dynamic Input Management: I’m trying to use the Dynamic Input tool to read each file, but I can’t seem to configure it correctly to read all the files in the folder one by one.

2. Managing output flows across multiple sheets: Even though I can generate the output for each workflow, I’m not sure how to ensure that each workflow gets written to a separate sheet in the output Excel file.

3. Automating the process for all files: I’m not sure how to configure the workflow so that it runs automatically on all files in the folder and that the result is written to multiple sheets in a single Excel file for each processed file.

Attached is a sample Excel file to illustrate its structure. I have also highlighted the areas to give a general idea of which information needs to be extracted and how it is organized.

Additionally, I have attached the workflow I designed to make the process clearer.

Please note that there are multiple Excel files, all with a similar structure, but some may have additional columns. It would be helpful to account for this variability as well.

Thank you!

[DanielG]

Hi @GiuseppeMarrucci 

1.) Dynamic Input accepts wildcard in name of excel files.  You can have filepath\*.xlsx to grab all the Excels HOWEVER the schemas have to match exactly and it can be a bit annoying and problematic to deal with differences.  Best bet is a batch macro to process each independently (outputting the file name in config) so you can process the data at once but still retain the individual reports identity as needed.  https://knowledge.alteryx.com/index/s/article/Getting-Started-with-Batch-Macros-1583461640393

2.) Just create a formula that adds sheet name to the output file path then "replace entire file path" in output config the workbook will be created with all relevant sheets and the applicable data assigned to each.  https://community.alteryx.com/t5/Alteryx-Designer-Desktop-Discussions/Output-Excel-with-Dynamic-File...

3.) Combination of 1&2 and server depending on the level of automation you need.

[OTrieger]

@GiuseppeMarrucci 

You will need  is a Directory tool, with a formula tool add \\\<List of Sheet Names>, Get into a Batch macro and get the list of files and the sheet names, replace <List of Sheet Names> with the Sheet Name. Now get the data into the next batch macro. In the batch macro you will have the steps to transform the data and also setting the path for each of the files and then writing them out, all in the batch macro