Search code examples
c#exceloledb

LINQ on Datatable Find where all rows are empty

I have a code reading data from excel spreadsheet and I have gone this far with some answers on SO

DataTable dt = ds.Tables[0];
dt = dt.AsEnumerable().Where((row, index) => index > 4).CopyToDataTable();
DataTable filteredRows = dt.Rows.Cast<DataRow>().Where(row => row.ItemArray.All(field => !(field is System.DBNull))).CopyToDataTable();

having this 

dt.Rows.Cast<DataRow>().Where(row => row.ItemArray.All(field => (field is System.DBNull)))

returns all rows that are empty.

I have also tried Any, it didn't give the required output

The code above works for where all the fields are not NULL i.e. every columns has a field. This exempt all rows that have 1 column missing but that's not what I want.

I want to exempt all rows that have all columns empty.

Solution

  • Just move the NOT (!) out one level. You want the items where "all rows are null" is not true, rather than where "all of the rows are not null" is true.

    DataTable filteredRows = dt.Rows.Cast<DataRow>()
    .Where(row => !row.ItemArray.All(field => field is System.DBNull))
    .CopyToDataTable();