Remove all columns with no data from DataTable

Question

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?

SLaks · Accepted Answer · 2009-11-19T22:43:54.760

47

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.

edited Nov 19 '09 at 22:43

answered Nov 19 '09 at 22:11

SLaks

868,454
176
1,908
1,964

Nice. Adjust code to check all the column names and you get the green checkmark. – Larsenal Nov 19 '09 at 22:23
Maybe ;-). I've actually implemented it myself already. Just don't want to mark it correct yet since it doesn't completely answer the initial question. – Larsenal Nov 19 '09 at 22:31
My DataTable Have many rows.One of the row contain One specific strint Like=”Container”.I want to delete all of the above rows of the container string ,Can you plz show some syntax . – shamim Mar 09 '11 at 04:40

score 2 · Answer 2 · edited Dec 23 '14 at 07:03

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();           
    }

score 1 · Answer 3 · edited Jun 13 '16 at 11:47

1

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();
}

edited Jun 13 '16 at 11:47

gevorg

4,835
4
35
52

answered May 23 '14 at 07:52

RameezAli

956
11
12

Surely this deletes the entire row if column 1 in that row is DBNULL. Not quite what the OP was after. – Steve Feb 10 '17 at 16:59

score 0 · Answer 4 · answered Dec 15 '17 at 05:27

0

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

datatable.Columns.Clear();

answered Dec 15 '17 at 05:27

Manish sharma

526
4
14

This clears all the column which is not what the user is asking for. – John Evans Solachuk Oct 03 '19 at 02:40

score 0 · Answer 5 · edited Oct 28 '12 at 07:53

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

Remove all columns with no data from DataTable

5 Answers5

Linked