Remove Empty Records from DataTable

by abhilashca 7. August 2011 16:23


Recently, I have to upload an Excel file contents to a Database. As usual, I wrote the code for retrieving Excel sheet values into a DataTable using the OleDb connection. Oops! the uploading crashed when I realized that the contents read has empty records in it. I tried for some standard techniques for removing empty records. Since, the column names of Excel sheet may varies (in my case), I have to give up the standard way and have to stick with the old lame mechanism of iterating through all the records searching for empty rows. Crying face

And here is the code sample:

   1: bool isEmpty = true;
   2:  
   3: DataTable dt = new DataTable("Table1");
   4: dt.Columns.Add("Id", Type.GetType("System.Int32"));
   5: dt.Columns.Add("Name", Type.GetType("System.String"));
   6:  
   7: dt.Rows.Add(1, "Name1");
   8: dt.Rows.Add(2, "Name2");
   9: dt.Rows.Add(3, "Name3");
  10: dt.Rows.Add(null, null);
  11: dt.Rows.Add(null, null);
  12: dt.Rows.Add(6, "Name4");
  13: dt.Rows.Add(null, null);
  14: dt.Rows.Add(8, "Name5");
  15: dt.Rows.Add(null, null);
  16: dt.Rows.Add(null, null);
  17: dt.Rows.Add(null, null);
  18:  
  19: for (int i = 0; i < dt.Rows.Count; i++)
  20: {
  21:     isEmpty = true;
  22:  
  23:     for (int j = 0; j < dt.Columns.Count; j++)
  24:     {
  25:         if (string.IsNullOrEmpty(dt.Rows[i][j].ToString()) == false)
  26:         {
  27:             isEmpty = false;
  28:             break;
  29:         }
  30:     }
  31:  
  32:     if (isEmpty == true)
  33:     {
  34:         dt.Rows.RemoveAt(i);
  35:         i--;
  36:     }
  37: }

Please note, this code is just a simulation. There might be better code out there Winking smile

Thanks.

 

Tags: , , , ,
Categories: ASP.NET | C# | Excel | DataTable
E-mail | Kick it! | DZone it! | del.icio.us
Permalink | Comments (0) | Post RSSRSS comment feed