Multiple Excel files different Sheet Names

[prateek29c]

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.

[LukeG]

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.

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

[prateek29c]

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.

[LukeG]

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 Macro

Inside the Batch Macro

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

Directory -> Formula -> Dynamic Input

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

Luke

[prateek29c]

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.