Method to remove empty rows from DataTable?

Question

I parsed through an Excel spreadsheet and returned the entire result as a DataTable. However, this Excel spreadsheet has several empty rows that I would like to eliminate from the resulting DataTable. In particular, each empty row begins with an empty cell. So, I know that the entire row is empty if the value of the cell at the first index is empty. Note that I cannot simply modify the Excel spreadsheet because I have to work with exactly what the client has sent to me. Based on this information, I assumed that I could perform the following function to remove empty rows:

' DataTable dt has already been populated with the data

For Each row As DataRow In dt.Rows
    If dt.Rows.Item(0).ToString = "" Then
        dt.Rows.Remove(row)
    ElseIf dt.Rows.Item(0) Is Nothing Then
        dt.Rows.Remove(row)
    End If
Next

However, after crafting this solution, I am greeted with the following error:

Collection was modified; enumeration operation might not execute.

I now realize that I cannot alter the collection as I access it. How can I get around this? I'm wondering if I should create a new DataTable with the rows that aren't empty. Is this the best approach or are there better options?

EDIT: I have also tried iterating over the rows backwards:

For i = dt.Rows.Count() - 1 To 0
    If dt.Rows.Item(i).Item(0).ToString = "" Then
        dt.Rows.RemoveAt(i)
    End If
Next

Your For loop is missing a Step -1 to iterate backwards. — Richard Deeming
– Richard Deeming, Commented Dec 20, 2012 at 19:04

Richard Deeming · Accepted Answer · 2012-12-20 19:02:31Z

6

You can't modify the collection while you're enumerating it with For Each, but a simple For loop will work. You'll need to loop backwards to avoid skipping the row after a removed row.

You've also got your tests the wrong way round; if Item(0) returns Nothing, then Item(0).ToString will throw a NullReferenceException.

I'm assuming the dt.Rows.Item(0) is a typo, and should read row.Item(0) instead.

For i As Integer = dt.Rows.Count - 1 To 0 Step -1
    Dim row As DataRow = dt.Rows(i)
    If row.Item(0) Is Nothing Then
       dt.Rows.Remove(row)
    ElseIf row.Item(0).ToString = "" Then
       dt.Rows.Remove(row)
    End If
Next

answered Dec 20, 2012 at 19:02

Richard Deeming

31.5k11 gold badges86 silver badges167 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Murugan · Accepted Answer · 2019-07-29 06:53:40Z

0

Vb.Net using linq

 Dtset.Tables(0).AsEnumerable().Where(Function(row) row.ItemArray.All(Function(field) field Is Nothing Or field Is DBNull.Value Or field.Equals(""))).ToList().ForEach(Sub(row) row.Delete())
        Dtset.Tables(0).AcceptChanges()

answered Jul 29, 2019 at 6:53

Murugan

311 bronze badge

Collectives™ on Stack Overflow

Method to remove empty rows from DataTable?

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related