Issues with Except/Remove dublicate rows in two datatables

Hi everyone,

I hope someone could help me with this issue I am having. I have two datatables in one of them old_DataTable i have 3 results. In the other one called Output I have 4 results. I have been implementing the solution that was suggested in the forum :

1. Read the two Datatables
2. drCommonRows = (From d1 In old_DataTable.AsEnumerable
Join d2 In Output.AsEnumerable
On d1("StartDate").toString.Trim Equals d2("StartDate").toString.Trim
Select d1).ToList
3. drFilteredRows=old_DataTable.AsEnumerable.Except(drCommonRows, DataRowComparer.Default).toList
4. If drFilteredRows.Count >0 than dtFiltered drFilteredRows.CopyToDataTable
Else dtFiltered old_DataTable.Clone

When testing it gives a good result of drCommonRows but not of drFilteredRows and in the IF condition it goes always in Else. 

I even changed the : drFilteredRows=**old_DataTable**.AsEnumerable.Except(drCommonRows, DataRowComparer.Default).toList

 into  

drFilteredRows=**Output**.AsEnumerable.Except(drCommonRows, DataRowComparer.Default).toList but in that case it gives as result all the datatable saved in Output so gives 4 results and is not working.
```Any idea please? 

**The result that I am expecting is 1 row that is the one which is different and is in the Output Datatable.**

Can u try this code instead of above

drFilteredRows=old_DataTable.AsEnumerable.Except(drCommonRows.AsEnumerable(), DataRowComparer.Default).toList

if test data is available feel free and share it with us

Kindly note: the except statement also deduplicates the returned rows. In certain steps it has a not expected side effect

Could you try like this

  1. find common rows by this assign activity:

dt_CommonRows= (From r1 in old_DataTable.AsEnumerable()
From r2 in Output.AsEnumerable()
Where r1(“StartDate”).ToString.Trim.Equals(r2(“StartDate”).ToString.Trim)
Select r1).CopyToDataTable

  1. use the below code to get the rows present only in old_DataTable
    dtFiltered= old_DataTable.AsEnumerable.Except(dt_CommonRows.AsEnumerable(), DataRowComparer.Default).CopyToDataTable

Let me know what result u got

image

Sorry @Xheni_Xhensila
just updated the thread

kindly check it

If I do the last edited version dtFiltered= old_DataTable.AsEnumerable.Except(dt_CommonRows.AsEnumerable(), DataRowComparer.Default).CopyToDataTable the result that I receive is:


and the old_DataTable has 3 results so I dont understand why it says null. If I do instead of old_DataTAble the second Datatable the one that has 4 results called Output Output.AsEnumerable.Except(dt_CommonRows.AsEnumerable(), DataRowComparer.Default).CopyToDataTable

it gives me all the result the 4 results of Output, I need only the one that is different.

handling empty return result
:ambulance: :sos: [FirstAid] Handling of The source contains no DataRows exception - News / Tutorials - UiPath Community Forum

1 Like

find some starter help here:
Find_Common_NonCommon_By1Col.xaml (12.3 KB)

Already tried all what said and still no result. Have tried many ways and options. Recently using this code
drNonDuplicates=dtMaster.AsEnumerable().Union(dtData.AsEnumerable().Except(dtMaster.AsEnumerable(), DataRowComparer.Default)).ToList()
This is My Master result from test 10 till test 19


This is my dtData result from test 10 till test 21

AND this is what I get when I do the code

I am trying to get those who do not repeat. Should take only 2 ( test 20 and test 21)

Please can someone check if I am doing something wrong?

Union was not mentioned by me or provided XAMLs

for direct help we asked for sample data

Yes I know it was not mentioned by you. ill post here the sample

Peter finally it is working, You know what I did: I used your solution FindDupsNonDupsByAllCols.xaml just with this little modification and Now I have the result I wanned.

dtMaster.AsEnumerable().Except(dtData.AsEnumerable(), DataRowComparer.Default).Union(dtData.AsEnumerable().Except(dtMaster.AsEnumerable(), DataRowComparer.Default)).ToList()
Thank you a lot to everyone for the help.

Perfect. For learning purpose also have a look on the intersect Operator
https://www.tutorialsteacher.com/linq/linq-set-operators-intersect

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.