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.
-I have two data table DT1 and DT2, where I have to compare and get the mismatched records from both the data table.
-Have to compare with columnPayment reference in Ref.xlsx(excel sheet) with contains on column Trans Item in TransItem.xls(excel sheet)
-I am attaching a sample excel sheet.
In general, I am looking for 3 output:-
2.Unmatched data(data present DT1 but not in DT2).
Unmatched data(data present DT2 but not in DT1).
Ref.xlsx (8.8 KB)
TransItem.xls (22.5 KB)
It seems you should not use write range activity inside for each
please use datarow to add data into datatable
What you did was partially correct, and you just need to add few more activities to make it work.
Read both excel files in your case Book 1 and Book 2
Create two variable of datatype data table, make sure you initialise these variable
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.
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 Rows UnmatchedRecordszz.xlsx (8.3 KB)
UnmatchedRecords.xlsx (8.3 KB)
Attached the Excel files as well
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 -
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))
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.
You did a great job, Just use Remove duplicate rows before you write range
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.
Use a linq query … It’s fast but little complex to learn, however with practice things become easier.
Use a filter datatable, it will give you result with one activity with matching rows and unmatching rows…
@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
What would be the query for unmatched record?
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