How To Combine Multiple Excel Files Into One In Alteryx

Multiple Excel Sheets into Alteryx

This weblog gives a quick tutorial on how to load in multiple sheets from one Excel file into Alteryx all at the same time rather than manually loading them in one by one. In that location is likewise an extra section at the end which goes through how to append the sheet name onto the merged data table.

QUICK HOW-TO

Kickoff of all bring your excel canvas onto the sheet using one of the post-obit methods:

a. Elevate and drop Input Information tool, click on the Connect a File or Database driblet down and select your Excel file

b. Drag and driblet your Excel file directly onto the canvas from your file explorer

Either i of these methods will bring you to the Select Excel Input config where the default option is Select a canvass.

And so at this point you could load in each private sheet one past one but this starts becoming boring if you have more than than a few. Luckily there is a much easier way! Instead of selecting a sheet to bring in, choose the last option to 'Import but the listing of sheet names'

After y'all click OK and run the workflow, in the results window you'll meet it returns one field with a row for each sail name

Now for the key pace, we bring in the Dynamic Input tool and configure equally the post-obit:

Again, find the file path where your Excel file is saved. You will get the aforementioned Select Excel Input config every bit you did in the beginning. Just choose the first option 'Select a sheet' and choose whatever one of your Excel sheets.
Edit the Field to 'Sail Names' which should exist the only option from the drop downward menu

Click run and voila! The output we get is a marriage of all our sheets pregnant the sheets will get stacked on height of one another vertically so it's important that all the Excel tabs have the aforementioned schema i.e. they should have the aforementioned ordered columns and corresponding data types.

It may be useful to too include the sheet name every bit it's own column and so that we know which rows came from which sheet. The easiest manner I have found to do this is as follows:

Go through the same steps as to a higher place until the Dynamic Input configuration. Now instead of selecting Sheet Names for Field, we instead want to select the Change SQL Query selection then click Add on the right hand side.

Now we demand to do two things here; firstly, replicate what the first method did in loading in all sheets and so nosotros want to append the sheet proper noun.

And so the get-go choice we want to make is 'Replace a Specific String' which will create our table

A new config box volition pop upwards but leave everything as the default and click okay, you should accept something similar to the following in the query box

Next we want to get back on that Add driblet down and select Pass a Field to the Output. Again leave the default option in the config and click okay, this is where we are asking the Sheet Name to be added to the table