Quick way to compare two excel files

Hi @vikashkumarvivek00 can you please elaborate it a bit for me , Merge Datatable will merge both the DT’s but I need to compare both Dabatables and update status.

Before comparing ensure the following,

  1. No. of Rows in dt1 and dt2 should be equal.
  2. Column names should be in sync. If not, please update the column names accordingly.

Use this linq to compare,

unmatchedDt = If((From table2 In dt2.AsEnumerable() Where Not dt1.AsEnumerable().Any(Function(f) f.Field(Of String)(“Column1”) = table2.Field(Of String)(“Column1”) AndAlso f.Field(Of String)(“Column2”) = table2.Field(Of String)(“Column2”)) Select table2).Count=0, New datatable, (From table2 In dt2.AsEnumerable() Where Not dt1.AsEnumerable().Any(Function(f) f.Field(Of String)(“Column1”) = table2.Field(Of String)(“Column1”) AndAlso f.Field(Of String)(“Column2”) = table2.Field(Of String)(“Column2”)) Select table2).CopyToDataTable)

If (unmatchedDt.Rows.Count=0), Then {Match} Else {Unmatch}

Please refer this xaml for more details.
datatable compare.xaml (12.2 KB)

1 Like

Hi @kadiravan_kalidoss in my case both the Datatables are of different size and Columns are of different names. The Status should be updated in the same Excel sheet under Status column But when I use Write Cell to Update the Status its taking alot of time as it has to compare 6000 rows from one DT and 644 rows from another . Any idea what I can do in to update the Status column quickly instead of Writ Cell

Can you give me samples of 2 input files and expected output file with dummy records.

I will try to achieve it!

@kadiravan_kalidoss Its Bank related so I cannot share anything . Just assume Two Excel files . One has a status Column along with other columns and the other file has data in thousands. All I need to do is read both the files, values in both and if the values Match the Status Column in file One should be as MATCHED else it should be empty. Using For each and Write cell it took me 20 minutes dont know why .

Can u verify this excel and check the sheet1,sheet2 and sheet3 and confirm your expected result should look like this,

test.xlsx (9.6 KB)

@kadiravan_kalidoss Yes something like that . Only thing is the data will be in Thousands . The Status Column will be the First column always .

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