Compare Excel files to identify extra columns

[Mert]

Hi everyone!

I am working with 100 Excel files. Supposedly, the template should be the same for all.

However I suspect that there might be some "cheeky" files with extra columns.

Would you know a way to compare the files and highlight those extra columns?

Thank you in advance!

[Qiu]

@Mert 
I have done something similar in work.
I had to compare the 100 Sheets between Excel A and B.

Basically I think we can first read in the all files only for Full path, then read in data for each sheet and drop the data with Field Info tool for each sheet in a Batch Macro,
Then I believe we can locate the "Cheeky" one.
Maybe you have some sample data, we can make something hopefully.

[Mert]

Hi @Qiu - Thank you for your quick reply.

Attached is a zipped folder containing 4 sample files. Two of them have the expected schema. Other two have an extra column each. Would this help?

[gawa · Accepted solution]

hi @Mert 

My idea is to get filed info of all of files by using Batch macro, and identify the extra columns.

Please refer to attached WF. (As it contains Directory tool, and Interface tool to replace path, it should not immediately work at your computer so please modify them)

[Mert]

@gawa - Thank you for sharing the workflow. Just to be sure, the Browse tool at the end displays common fields, is that right? If so, is there a way to reverse it to see "unique" column(s) per file?

[gawa]

hi @Mert  

In my WF, Browser tool being connected from L anchor of Join tool shows "uncommon" fields that is extra columns you want to find.

If you see J anchor of Join tool, you can know "common" fields that all of the files have in common.

[Mert]

@gawa - My apologies that I did not get back to your message earlier, due to busy Q4! However your solution helped me immediately on the day.

I would like to thank you for providing a solution promptly. Cheers!

[gawa]

@Mert Glad to hear that. No problem, everybody knows Q4 is the busiest season!  :)