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!

[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?...

@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!