Inputting data from 50+ sheets from an excel.

[Siddhantt_1999]

Hi,

I needed help in inputting data from 50+ sheets in excel and performing a union on all the data. All the sheets have the same header names but might have different column order in each sheet so can't union by position and need to union by header names. I'm guessing a macro would be needed for this but can someone suggest how this can be achieved? Attaching a sample excel for reference (with only 4 sheets as it is only a dummy file)

Thank you for your help in advance!

[DataNath · Accepted solution]

Hey @Siddhantt_1999, you're absolutely right that you'll need a macro here - a batch macro.

Inside the macro we set up a standard connection to just one of the sheets, which acts as a template where we can run N iterations of the macro, replacing the sheet name each time until our Excel file is fully read. The Control Parameter then feeds into the Input Data tool, with the Action tool set up to change the sheet name of whatever the template sheet is called - in your case, Sheet1. We also set up the Interface Designer (Ctrl+Alt+D) to Union the inputs by Name so that Alteryx will automatically reorder them and not error due to differing positions:

In the main workflow, we read in our Excel sheet but instead of selecting a single sheet, we just get Alteryx to print a list of the sheet names. This then feeds into the Control Parameter of the batch macro so that each iteration gets replaced with the next sheet name until the list is complete:

The result? All 4 sheets nicely unioned!

Hope this helps! I've attached the packaged workflow used here for you to look at and play around with. NB: I did delete a duplicate Product Name field from your Excel as I assumed that was added in error.

[Siddhantt_1999]

Hi @DataNath, thank you for sharing the solution. This indeed works for me, I was not changing the output mode from the properties pane and hence was getting the wrong output but now the workflow is working perfectly fine.