Read several excel files and loop workflow until all files have been processed

[C9272]

Dear All,

I would like to achieve the following in Alteryx:

1. Read in several Excel files from a specified folder
2. Extract the date (e. g. 20210720) from the excel file (date is contained in the file name of each excel file; see simplified example files below) and append this date in a separate column named "reporting period"
3. Run my "normal workflow" for each single Excel file
4. Repeat steps 1.-3. until all excel files contained in the source folder have been processed and run through the workflow
5. Union the results
6. Write the results back in a single excel table with one sheet containing all the results and delete all previous records contained in the excel table.

Example Input Files

All input files share the same file structure.

File 1_20210720.xlsx

Product	Value
Tomato	100
Apple	250
Potato	150

File 2_20210615.xlsx

Product	Value
Apple	100
Apple	250
Pineapple	150
Apple	100

I have tried so far to use the "dynamic input tool" as well as the "Alteryx Holiday Gift of 2015 Read all excel worksheets macro" (https://community.alteryx.com/t5/Engine-Works/The-Ultimate-Alteryx-Holiday-gift-of-2015-Read-All-Exc...)  for the above mentioned purpose. Unfortunately these seem to be reading in all excel files first and afterwards run the "normal" workflow (once). This leads to wrong results in my workflow as this e. g. contains several summarize tools in which I would then have to include the "reporting period" as a "group by" criterium. As my workflow has become quite complex in the meanwhile, I would like to avoid this extra effort.

Instead, I would like to loop the workflow until each excel file has been read in, processed and write the results back to a single excel table that gets updated weekly.

Any help would be greatly appreciated!

Thanks in advance

Christopher 

[C9272]

Hi @KilianL, 

Thank you very much for the ongoing support. 🙂

Now I have been able to replicate the solution in my workflow and it works as intended. 

Kind regards

Christopher

[lbjohn]

Hello,  can you post the designer screenshot of the BatchExample2?  My company is on 2020.4, so I am not able to read your file.  I just want to see which tool you use to dynamically process the different Excel files.

Thanks!

[C9272]

Hi @lbjohn,

Basically the Yxzp file is just a kind of a zip file. You can extract the file using e. g. Winzip or 7-zip  and then follow the steps below for the yxmd file after extraction: 

Open the .yxmd with an app like notepad or any similar text editor, then change the second line to match your version

You can find your version under the menu option Help->About

On the first lines inside the .yxmd file, you will see the version of the program

<?xml version="1.0"?>
<AlteryxDocument yxmdVer="2019.2">   Change the bold number here 
<Nodes>

I also attached the screenshots of the Workflow (workflow + macro). 

Kind regards

Christopher