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

[kal-tech]

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.

[stataltgee]

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!

[Qiu · Accepted solution]

@kal-tech 
I developed further based on flow of @stataltgee , 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.

[stataltgee]

@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)

1. 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?

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

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

Workflow-Test-r1.yxmd

1.  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?
2. What are we doing in update value here?

3. 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

[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.