How to delete duplicate records in DataTables

[Drache]

I have two DataTables, T1 and T2 both contain a Date field.

T1 contains around 5000 records and T2 contains around 400 records.

What I want to do is check if any of the records in T2 are in T1 (checking the Date field) and if they are delete them from T1.

I have tried using a for loop, but it has to loop over two million times.

Is there any better way of doing this?

Thanks for any help.

 

[Arg81]

SQL database?

If so you can use SQL to do this. I think it's a Right outer join on the tables ON the date field....

Emphasise on "I Think"

 

[cjard]

Is there any better way of doing this?

Yep.

Something like this:

Dim t1rows() as T1DataRow 'array
 
For Each t2ro as T2DataRow in [B]MyDataSet.T2[/B]
  t1rows = [B]MyDataSet.T1[/B].Select(string.Format("[B]T1KEY_FIELD[/B] = #{0}#", t2ro.[B]T2KEY_FIELD[/B].ToString("MM/dd/yyyy hh:mm:ss"))
  For i as Integer = 0 to t1rows.Length - 1
    t1rows(i).Delete()
  Next i
Next ro
 
MyDataSet.AcceptChanges()

You have to engage brain to edit the Bold bits.. your post doesnt contain enough info to writethem


Theory:

For each row in t2
..use the built in SELECT method to return a set of data rows from t1 whose date field is equal to the t2 date field, note the american format is hard enforced. no other format is acceptable
For each row returned, mark it deleted



Other ways to do it:
Load every date out of t2, into a dictionary, as the KEY, with a NULL for the VALUE
For Each row in t1, if theDictionary.ContainsKey(t1date) then delete the row

 

[cjard]

BUt, arg's suggestion has merit; if the data gets into these tables from the database, youre way better off getting the database to do this:

SELECT * FROM t1 LEFT OUTER JOIN t2 ON t1.date = t2.date WHERE t2.DATE IS NULL

WIll only return rows from T1 that have no matching date in t2

 

[Drache]

Further information

I should have put this in earlier, but anyway both data tables are internal to the program with no external database connections.

The data is read in from csv files and after processing is sent out into a Excel spreadsheet.

Thanks for taking the time to reply.

 

[cjard]

Use the advice in the first post then. For an easy way to make an excel file without needing excel installed, search this forum for ExcelableDataset