Quick way to compare two excel files

I tried looping through columns but still it takes time to Write Update to sheet

Please refer this xaml.

datatable compare.zip (11.1 KB)

  1. Change the column names as per your datatable and add all the columns u want to compare inside invoke code.
  2. If Column names are different, before doing comparison please change to match both datatables.

let me know for more details.

Thanks!
Kadiravan K.

@kadiravan_kalidoss I am unable to give multiple conditions in the VB code. It complies for both the conditions for example if I mention row("Value1).To String = x(“Value1”).To String row(“Status”)=Success in the first line of the code and ow("Value1).To String <> x(“Value1”).To String row(“Status”)=Fail , then it overwrites the Status even if values match

You could try doing a left join with Join DT activity, and the rows that have null values in the new columns added to the dt are the ones unmatched.

I’m writing from my phone, if you want I can elaborate when I get to a computer.

@Shailesh123

  1. Match Linq:

Dt1.AsEnumerable().Where(Function(row) Dt2.AsEnumerable.Any(Function(x) x(“Value1”).ToString=row(“Value1”).ToString And x(“Value2”).ToString=row(“Value2”).ToString And x(“Value3”).ToString=row(“Value3”).ToString)).ToList().ForEach(Sub(row) row(“status”)= “Match”)

  • this will fetch only Match records in dt1 and update the status as “Match”.
  1. UnMatch Linq:

Dt1.AsEnumerable().Where(Function(row) NOT Dt2.AsEnumerable.Any(Function(x) x(“Value1”).ToString=row(“Value1”).ToString And x(“Value2”).ToString=row(“Value2”).ToString And x(“Value3”).ToString=row(“Value3”).ToString)).ToList().ForEach(Sub(row) row(“status”)= “UnMatch”)

  • this will fetch only UnMatch records in dt1 and update the status as “UnMatch”.

Note: So, Match linq will not touch unmatch rows similarly, UnMatch linq will not touch match rows. Hence, Overwrite case will not happen here.!

otherwise, please edit the input excel sheet which i have given above and provide your expected output as well. I will look it into it.

Thanks!

@kadiravan_kalidoss It worked Thanks alot !!

1 Like

Awesome that it worked! Please mark it as solution.

Cheers @Shailesh123

1 Like

@kadiravan_kalidoss - Can I update two rows in the for each you mentioned at a time : for example - .ForEach(Sub(row) row(“status”)= “UnMatch”) And row(“Rate”)= some variable value.

1 Like

Yes. You can do that… use two assign activity inside for each loop…!

Thanks!

1 Like

Hi @kadiravan_kalidoss- I get an error when I do so. I am using .ForEach(Sub(row) row(“Status”)= “Something” row(“Column1”)=“Amend” . Is the syntax correct ?

can you share your xaml…!

cant share xaml as its in AWS. However I am using this command, Dt1.AsEnumerable().Where(Function(row) NOT Dt2.AsEnumerable.Any(Function(x) x(“Value1”).ToString=row(“Value1”).ToString And x(“Value2”).ToString=row(“Value2”).ToString And x(“Value3”).ToString=row(“Value3”).ToString)).ToList().ForEach(Sub(row) row(“status”)= “UnMatch” row(“Action”)=“Amend”)

Hi @Shailesh123

I’m also facing syntax error. For time being, try this below code.

Dt1.AsEnumerable().Where(Function(row) NOT Dt2.AsEnumerable.Any(Function(x) x(“Value1”).ToString=row(“Value1”).ToString And x(“Value2”).ToString=row(“Value2”).ToString And x(“Value3”).ToString=row(“Value3”).ToString)).ToList().ForEach(Sub(row) row(“status”)= “UnMatch”)

Dt1.AsEnumerable().Where(Function(row) NOT Dt2.AsEnumerable.Any(Function(x) x(“Value1”).ToString=row(“Value1”).ToString And x(“Value2”).ToString=row(“Value2”).ToString And x(“Value3”).ToString=row(“Value3”).ToString)).ToList().ForEach(Sub(row) row(“Action”)=“Amend”)

Add one more line like above in your invoke code…!

Thanks!

1 Like

@kadiravan_kalidoss- any idea how we can use nested for each here ? Or pass initiate another row value and pass it within For Each. Something like .ToList().ForEach(row(“Action”) = x(“Action”))

Hi @kadiravan_kalidoss - Hello, can these code be used also if I compare 2 different excel file? I noticed in the scenario above, the comparison came from 2 tabs in 1 excel file…

Hi @redbee,

Yes , we can compare data from 2 different excel file. Read the excel files separately and pass the datatable into invoke code activity.

Thanks!

1 Like

Hi I have two excel files, I need to compare all cells in Excel1 that is not null to Excel2. I have to check if that input matches.

use Excel application scope for this write range. it will work only inside the scope.

or use this activity directly.

this will work without scope.

Thanks!

Hi @kadiravan_kalidoss - i tried and tried for this approach but it doesnt work , so had to go with Vb Script

Hello, thanks for assistance :slight_smile:

I just want to ask what might be the issue if I have this error on my Invoke Code?

Invoke Code: Exception has been thrown by the target of an invocation.

I used the same code above, just changed the “Value 1” on my own row names.