CODEWITHSUNDEEP
c#datasetdelete-row
C N
C N

Reputation: 449

Delete empty rows in a Dataset

How do I delete the empty rows in my dataset?

I am reading data from an excel spreadsheet that has a few empty rows at the bottom.

Here is my code so far: 

ConnectionString = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=\"Excel 12.0 Xml;HDR=YES;IMEX=1\";", VariableFile);

OleDbConnection objConn = new OleDbConnection(ConnectionString);

objConn.Open();

OleDbCommand objCmdSelect = new OleDbCommand("SELECT * FROM [Requirements$]", objConn);

OleDbDataAdapter objAdapter1 = new OleDbDataAdapter();

objAdapter1.SelectCommand = objCmdSelect;

DataSet objDataset1 = new DataSet();

objAdapter1.Fill(objDataset1);

objConn.Close();

Upvotes: 1

Views: 2744

Answers (3)

Mike Cole
Mike Cole

Reputation: 14713

Use Linq to include only rows that have a non-null/blank value in any of its columns.

var filteredData = sourceDataTable.AsEnumerable()
         .Where(row => row.ItemArray.Any(col => !String.IsNullOrWhiteSpace(col.ToString())))
         .ToArray();

Upvotes: 0

bdwakefield
bdwakefield

Reputation: 705

Every solution I have found told me to modify the Excel query like you have it. So that isn't much help. You could just create a DataView from your Table that would look at the non-blank rows. Do you know what the columns are beforehand? Even if you don't you could still loop over the column names and build a filter string for the DataView.

string filter = "";

foreach (DataColumn dc in dt.Columns)
{
    filter += dc.ColumnName + " <> '' ";

    if (dt.Columns[dt.Columns.Count-1].ColumnName != dc.ColumnName)
    {
        filter += " AND ";
    }
}

DataView view = new DataView(dt);
view.RowFilter = filter;
dt = view.ToTable();

Upvotes: 3

Brissles
Brissles

Reputation: 3881

Why not just modify your query to pull only the non-empty data.

Upvotes: 3

Related Questions