While trying to compare two excels i am getting Wrong data

Sequence.xaml (27.3 KB)

1 Comparing to Excel and trying to write matching fields in another excel and unmatched field in another excel, But the data is coming up twice in another excel.
2. This Means I am getting each matched field twice in my new excel and the same with Unmatched.
I have attached a dummy XAML.
If anyone can correct me

the logic that you are using to get the match and unmatched rows seems to be incorrect.

Check this Post. It solves the same problem.

Hi @Dimple_Mandal

It seems you should not use write range activity inside for each
please use datarow to add data into datatable

thanks .

Hi @Dimple_Mandal Dimple

What you did was partially correct, and you just need to add few more activities to make it work.

  1. Read both excel files in your case Book 1 and Book 2
  2. Create two variable of datatype data table, make sure you initialise these variable
  3. Use either for each row or linq, whatever you are proficient in to filter data. Based on your filter either add data row to matched variable dt or unmatched variable dt.
  4. Last write these back to excel file post for each loop.

Changed the code ,still getting duplicate values

Sequence.xaml (26.0 KB)
Is this correct, Still getting duplicate rows

You are close,
Just build datatable outside before for each loop. Rest is perfectly fine

Sequence.xaml (27.8 KB)
Still getting duplicate RowsUnmatchedRecordszz.xlsx (8.3 KB)
UnmatchedRecords.xlsx (8.3 KB)
Attached the Excel files as well

1 Like

Hi @Dimple_Mandal ,

did you try with the LINQ query? if not then you can give it a try, It will help you if the data is high in numbers and also it’s fast.

  • After your read range of both the excel
  • Use assing activity and declare a variable and data type will be DataTable, e.g - MatchedBook1
  • Use the below code in the value field of assign activity -
(From A In Book1.AsEnumerable()
Where Book2.AsEnumerable.Any(Function (b) a("PhoneNumber").ToString.Trim.Equals(b("PhoneNumber").ToString.Trim))
Select A).CopyToDataTable()
  • After that use the Write range and used the above declared variable as input for writing the output

Please refer to the attached screenshot –

Hope this solves your problem.

2 Likes

You did a great job, Just use Remove duplicate rows before you write range

1 Like

ThankYou, I will try this as well.Do you have a dummy XAML?

You won’t need that, just follow the steps and for ref. see the screenshot and just replicate it.

Use the Assign activity just after both the read range activity.

Idea is to help you build solution with what you have done already. However there are different ways more complex but easier to implement.

  1. Use a linq query … It’s fast but little complex to learn, however with practice things become easier.
  2. Use a filter datatable, it will give you result with one activity with matching rows and unmatching rows…
1 Like

@Lakshay_Verma agreed with you.

but Filter DataTable is not helpful(my personal experience) with working with large data sets.

but again I have learnt the same way and people in the community had helped me a lot to learn and grow and to write and develop better and better codes and bots, so shared the LINQ so that the initial bottleneck will get resolved for her.

Cheers (y), nice work keep it up

1 Like

What would be the query for unmatched record?

Hey @Dimple_Mandal

For unmatched records from Book1 DT will be like below -

Book1.AsEnumerable.Except(MatchedBook1.AsEnumerable, DataRowComparer.Default ).CopyToDataTable()

Create a DataTable variable and use the above LINQ query or you can change it according to your requirement.

it worked for me, but why I am not getting the uncommon fields in else part, it is getting the same data as a matched record

Thankyou i will try it,was trying to learn LINQ queries as well

Great, keep learning its light and fast to work with (y)

Getting error the source contains no data rows