What is the best way to remove duplicates from a datatable?

Question

I have checked the whole site and googled on the net but was unable to find a simple solution to this problem.

I have a datatable which has about 20 columns and 10K rows. I need to remove the duplicate rows in this datatable based on 4 key columns. Doesn't .Net have a function which does this? The function closest to what I am looking for was datatable.DefaultView.ToTable(true, array of columns to display), But this function does a distinct on all the columns.

It would be great if someone could help me with this.

EDIT: I am sorry for not being clear on this. This datatable is being created by reading a CSV file and not from a DB. So using an SQL query is not an option.

Can you use a database to this for you ? Or is this a one off deal ? If you can use the database you can do this with some smart selects/ view. — Mischa Kroon, Dec 04 '08 at 11:11

score 9 · Accepted Answer · answered Dec 04 '08 at 11:19

You can use Linq to Datasets. Check this. Something like this:

// Fill the DataSet.
DataSet ds = new DataSet();
ds.Locale = CultureInfo.InvariantCulture;
FillDataSet(ds);

List<DataRow> rows = new List<DataRow>();

DataTable contact = ds.Tables["Contact"];

// Get 100 rows from the Contact table.
IEnumerable<DataRow> query = (from c in contact.AsEnumerable()
                              select c).Take(100);

DataTable contactsTableWith100Rows = query.CopyToDataTable();

// Add 100 rows to the list.
foreach (DataRow row in contactsTableWith100Rows.Rows)
    rows.Add(row);

// Create duplicate rows by adding the same 100 rows to the list.
foreach (DataRow row in contactsTableWith100Rows.Rows)
    rows.Add(row);

DataTable table =
    System.Data.DataTableExtensions.CopyToDataTable<DataRow>(rows);

// Find the unique contacts in the table.
IEnumerable<DataRow> uniqueContacts =
    table.AsEnumerable().Distinct(DataRowComparer.Default);

Console.WriteLine("Unique contacts:");
foreach (DataRow uniqueContact in uniqueContacts)
{
    Console.WriteLine(uniqueContact.Field<Int32>("ContactID"));
}

score 8 · Answer 2 · edited May 23 '17 at 12:24

How can I remove duplicate rows?. (Adjust the query there to join on your 4 key columns)

EDIT: with your new information I believe the easiest way would be to implement IEqualityComparer<T> and use Distinct on your data rows. Otherwise if you're working with IEnumerable/IList instead of DataTable/DataRow, it is certainly possible with some LINQ-to-objects kung-fu.

EDIT: example IEqualityComparer

public class MyRowComparer : IEqualityComparer<DataRow>
{

    public bool Equals(DataRow x, DataRow y)
    {
        return (x.Field<int>("ID") == y.Field<int>("ID")) &&
            string.Compare(x.Field<string>("Name"), y.Field<string>("Name"), true) == 0 &&
          ... // extend this to include all your 4 keys...
    }

    public int GetHashCode(DataRow obj)
    {
        return obj.Field<int>("ID").GetHashCode() ^ obj.Field<string>("Name").GetHashCode() etc.
    }
}

You can use it like this:

var uniqueRows = myTable.AsEnumerable().Distinct(MyRowComparer);

score 1 · Answer 3 · edited May 07 '21 at 00:49

I think this must be the best way to remove duplicates from Datatable by using Linq and moreLinq Code:

Linq

RemoveDuplicatesRecords(yourDataTable);


private DataTable RemoveDuplicatesRecords(DataTable dt)
{
    var UniqueRows = dt.AsEnumerable().Distinct(DataRowComparer.Default);
    DataTable dt2 = UniqueRows.CopyToDataTable();
    return dt2;
}

MoreLinq

// Distinctby  column name ID 
var valueDistinctByIdColumn = yourTable.AsEnumerable().DistinctBy(row => new { Id = row["Id"] });
DataTable dtDistinctByIdColumn = valueDistinctByIdColumn.CopyToDataTable();

Note: moreLinq need to add library.

In morelinq you can use function called DistinctBy in which you can specify the property on which you want to find Distinct objects.

score 1 · Answer 4 · answered Dec 04 '08 at 11:17

1

If you have access to Linq I think you should be able to use the built in group functionality on the in memory collection and pick out the duplicate rows

Search Google for Linq Group by for examples

answered Dec 04 '08 at 11:17

terjetyl

9,497
4
54
72

wont this be like an over head??? when same thing can be done by a simple single query? No offense but i would like to know its adv over a single query? – Samiksha Dec 04 '08 at 11:21

score 1 · Answer 5 · answered Oct 01 '11 at 11:41

1

It should be taken into account that Table.AcceptChanges() must be called to complete the deletion. Otherwise deleted row is still present in DataTable with RowState set to Deleted. And Table.Rows.Count is not changed after deletion.

answered Oct 01 '11 at 11:41

Alexey

11
1

