Select Duplicates in a DataTable

Help Studio
1

Hi All.

I am struggling to select records in a DataTable where there are duplicates.
Here is a sample data in my DataTable.
image

Here is the code I am trying to run to retrieve duplcate records, but somehow its not working.

dt_duplicateRecords = (From d In dtOutput
Group d By k1=d(2).toString.Trim, k2=d(3).toString.Trim,k3=d(5).toString.Trim Into grp=Group
Where grp.Count >1
Select grp.toList).SelectMany(Function (x) x).CopyToDatatable

Can someone please assist? I want to be able to select all records including the duplicates, so in the above example first 2 rows

2

Hi @sacad

You can try with below expression

(From p in DT.Select() where( From q in DT.Select() where q("ID").Equals(p("ID)) Select q).ToArray.Count>1 Select p).ToArray.CopyToDataTable()

Regards
Gokul

3

Hi @sacad

Use below function!
Assign

DuplicateRecordDt =ReadDt.Select(“[FamilyName] =‘TestA’”).CopyToDatatable

Regards

1 Like
4

Hi @pravin_calvin ,

Try this out,

 dt_duplicateRecords  = (From d In dtOutput.AsEnumerable()
Let cnt = dtOutput.AsEnumerable.Where(Function (r) d.ItemArray.SequenceEqual(r.ItemArray)).Count
Where cnt > 1
Select d).copydatatable

Regards,

5

your initial statement could work, but maybe fails on on name variations. Compared to the real data, what is the issue, what are the fails?

6

Thanks for your response.

Ive run this against a large datatable containing about 200 records where about 15 records containing duplicates. For example one client had about 5 records.
This query only returned 2 records

Does this query only returns exact duplicates for all columns?

I want to check duplicates for 3 columns only and ignore the rest

7

Hi,

Can you share specific sample data as a file? It’s no problem if dummy data.

Regards,

8

image
image708×461 8.21 KB

Here is a subset of a large dataset. This should be enough as there is lots of duplicates

9

Hi,

Thank you for sharing. but can you share this as an excel file? It’s OK if there are several rows like your first post.

Regards,

10

Sorry i dont have it in excel

11

(From d In dtOutput.AsEnumerable
Group d By k1=d(2).toString.Trim, k2=d(3).toString.Trim,k3=d(5).toString.Trim Into grp=Group
Where grp.Count >1
Select grp.toList).SelectMany(Function (x) x).CopyToDatatable

Give a try on this statement. AsEnumerable was added on the first line

12

Hi @sacad ,

try this,

(From p in dtOutput.Select() where( From q in dtOutput.Select() where q("column1").Equals(p("column1)) and q("column2").Equals(p("column2)) and q("column3").Equals(p("column3)) Select q).ToArray.Count>1 Select p).ToArray.CopyToDataTable()

13

Thanks, this is overall working

However i want to search using a specific column, how can i do that please?

I want to be able to search using ID = 123456 but this isnt working somehow

For example:

(From p In in_DataTable.Select() Where( From q In in_DataTable.Select()
Where q(“ID”).Equals(“123456”) And q(“FN”).Equals(p(“FN”)) And q(“PT”).Equals(p(“PT”)) Select q).ToArray.Count>1
Select p).ToArray.CopyToDataTable()

14

HI @sacad ,

With regards to your question, so, when use a LINQ query, the part where, q("column1").Equals(p("column1"), we are collectively comparing the whole columns. Here since your trying to compare column with a single ID, it will throw you error.

As per my understanding on your question, you want to search the ID = 123456 , In the datatable, Am i right ? if not please explain more on this.

If Yes, you can try a simple select condition,

step1 : if (in_DataTable.select("[ID]='123456'").Length >1) then
step2 : in_DataTable = in_DataTable.select("[ID]='123456'").copytodatatable()
step3 : Nothing
2 Likes
15

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