Validation of excel sheet

[haseebhm]

My clients upload excel sheets to the software which need to be validates. One of the validation steps is to check the sheet for duplicate records. The following code demonstrates the method I use to make this possible.

For i = 0 To xlDtSet.Tables(0).Rows.Count - 1
            xlCommand2 = New System.Data.OleDb.OleDbDataAdapter("select [COL1], [COL2] from [Sheet1$] WHERE [COL1]=" & xlDtSet.Tables(0).Rows(i).Item("COL1"), xlConnection)
            xlCommand2.Fill(xlDtSet2)
            If xlDtSet2.Tables(0).Rows.Count > 1 Then
                'LOG ERROR
                MsgBox("DUPLICATE")
            End If
            xlDtSet2.Clear()
Next

But this makes the validation process very very slow.Please suggest me other(faster) ways to do the same job. Thanks in advance.

 

[Tom]

You can fill the entire excel worksheet into a dataset/datatable with one call. Then you can search/filter the table itself to look for duplicate records.