score 0 · Answer 6 · answered Apr 03 '13 at 22:54

I wasn't keen on using the Linq solution above so I wrote this:

/// <summary>
/// Takes a datatable and a column index, and returns a datatable without duplicates
/// </summary>
/// <param name="dt">The datatable containing duplicate records</param>
/// <param name="ComparisonFieldIndex">The column index containing duplicates</param>
/// <returns>A datatable object without duplicated records</returns>
public DataTable duplicateRemoval(DataTable dt, int ComparisonFieldIndex)
{
    try
    {
        //Build the new datatable that will be returned
        DataTable dtReturn = new DataTable();
        for (int i = 0; i < dt.Columns.Count; i++)
        {
            dtReturn.Columns.Add(dt.Columns[i].ColumnName, System.Type.GetType("System.String"));
        }

        //Loop through each record in the datatable we have been passed
        foreach (DataRow dr in dt.Rows)
        {
            bool Found = false;
            //Loop through each record already present in the datatable being returned
            foreach (DataRow dr2 in dtReturn.Rows)
            {
                bool Identical = true;
                //Compare the column specified to see if it matches an existing record
                if (!(dr2[ComparisonFieldIndex].ToString() == dr[ComparisonFieldIndex].ToString()))
                {
                    Identical = false;
                }
                //If the record found identically matches one we already have, don't add it again
                if (Identical)
                {
                    Found = true;
                    break;
                }
            }
            //If we didn't find a matching record, we'll add this one
            if (!Found)
            {
                DataRow drAdd = dtReturn.NewRow();
                for (int i = 0; i < dtReturn.Columns.Count; i++)
                {
                    drAdd[i] = dr[i];
                }

                dtReturn.Rows.Add(drAdd);
            }
        }
        return dtReturn;
    }
    catch (Exception)
    {
        //Return the original datatable if something failed above
        return dt;
    }
}

Additionally, this works on ALL columns rather than a specific column index:

/// <summary>
/// Takes a datatable and returns a datatable without duplicates
/// </summary>
/// <param name="dt">The datatable containing duplicate records</param>
/// <returns>A datatable object without duplicated records</returns>
public DataTable duplicateRemoval(DataTable dt)
{
    try
    {
        //Build the new datatable that will be returned
        DataTable dtReturn = new DataTable();
        for (int i = 0; i < dt.Columns.Count; i++)
        {
            dtReturn.Columns.Add(dt.Columns[i].ColumnName, System.Type.GetType("System.String"));
        }

        //Loop through each record in the datatable we have been passed
        foreach (DataRow dr in dt.Rows)
        {
            bool Found = false;
            //Loop through each record already present in the datatable being returned
            foreach (DataRow dr2 in dtReturn.Rows)
            {
                bool Identical = true;
                //Compare all columns to see if they match the existing record
                for (int i = 0; i < dt.Columns.Count; i++)
                {
                    if (!(dr2[i].ToString() == dr[i].ToString()))
                    {
                        Identical = false;
                    }
                }
                //If the record found identically matches one we already have, don't add it again
                if (Identical)
                {
                    Found = true;
                    break;
                }
            }
            //If we didn't find a matching record, we'll add this one
            if (!Found)
            {
                DataRow drAdd = dtReturn.NewRow();
                for (int i = 0; i < dtReturn.Columns.Count; i++)
                {
                    drAdd[i] = dr[i];
                }

                dtReturn.Rows.Add(drAdd);
            }
        }
        return dtReturn;
    }
    catch (Exception)
    {
        //Return the original datatable if something failed above
        return dt;
    }
}

score 0 · Answer 7 · answered May 27 '10 at 09:31

This is a very simple code which doesnot require linq nor individual columns to do the filter. If all the values of columns in a row are null it will be deleted.

    public DataSet duplicateRemoval(DataSet dSet) 
{
    bool flag;
    int ccount = dSet.Tables[0].Columns.Count;
    string[] colst = new string[ccount];
    int p = 0;

    DataSet dsTemp = new DataSet();
    DataTable Tables = new DataTable();
    dsTemp.Tables.Add(Tables);

    for (int i = 0; i < ccount; i++)
    {
        dsTemp.Tables[0].Columns.Add(dSet.Tables[0].Columns[i].ColumnName, System.Type.GetType("System.String"));
    }

    foreach (System.Data.DataRow row in dSet.Tables[0].Rows)
    {
        flag = false;
        p = 0;
        foreach (System.Data.DataColumn col in dSet.Tables[0].Columns)
        {
            colst[p++] = row[col].ToString();
            if (!string.IsNullOrEmpty(row[col].ToString()))
            {  //Display only if any of the data is present in column
                flag = true;
            }
        }
        if (flag == true)
        {
            DataRow myRow = dsTemp.Tables[0].NewRow();
            //Response.Write("<tr style=\"background:#d2d2d2;\">");
            for (int kk = 0; kk < ccount; kk++)
            {
                myRow[kk] = colst[kk];         

                // Response.Write("<td class=\"table-line\" bgcolor=\"#D2D2D2\">" + colst[kk] + "</td>");
            }
            dsTemp.Tables[0].Rows.Add(myRow);
        }
    } return dsTemp;
}

