How check if the entire Datable is empty or only whitespace

Help Activities
1

So I have this datatable that I filter in which, I will check if it’s empty or not.

I tried making an If with Condition If dt is Nothing OrEsle dt.rows.count = 0
But it will always have one row of empty white space of ''''', so it isn’t empty nor the rows is equal to zero.
I also tried removing the empty rows before the If condition, but it gives an error of Removing Empty Rows: The source contains no DataRows.

I know I can do like if one column is empty. But there are some cases In which some of columns may or may not contain anything. The Process should continue if atleast one cell in any of the columns contains a value.

2

Hi @Shinjid

You can filter the DataTable as given below

ExcelData.Rows.Cast(Of DataRow)().Where(Function(row) Not row.ItemArray.All(Function(field) field Is DBNull.Value Or field.Equals(“”))).CopyToDataTable()

This eliminate DBNull and empty space in the entire table collection.

Hope this will be helpful. Thank you.

3

Thanks for replying, I actually use this for removing empty rows as what I mentioned above. Unfortunately, it gives me the Removing Empty Rows: The source contains no DataRows. error

4

Hi @Shinjid

Please try this,

Dt.asenumerable.any(function(row) row.itemarray.any(function(item) not string.isnullorempty(item.tostring.trim)))

It will return true if any cell contains values, else returns false if all the cell is empty.

Thanks

5

Code doesn’t seem to work. Sorry that I can’t understand what’s was wrong

Error	ERROR	Validation Error	Compiler error(s) encountered processing expression "dt.AsEnumerable.Any(function(row)row.ItemArray.Any(function (col)not string.IsNullOrWhiteSpace(row(col).ToString.Trim)))".
Overload resolution failed because no accessible 'Item' can be called with these arguments:
    'Public Default Property Item(column As System.Data.DataColumn) As Object': Option Strict On disallows implicit conversions from 'Object' to 'System.Data.DataColumn'.
    'Public Default Property Item(columnName As String) As Object': Option Strict On disallows implicit conversions from 'Object' to 'String'.
    'Public Default Property Item(columnIndex As Integer) As Object': Option Strict On disallows implicit conversions from 'Object' to 'Integer'.	Test/Test - Ship To Retag/Test - ShipToRetag - Read Input File.xaml
6

@Shinjid updated the code please try this,

Dt.asenumerable.any(function(row) row.itemarray.any(function(item) not string.isnullorempty(item.tostring.trim)))

Thanks

7

Thank you very much! It finally worked :smiley:

1 Like
8

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.