Remove empty columns from DataTable/DataView

Question

I have a DataTable that returns data from a stored-procedure (it's generated by a dynamic pivot statement, but I don't think that is relevant). The returned data may have columns without data. How can I generate a DataView at runtime that excludes those columns that don't return data?

Edit - sample data

from:

ID  A  B  C
------------
1   1  2  
2   2  4

to:

ID  A  B
---------
1   1  2  
2   2  4

removing column C. If the data looks like this:

ID  A  B  C
------------
1   1     3
2   2     6

then column B should be removed.

Do you want to remove these columns from the table/view or do you want to remove rows that contain no data? Can you show a little sample? — Tim Schmelter, Jun 26 '13 at 12:55
Are all columns of type string? What is the valu that should be treated as "no data", an empty string, null or the default value for that type? — Tim Schmelter, Oct 07 '14 at 06:15

Tim Schmelter · Accepted Answer · 2014-10-07T06:55:25.097

3

You could use this method:

public static void RemoveNullColumns(ref DataTable tbl, params string[] ignoreCols)
{
    var columns =  tbl.Columns.Cast<DataColumn>()
        .Where(c => !ignoreCols.Contains(c.ColumnName, StringComparer.OrdinalIgnoreCase));
    var rows = tbl.AsEnumerable();
    var nullColumns = columns.Where(col => rows.All(r => r.IsNull(col))).ToList();
    foreach (DataColumn colToRemove in nullColumns)
        tbl.Columns.Remove(colToRemove);
}

Your sample:

DataTable table = new DataTable();
table.Columns.Add("ID", typeof(int));
table.Columns.Add("A", typeof(int));
table.Columns.Add("B", typeof(int));
table.Columns.Add("C", typeof(int));
table.Rows.Add(1, 1, 2, null);
table.Rows.Add(2, 2, 4, null);

RemoveNullColumns(ref table, "ID");
DataView result = table.DefaultView;

Result (column "C" removed):

ID  A   B
1   1   2
2   2   4

Here is an overload that does not modify the original table but creates a copy:

public static DataTable RemoveNullColumns(DataTable tbl, params string[] ignoreCols)
{
    DataTable copy = tbl.Copy();
    var columns = copy.Columns.Cast<DataColumn>()
        .Where(c => !ignoreCols.Contains(c.ColumnName, StringComparer.OrdinalIgnoreCase));
    var rows = copy.AsEnumerable();
    var nullColumns = columns.Where(col => rows.All(r => r.IsNull(col))).ToList();
    foreach (DataColumn colToRemove in nullColumns)
        copy.Columns.Remove(colToRemove);
    return copy;
}

edited Oct 07 '14 at 06:55

answered Oct 07 '14 at 06:48

Tim Schmelter

450,073
74
686
939

Does the first method need the datatable passed by `ref`? – Zev Spitz Oct 07 '14 at 07:36
@ZevSpitz: yes, in C# you have to specifiy `ref` if you use a method that expects a `ref` parameter. It's just an indication that this method will modify your table. – Tim Schmelter Oct 07 '14 at 07:42
By that reasoning, the static `Array.Sort` method should also take the first parameter by reference. Isn't it understood that when an object is passed into a method the method might modify properties of the object, even if the method cannot replace the object with another object of the same type? – Zev Spitz Oct 07 '14 at 08:33
@ZevSpitz: you're right that my understanding of `ref` is not set in stone. You could also remove the `ref`. In this case i've used it for two reasons: 1) it makes it pretty clear that the method removes columns(instead of only sorting like `Array.Sort`) and 2) it allows to overload both methods. Without `ref` the compiler wouldn't allow them because they have the same parameters. In general, you're right that it's neither necessary nor common practise to use `ref` on every method that modifies a passed object. – Tim Schmelter Oct 07 '14 at 08:39

Remove empty columns from DataTable/DataView

1 Answers1