Dev Space

Customize & extend the power of Alteryx. SDKs, APIs, custom tools, and more!

Multiple Excel files different Sheet Names

5 - Atom

Hi Guys,

 

I am new to this community and was facing issue while making workflow. I need to extract data from multiple excel files which has different sheet names but schema is name. Is there any option in Alteryx to dynamically rename those sheet name and consolidate data.

Highlighted
Alteryx
Alteryx

Hi @prateek29c 

 

Are all of these files in the same folder? If so, I would recommend looking into the directory tool. This tool used with the dynamic input tool allow you to read in multiple files at once.

 

Here is a community article that shows how these two tools can be used together in a powerful way: Directory -> Dynamic Input

 

In the situation that sheet names are different, importing the sheet names and using a dynamic input tool could be a step towards the right solution as well.

 

LukeG_0-1576590031417.png

 

Let me know if you have any questions! If you are able to provide more details on your use case, we will be able to help you find a more exact solution.

 

Luke

Highlighted
5 - Atom

Hi Luke, Thanks for your reply. Let me explain what am trying to do. We have all the files in the same folder. Have attached worksheet1 and worksheet2 for your reference. Name of 1st tab in each worksheet are same but the problem is with the second tab where schema is same but sheet names are different. I want to get all data combined in the format shown in the attachment "Output2" where currency column is already there and we need to populate data accordingly from each file as per mentioned currency.

 

I did try Dynamic input tool + Directory tool but couldn't get success.

 

 

 

Highlighted
Alteryx
Alteryx

Hey @prateek29c 

 

Here is a workflow with a solution using the sample files.

 

This leverages a batch macro. This macro reads in each file from the specified folder and selects the second sheet to add to the final output table.

Directory tool -> Batch MacroDirectory tool -> Batch Macro

 

Inside the Batch MacroInside the Batch Macro

 

When creating a table for all Sheet1s, we can use a directory tool, formula tool, and dynamic input.

Directory -> Formula -> Dynamic InputDirectory -> Formula -> Dynamic Input

 

Please let me know if you have any questions about the workflow. Package attached.

 

Luke

Highlighted
5 - Atom

Hi Luke, Thanks a lot for your help. The problem is I am not able to run this workflow at my workplace as I have Alteryx lower build version installed - alteryx 2019.1.6.58192. Is there any way where I could run this workflow using this version. I tried making workflow using the given screenshots but couldn't get success. Anyways will try again. Again thanks for your help.