Quick way to compare two excel files

Help
1

Hi all, I have an excel sheet with 6000+ rows in it and I need to compare it another excel file and update a status as Match or Unmatch. I don’t want to write a VB script and For each Row iteration is taking a lot of time to update status . What can I do to update status so that my flow completes ASAP as thats the business requirement . Any inputs shall be much appreciated , Thanks in advance!

1 Like
2

Use merge activity after reading those Excel
And atlast use defaultView

3

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.

4

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)

2 Likes
5

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

1 Like
6

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

I will try to achieve it!

7

@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 .

8

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)

9

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

10

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

11

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.

12

@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

13

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.

14

@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!

1 Like
15

@kadiravan_kalidoss It worked Thanks alot !!

1 Like
16

Awesome that it worked! Please mark it as solution.

Cheers @Shailesh123

2 Likes
17

@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
18

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

Thanks!

1 Like
19

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 ?

20

can you share your xaml…!