Compare two excel documents and show differences - Please help

#1

Hi,
I’m having an issue around comparing two excel files. Both have the same column names and however, one file may have different IDs that the other. Within my nested for each, I’m able to state which IDs are the same in both, but the issue i’m having is around the ones that aren’t matched. For example, my write lines are showing all the IDs it has compared rather than just the ones that aren’t there:

Dt1:
ID
123
456
789

Dt2:
ID
123
456

So, in the above scenario i’d like to output one file that says these are all my matches (123, 456)
And another file that says this hasn’t matched ‘789’

Is there a simple way to do this, because i’m just going round in circles…

Thanks

1 Like
#2

Fine
no worries
–hope we have the datatable read from these two files named dt1 and dt2
–now we can use JOIN DATATABLE ACTIVITY where pass both the datatable and get the output with a datatable by using any of the JOIN method

Cheers @qwerty1

#3

Hi @qwerty1
Use Read range of both the excel
Use for each row in dtl anduse for each row in dt2
if row(ID").ToString.equals(row(“ID”).ToString
then condtions as row(“ID1”).=row(“ID”).ToString same for else conditions

Thanks
Ashwin S

#4

Hi,
Thanks for your reply.

I’m able to get the columns that match however, when i’m trying to get the unmatched ones, I’ve placed a simple write line in the else section. This results in all the failed attempts being logged, when really I just want to call out the final result.

In my current scenario, DT1 (the ‘master file’ if you will) has the following data:
ID
123
456
789
321

The datatable that i’m comparing that to (DT2) has the following:
ID
456
789
321

So essentially I’d just like the following output:
Matched:
456=456
789=789
321=321

Unmatched:
123

Instead, I have this:
comparisonOutput

#5

Hi,
Thanks for your reply.
I don’t have a Join Table activity available… is there a package I need to install to access this?

Thanks

#6

Hi @qwerty1

Use String.join("",row.itemarray)

Thanks

AshwinS

#7

Sorry, really not sure what you mean. Could you explain a bit further please?

Thanks

#8

Hi @qwerty1

Use Merge Datatable Activity

Thanks
Ashwin S

#9

Hi,
I’m still no further forward. Do you have an example you can share please?

Thanks

#10

Hi @qwerty1

Please find the screenshot

MergeDt
MergeDt.PNG872×435 12.3 KB

Thanks
Ashwin S

#11

Although i’m grateful for your help, I think we’re on different wave lengths. I’m not looking to merge or join two data sets, i’m trying to show where there are differences. Merging the data sets doesn’t point out clear exceptions, just joins them together then I’ve got to again, remove duplicates (so basically come full circle to face the same problem again).

#12

Hi @qwerty1

Check this query to remove duplicates

From p in dt.Select() where( From q in dt.Select() where string.Join(",",q.ItemArray).Equals(string.Join(",",p.ItemArray)) Select q).ToArray.Count>1 Select p).ToArray.CopyToDataTable()

Use it based on Assign new dt=

Thanks
Ashwin S

#13

Again, thanks, but perhaps i’m not explaining myself clearly:

I have two documents. One is a master file, the other may or may not have the same data.
In either case, I need to compare the two documents & show which numbers match & which ones do not. For both cases I need to follow a set procedure, so I need to have both outcomes; matches & unmatched showing in separate CSVs.

At the moment, I can find all my matches, but the unmatched just shows all the attempts.

I do not need to join the data tables, I need to separate the matched data from the unmatched data.

#14

For anyone that’s facing a similar issue, this post helped my situation:


Thank you, @Ananthu