This can even be used to remove null data from excel sheet.

score 0 · Answer 8 · edited Oct 17 '09 at 20:06

0

Use a query instead of functions:

DELETE FROM table1 AS tb1 INNER JOIN 
(SELECT id, COUNT(id) AS cntr FROM table1 GROUP BY id) AS tb2
ON tb1.id = tb2.id WHERE tb2.cntr > 1

edited Oct 17 '09 at 20:06

abatishchev

98,240
88
296
433

answered Dec 04 '08 at 11:11

Samiksha

6,122
6
29
28

score 0 · Answer 9 · answered Dec 04 '08 at 11:22

Liggett78's answer is much better - esp. as mine had an error! Correction as follows...

DELETE TableWithDuplicates
    FROM TableWithDuplicates
        LEFT OUTER JOIN (
            SELECT PK_ID = Min(PK_ID), --Decide your method for deciding which rows to keep
                KeyColumn1,
                KeyColumn2,
                KeyColumn3,
                KeyColumn4
                FROM TableWithDuplicates
                GROUP BY KeyColumn1,
                    KeyColumn2,
                    KeyColumn3,
                    KeyColumn4
            ) AS RowsToKeep
            ON TableWithDuplicates.PK_ID = RowsToKeep.PK_ID
    WHERE RowsToKeep.PK_ID IS NULL

score 0 · Answer 10 · answered Dec 04 '08 at 11:23

Found this on bytes.com:

You can use the JET 4.0 OLE DB provider with the classes in the System.Data.OleDb namespace to access the comma delimited text file (using a DataSet/DataTable).

Or you could use Microsoft Text Driver for ODBC with the classes in the System.Data.Odbc namespace to access the file using ODBC drivers.

That would allow you to access your data via sql queries, as others proposed.

score 0 · Answer 11 · answered Dec 04 '08 at 11:26

0

"This datatable is being created by reading a CSV file and not from a DB."

So put a unique constraint on the four columns in the database, and inserts that are duplicates under your design won't go in. Unless it decides to fail instead of continuing when this happens, but this surely is configurable in your CSV import script.

answered Dec 04 '08 at 11:26

JeeBee

17,476
5
50
60

I am not inserting the data into a database. This data is being written to a csv file. – Khaja Minhajuddin Dec 04 '08 at 12:13

score 0 · Answer 12 · answered Jan 20 '22 at 18:09

For completion, I enclose an example based on some of the answers already here. This solution filters the table by fieldKey1 to N, when the rest of columns could be different. But also filters the first that matches within the duplications, the lowest value for other two columns:

return dt.AsEnumerable()
    .Distinct(DataRowComparer.Default)
    .GroupBy(r => new
    {
        fieldKey1 = r.Field<int>("fieldKey1"), 
        fieldKey2 = r.Field<string>("fieldKey2"), 
        fieldKeyn = r.Field<DateTime>("fieldKeyn")
    })
    .Select(g =>  
        g.OrderBy( dr => dr.Field<int>( "OtherField1" ) )
            .ThenBy( dr => dr.Field<int>( "OtherField2" ) )
                .First())
    .CopyToDataTable();

So datatable dt:

fieldKey1	fieldKey2	fieldKeyn	OtherField1	OtherField2	OtherField3
1	Two	31-12-2020	4	3	xyz7
2	Other	31-12-2021	4	3	xyz100
1	Two	31-12-2020	2	2	xyz3
1	Two	31-12-2020	2	3	xyz4
1	Two	31-12-2020	1	2	xyz1
1	Two	31-12-2020	1	4	xyz2
1	Two	31-12-2020	3	3	xyz5
1	Two	31-12-2020	3	3	xyz6

Would return:

fieldKey1	fieldKey2	fieldKeyn	OtherField1	OtherField2	OtherField3
1	Two	31-12-2020	1	2	xyz1
2	Other	31-12-2021	4	3	xyz100

score 0 · Answer 13 · edited Nov 04 '12 at 07:26

0

Try this

Let us consider dtInput is your data table with duplicate records.

I have a new DataTable dtFinal in which I want to filter the duplicate rows.

So my code will be something like below.

DataTable dtFinal = dtInput.DefaultView.ToTable(true, 
                           new string[ColumnCount] {"Col1Name","Col2Name","Col3Name",...,"ColnName"});

edited Nov 04 '12 at 07:26

John Palmer

25,356
3
48
67

answered Dec 16 '11 at 07:47

Suhas Patil

1

What is the best way to remove duplicates from a datatable?

13 Answers13

MoreLinq

Linked