Compare two different Excel files line by line and create a new file or table with the deviating lines where are the deviating cells are highlighted

Help Studio
, , ,
1

I have in my use case two different Excel files where i want to figure out tihe delta of this two tables. I am able to merge them through the column ID which is unique. I can check out which lines are new in file two compared with file one. It works with two nested for each loops an i can also compare other colums furthermore. But if i have two huge data tables with more than 3k lines the bot needs a lot of time. And i want to compare in every lines 5+ colums and write this lines in a new file or sheet where the deviating cell is highlighted.

My question is, is there any better solution to compare two or more sheets/files? Maybe with C# or VB.net? I haven’t experience with C# and VB.net

2

Hello @Schlosser_Tobias, Welcome to the forums!

Can you please elaborate what you used to do this?

If you haven’t used Join Data Tables activity then you could start by using this activity Table activities to join the sheets you have loaded into DT1 and DT2 respectively.

If you want all rows in the incoming dataset DT2 and only matching rows from DT1 you can use the Right Join .
When you include the fields from DT1, the rows that are not present in DT1 will have their field values set to null . These rows are your new rows in DT2 not present in DT1.

You can pull just these new rows to loop through and perform your business logic.
In your logic it is important that you maintain the Row position of the Cells from DT2.
You can use it to get the Cell color using the Get Cell Color Activity to determine if the cell is highlighted or not to process the record as needed.

I hope this helps.

3

Hi Andy, thank you very much for your fast reply and your help.
I tried it with the activity “Join Data Tables” and it runs much more faster than with my two for each loops over both excel files. I have built a flow where i use the read range activity for uploading the two files (the two files have the same data records regarding the master data from two systems which should be the same) in different data tables and store them in two variables.
In both files i have to manipulate the data. In dt1 there are some colums where i have to map the values to the same values from the other list like:
dt1 dt2
x y
a b
The values from table dt1 have the same meaning but another value like the values from dt2.
Then is use the “Join Data Table” activity to merge this twü data tables via left join to a “merged-dt”. With this step, i figure out which data records are new from dt2 in comparision to dt1.
Currently i use a for each loop to change the values from dt2 and another one to check both colums if the values are match or not.
For the non matching values i append a column on the merged data table where i write a message in the line if there is a nomatch.

1 Like
4

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.