Loop Thru Excel files in a folder. Add a render tool to save data and charts to the file

I have two excel files in the folder. The Excel File "Sheet1" contains data. Open each excel file add a bar chart in sheet 1 and save it in the different folder. The File will have data and bar chart. I do not know how to dynamically read through the files in a directory and use the render tools to save the files.

1. Input File - Catsie.xlsx, Orient.xlsx (Folder - c:\test\input)

2. Output File name - Catsie-chart.xlsx, Orient-chart.xlsx (Folder - c:\test\output)

I attached the input files and Bar chart workflow.

Workflow-Test.zip

Developer

Accepted answers

Qiu

@kal-tech
I developed further based on flow of @Inactive User , since his is only for one file.
When checked your 2 sample input files, I noticed below.

1. The column name after company is changing "Month" or "Sale Month", I use a Dynamic Rename to change it to "SaleMonth"

2. Then I use the Split feature in Interactive Report after Transposing the data

3. After that, we can use a batch macro to process each file and get desired output.

Workflow-Test-r1.yxzp

All comments

unknown

Hi @kal-tech. You can use visual layout to add the bar chart. For output file u can select "choose a specific output file" from the render tool config. Also in the "visual layout tool" u can adjust the positions. Check this out!

Workflow-Test.yxmd

Screenshot 2024-08-05 100417.png

Qiu

@kal-tech
I developed further based on flow of @Inactive User , since his is only for one file.
When checked your 2 sample input files, I noticed below.

1. The column name after company is changing "Month" or "Sale Month", I use a Dynamic Rename to change it to "SaleMonth"

2. Then I use the Split feature in Interactive Report after Transposing the data

3. After that, we can use a batch macro to process each file and get desired output.

Workflow-Test-r1.yxzp

unknown

@Qiu That's great! I'll definitely check out.

kal-tech

Hi @Qiu:

I am trying to understand the workflow. I have few questions.

kal-tech.yxmc (Macro)

My understanding is that I will run this macro. It will read all .xlsx files from the input folder and create an output file in the output folder. Is this correct?

What do I need to update here “ Replace([FileName], "<List of Sheet Names>", [Sheet Names])”?

What is the below tool? Can you please explain? Im not sure.

Workflow-Test-r1.yxmd

When I run this workflow it created a “Output-File” folder and created “Catsie-Chart.xlsx” file . It is not creating “Orient-Chart.xlsx”file. Is there anything else I need to setup here?
What are we doing in update value here?

What is this RegEX is doing?

Replace(REGEX_Replace([FullPath],"(.+\\)(.+?)(\.xlsx)\|\|\|.+", "$1$2-Chart$3"),"Input","Output")

kal-tech

Hi @Qiu:

You have done excellent work putting the workflow together. I would appreciate it if you could explain it to me so that I can understand and use it.

Qiu

@kal-tech
Thank you for the feedback and happy to be any help.
I have added some annotation to the workflow, please kindly check.
Basically I am using a Batch Macro which can proceess the data in batch , which means proceed your Excel File one by one in your target folder.
Learn more about Batch Macro here.

https://community.alteryx.com/t5/Maveryx-Community-Resources/Apps-and-Macros-Guide/ta-p/1104327

https://community.alteryx.com/t5/Interactive-Lessons/Creating-a-Batch-Macro/ta-p/657923

Workflow-Test-r2.yxzp

Qiu

@kal-tech
I am thinking to submit this problem as a weekly challenge idea. Is that ok with you?

kal-tech

Hi @Qiu: Good idea. Please link to the page where you are planning to post weekly content. I would like to see the participants’ approach to resolving the issue.

kal-tech

Hi Qiu: I was running the Macro based on your solution. I am getting the below error message in the interactive chart "kal-tech.yxmc". The reason may be because I am unable to get the fields required for the interactive chart in the Transpose . Can you please help.

Error: Interactive Chart (24): You have found a bug. Replicate, then let us know. We shall fix it soon.

Workflow-Test-r2.zip

kal-tech

HI @Qiu : I figured it out and resolved the issue. Thanks again for your help.

Qiu

@kal-tech
Thank you for letting me.
Sorry that I did not reply your query in time, since it appears I was not properly mentioned.
And I am now preparing the document for submitting it as weekly challenge idea.

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15878

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703