Input multiple sheets from one excel file with slightly different schema?

[gwiz]

I am trying to input one excel file but it has about 20 different sheets all with similar, but slightly different schema. I would like to stack the contents and go from there...

This seems doable to figure out with some help. Thanks!

[Thableaus]

Hi @gwiz 

This article is very helpful on understanding what you need to do.

https://community.alteryx.com/t5/Alteryx-Knowledge-Base/CS-Macro-Dev-Reading-in-Multiple-Files-with-...

Cheers,

[DanM · Accepted solution]

@gwiz 

Since you have spreadsheets with different schema, you will have two options. You can either bring them in using header position or header name. In order to do this, you will need to create a batch macro.

The batch macro will continue these tools:

Input Tool

Control Parameter

Action Tool

Macro Output.

Step 1:

Drag Input tool on canvas and configure the tool with one of your files you will want to being in.

Step 2:

add a Control Parameter tools above the Input tool and connect to the top of the Input Tool. An Action tool should automatically appear on the canvas.

Step 3:

Select the Action tool. In the configuration window, you will see the connection string to your file. Select that string. If you look at the bottom of the configuration window, you will see that string populated in the window.

Step 4: in the configuration window of the Action tool. select at the bottom "Replace a Specific String". Here is where you will decide what you will want to update in the string. If all of the files you want to being in are in the same folder, remove everything in the window, but the file name. This is telling the Action tool to update just the file name of that string. If you have the files in different folders, then you will need to replace the folder and the file name in the string.

Step 5: Add a Macro Output Tool

Step 6: Go to the top of the program and select View. You will see an option to choose Interface Designer. Select that, and a new window will open. Select the gear in that window and scroll down. You will see an option for Output Mode. This is where you can change how the macro reads your data.

Step 7. Do a Save As onto your local machine. This will create an Alteryx Macro file .yxmc. Don't close the workflow

Step 8. Open a New Workflow and add a Directory tool and browse to the folder where you have the files saved. If you have them saved all over the place, then you will need multiple Directory tools.

Step 9. Run the workflow with the Directory tool to make sure you have all the files you need.

Step 10. Right click on the canvas and and select "insert", you will see an option at the bottom of the window for "macro". Your macro should be there. Select that and it will add it to the canvas.

Step11. Connect the Directory tool to the Macro. Select the Macro and choose the File Name field and run the Workflow. Make sure you add a Browse tool after the macro as macros have a tendency to not show data out of the macro without the Browse tool.

Step 12. Run the workflow, all of your data will be Union'd on top of each other based on the preference you chose in the Interface Designer.

Couple of things to note:

The most important piece to this is steps 3 & 4. This is telling the Input tool what it will receive to update. So the data you push to the macro must create the correct string to pull in the data. Be aware that if your sheet names are different also plays into this. So it may not be as simple as just adding the Directory tool to the macro. You may need to recreate the string you need to push into the macro.

Lastly, if it is easier, you can update the entire string instead of pieces of it, but again be careful of the sheet name. You may need the Formula tool to help you add the Sheet names. Don't forget Alteryx need the three pipes to separate the sheet.

I have attached a package sample. This sample will not work since you don't have the files, but at least it will help you follow along as you build it. TO OPEN the macro, just right click and choose "open macro". Another workflow will open with the inside of the macro.

[gwiz]

@DanM and @Thableaus thank you both for the replies. Sorry for the late responses, but I appreciate that you answered the question. Figure I would mark it!