Combine multiple identical excel files

Hello Community

I have a system report that is generated weekly; same tab names and same schema. I want to combine the same bit of data from one particular tab so that I can see every week's data in the output Excel.

Here's a very basic summary of the output I want. Each Week column represents a different Excel file, all of which have the same data set.

	Week 1	Week 2	Week 3
Data Set 1
Data Set 2
Data Set 3

I've seen posts for combining multiple excel sheets from one file, or excel sheets with different schema, but nothing that's as simple (I think) as this.

Any help is very much appreciated!

Datasets

Accepted answers

jrlindem

I think a macro would be the best option, but if you're trying to avoid that, then I would suggest using the directory input tool with a dynamic import tool. Each file can be differentiated using the filename and then you can pivot the data once it's imported. Like this:

Since you have the luxury of same tab names, same schema; to your point, this is much simpler. I've attached a .zip file with some sample excel files and the workflow. Extract them all into the same location and then run the workflow.

-Jay

Combine_Multiple_Identical_Excel.zip

All comments

jrlindem

-Jay

Combine_Multiple_Identical_Excel.zip

Gunnar-Scheck

Hi IMERNDO,

I think the solution should be pretty straightforward. I would use a wildcard, and specify the sheet (assuming the sheet name remains static) in the input data tool. This way, you can add new files into the same folder. Make sure to include the file name in the input.

From there, I would add some modifiers/formulas to adjust the data as you might need. I would use the crosstab tool to include the data I want to see, and set the values for the new columns. This should work dynamically. Without knowing what the dataset is that you're looking to produce, I'll guide you towards reviewing the Cross Tab tool mastery to get started.

https://help.alteryx.com/current/en/designer/tools/transform/cross-tab-tool.html#id558393

https://community.alteryx.com/t5/Tool-Mastery/Tool-Mastery-Cross-Tab/ta-p/4368

caltang

If you want simple, you can follow this as well: https://knowledge.alteryx.com/index/s/article/The-Ultimate-Input-Data-Flowchart-1583459854309

IMERNDO

I'm not trying to avoid a macro; I was just looking for the simplest solution to something that, to me at least, seemed simple (albeit I couldn't figure it out myself!). If you guys think a macro is the best way, then a macro it is!

One oversight on my part; the Data Sets aren't uniform across each weekly file, e.g. one week may have Set 1, 2, 6 & 7; the next week Set 2, 3 & 4 etc. Where a set is missing from a week, I want a blank or zero.

Thanks!

jrlindem

@IMERNDO - No, a macro would be overkill. Like using a sledgehammer on a screw 🙂

The solution I provided actually accomplishes differences like you've laid out. Play around with the quantity of files, the naming of the files, and the inclusion/exclusion of rows. I made the following changes to the excel files I packaged together in the .zip:

And ran the workflow as is, with no changes and the results now show:

As you can see:

The new set names don't follow the same "SET" + Space + Number and still work in the list
Missing SET's in the series just create Null or missing values but stay in sequence
Gaps in the files week5 jumps to week9 are also still handled
And in this case week9 has two more values than the other files, which also work

Hopefully this can be adapted to suite your needs, but I do believe this meets the spirit of what you're after and I hope it helps you solve your need!

-Jay

caltang

@jrlindem hey! I love using sledgehammers on screws…

IMERNDO

Thanks Jay.

I've adjusted the Directory tool so it's picking up all the sheets, but the Dynamic Input tool is giving me the error "No sheet specified, you must specify a sheet" and I'm stumped. Any ideas?

I'm fairly sure that I've edited the input data source correctly; I can see the column headers in the subsequent CrossTab tool. And I've checked that every Excel file has a sheet with that same name.

jrlindem

Hey @IMERNDO It's likely because of the sheet name(s) inside the excel file. Make sure that your FullPath calls for the right sheet by name. It might look like:

'C:\folder\folder\folder\filename.xlsx|||report' <-- this last part with the triple-pipes is the sheet name and you might need to add a formula tool to append the right sheet name behind the FullPath field from the Directory Input before you go into the Dynamic Input.

Also check to make sure that you are configured correctly in the Dynamic Input Tool:

jrlindem

You can use the Dynamic Input tool to grab the sheet names too, if you need. It's a little more involved since you have to configure the behavior when setting up your "placeholder" file for the tool:

IMERNDO

The formula tool to add the triple pipes and sheet name did the trick!

Unfortunately, Alteryx is now telling me that a lot of the Excel files have a different schema than the 1st file. They're all system generated and identical so I'm surprised/puzzled. And very disappointed because your solution, for those that are identical, is perfect.

Any idea how I can work around this?

jrlindem

Hmm that is puzzling...

I apologize for all the back-and-forth and appreciate you hanging in there as we experiment together. Something that comes to mind is considering ignoring the field headers in case there are any differences in the naming. See if enabling "First Row Contains Data" and "Start Data Import on Line" = 2 helps bypass any potential differences in the schema that may not be obvious to your eye. Obviously you'll have to add the headers back in after the fact, but if it solves the problem, it's a small sacrifice.

Otherwise, we might be leaning back into sledge-hammer mode, using a Batch Macro like @caltang was getting at.

IMERNDO

No apology necessary - you've been a massive help!

I had intentionally eliminated the first row because cell A1 contained the system path for each Excel file, which is unique. But adding that back in seems to have fixed my issue!

One last question - the numbers for each week are not formatted as numbers in the output Excel. Even when I highlight all cells and change the format to Comma Style, they don't change unless I edit each cell, i.e. click into it. Anything I can do in the workflow to fix this?

jrlindem

You're meaning after all is said and done, your workflow ends with an output tool that creates an Excel file and that's where you're having the issue?

Double check the data types upstream in your workflow (making sure it's fully an integer if that's what you need it to be)
Otherwise, if you want to have more control over formatting, your output can be run through the Reporting Tools to render a table

It does sound like you're there though. Glad I could assist in the journey. Once you are content, please do mark the portions of our dialogue as the solution for others benefit. I'm confident you'll figure out the number formatting issue with a little more experimentation.

Cheers, -Jay

Erin

**edit** - I didn't see the second page of comments. Alteryx is funny in how it reads in datasets. I wrote a whole blog about this very situation https://www.linkedin.com/pulse/schema-incident-internal-affairs-investigation-erin-miller-mba-3n90e?lipi=urn%3Ali%3Apage%3Ad_flagship3_series_entity%3BsMm7lzu6QGyHj9QBfo0CSw%3D%3D

@IMERNDO Heads up - the Dynamic Input tool is known to be finicky if it detects the slightest change in schema. I know you said that there shouldn't be schema issues. If this is going to be a recurring process, not just an ad-hoc one off, I would recommend a batch macro with the macro settings Output Mode set to "Auto Configure by Name (Wait Until All Iterations Run)". This is purely a rather-safe-than-sorry approach. And playing with macros can be fun.

PS @caltang - I agree, let's bust out the sledgehammers! 😂

IMERNDO

Thanks @jrlindem - I've marked your initial response as the solution; it's worked a treat.

@caltang & @erin, I may come back to you for that sledgehammer if things get messy down the line!

Quick Links

This months top contributors

SophiaF 432

AkimasaKajitani 390

mceleavey 388

mbarone 337

michael_weaver 335