Comparing Excel Files Dynamically

Question

I have two excel ouputs with multiple tabs (as many as 18) that I need to compare. File A and File B have the same structure, i.e. tab names, column headings, but the tabs within each individual file vary. I am using a batch macro to stack each of the tabs within the excel files. Is there any dynamic way in Alteryx to compare the two excel files?

I am trying to avoid a filter, join, and formula (comparing values) for each sheet within excel.

VinodKammula28 · Answer

Hi @abacon

Thanks for the post, it’s a great use case, and I think your approach makes a lot of sense.

On your question about avoiding the Join tool, I wouldn’t necessarily steer clear of it. Once you’ve pulled in data from Source A and B, using a Join is a reliable way to compare the two, especially if you have a shared field like an ID or name to match on.

You’re also right about Dynamic Input. It can be helpful, but it assumes the structure across files or sheets is the same, which often isn’t the case. That’s where a batch macro comes in. It gives you much more flexibility to bring in data from multiple files or sheets, even when the column layouts differ slightly.

There’s a good community post on how to read Excel sheets dynamically here:

https://community.alteryx.com/t5/Alteryx-Designer-Desktop-Discussions/using-dynamic-input-on-excel-files-with-different-tab-worksheet/td-p/3503

And another one on using batch macros with different schemas:

https://community.alteryx.com/t5/Alteryx-Designer-Desktop-Discussions/Batch-Macros-for-Multiple-excel-files-with-different-schemas/td-p/1142685

Once the data is brought in, you can union the files together and then use the Join tool to make your comparisons.

abacon · Answer

@bsolove123 Here is a link to a post I made about macros that pull out the data from excel files, whether they are separate books or sheets, they just need to be in the same directory.

Is there a reason you are trying to avoid a join tool? What I would do is pull out all the data from A and B and then Join the 2 to get your comparison.

Dynamic Inputs are good but I don't prefer them as they require identical schema's across the sheets/files you are reading in.

Bacon

VinodKammula28 · Answer

Hello @bsolove123 ,

To compare two Excel files (File A and File B)  with multiple tabs in Alteryx, while avoiding manual configurations for each sheet, here’s an efficient dynamic approach:

Step 1: Read Both Files Dynamically

* Dynamic Input Tool:
Use the Dynamic Input Tool to automatically read all tabs from File A and File B. Set the Sheet Name to <All Sheets>, which allows Alteryx to automatically load data from all tabs in both files—no need to specify each sheet manually.

Step 2: Stack the Data

* Union Tool:
After reading in the data, use the Union Tool to combine the datasets from both files. This will ensure the data from both files aligns correctly, facilitating the comparison process.

Step 3: Compare the Data

* Find and Replace Tool:
Use the Find and Replace Tool to compare values between File A and File B, and it will highlight any discrepancies.

Alternatively, you can use the Formula Tool to create a new field that flags mismatches between the two files, which makes it easy to spot differences.

Step 4: Filter the Differences (Optional)

* Filter Tool:
If you wish to isolate the rows with discrepancies, use the Filter Tool to separate the mismatched records from those that match.

Step 5: Summarize the Results

* Summarize Tool:
Finally, use the Summarize Tool to count the differences or generate a summary of the discrepancies between the two files.

This approach will help you compare the Excel files dynamically, without the need for manual configurations per sheet. If you need further clarification or assistance, feel free to reach out.