CODEWITHSUNDEEP
c#datatable
Larsenal
Larsenal

Reputation: 51146

Remove all columns with no data from DataTable

If all the items for a particular column are empty, I want to remove that column from the DataTable. What's the most elegant way to do this operation on all columns in the DataTable?

Upvotes: 18

Views: 46590

Answers (5)

Manish sharma
Manish sharma

Reputation: 536

You clear all column in datatable.you use like that.

datatable.Columns.Clear();

Upvotes: 0

RameezAli
RameezAli

Reputation: 956

public static void RemoveNullColumnFromDataTable(DataTable dt)
{
    for (int i = dt.Rows.Count - 1; i >= 0; i--)
    {
        if (dt.Rows[i][1] == DBNull.Value)
            dt.Rows[i].Delete();
    }
    dt.AcceptChanges();
}

Upvotes: 1

Imran Ahmed
Imran Ahmed

Reputation: 21

private static void RemoveUnusedColumnsAndRows(DataTable table)
    {            
        for (int h = 0; h < table.Rows.Count; h++)
        {
            if (table.Rows[h].IsNull(0) == true)
            {
                table.Rows[h].Delete();
            }
            enter code here
        }
        table.AcceptChanges();
        foreach (var column in table.Columns.Cast<DataColumn>().ToArray())
        {
            if (table.AsEnumerable().All(dr => dr.IsNull(column)))
                table.Columns.Remove(column);
        }
        table.AcceptChanges();           
    }

Upvotes: 2

Anon
Anon

Reputation: 1

Function RemoveEmptyColumns(Datatable As DataTable) As Boolean
    Dim mynetable As DataTable = Datatable.Copy
    Dim counter As Integer = mynetable.Rows.Count
    Dim col As DataColumn
    For Each col In mynetable.Columns
        Dim dr() As DataRow = mynetable.Select(col.ColumnName + " is   Null ")
        If dr.Length = counter Then
            Datatable.Columns.Remove(col.ColumnName)
            Datatable.AcceptChanges()
        End If
    return true
end function

Upvotes: 0

SLaks
SLaks

Reputation: 887285

You can use the Compute method, like this:

if (table.Compute("COUNT(ColumnName)", "ColumnName <> NULL") == 0)
    table.Columns.Remove("ColumnName");

Alternatively, you can use LINQ:

if (table.AsEnumerable().All(dr => dr.IsNull("ColumnName")))
    table.Columns.Remove("ColumnName");

EDIT: To completely answer the question:

foreach(var column in table.Columns.Cast<DataColumn>().ToArray()) {
    if (table.AsEnumerable().All(dr => dr.IsNull(column)))
        table.Columns.Remove(column);
}

You need to call ToArray because the loop will modify the collection.

Upvotes: 48

Related Questions