Combine multiple identical excel files

[IMERNDO]

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!

[jrlindem · Accepted solution]

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

[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: