Delete all empty or blank rows from DataTable

19. December 2014 Anil Kumar VB.Net Comments (0)
If you want to delete all empty rows from your DataTable (none of columns of that row holds any value) then either you can do it in old fashion i.e. looping or using lambda expression as -
  1. Dim dttt As DataTable = DtSet.Tables(0).Rows.Cast(Of DataRow)().Where(Function(row) Not row.ItemArray.All(Function(field) field Is DBNull.Value Or field.Equals(""))).CopyToDataTable()  
If you don't want to copy into another DataTable then -
  1. 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())  
  2.   
  3. DtSet.Tables(0).AcceptChanges()  
 
It had to be done using loop as -
  1. For rowCounter As Integer = DtSet.Tables(0).Rows.Count - 1 To 0 Step -1  
  2.     Dim emptyColumnCount As Integer = 0  
  3.     Dim row As DataRow = DtSet.Tables(0).Rows(rowCounter)  
  4.       
  5.     For Each rowItem In row.ItemArray()  
  6.         If rowItem Is Nothing Or rowItem Is DBNull.Value Or rowItem.Equals("") Then  
  7.             emptyColumnCount += 1  
  8.         End If  
  9.     Next  
  10.   
  11.     If emptyColumnCount = DtSet.Tables(0).Columns.Count Then  
  12.         DtSet.Tables(0).Rows.Remove(row)  
  13.     End If  
  14. Next  
 
You can also apply some conditional columns as -
  1. 'This code will not delete as it reads value as ""(empty string)    
  2. If DtSet.Tables(0).Rows(rowCounter)("columnNameOrColumnIndex) Is DBNull.Value Then       
  3.     DtSet.Tables(0).Rows(R).Delete()      
  4. End If     
  5.   
  6.   
  7. 'This will work & delete all rows based on a columns value -  
  8. If DtSet.Tables(0).Rows(rowCounter)(columnNameOrColumnIndex) Is DBNull.Value OR String.IsNullOrEmpty(DtSet.Tables(0).Rows(rowCounter)(columnNameOrColumnIndex)) Then  
  9.      DtSet.Tables(0).Rows(rowCounter).Delete()  
  10. End If  
 
Happy VB.Net coding! 
@AnilAwadh
Tags: ,