Identifying an Empty Cell across the Datatable

[iamgeet (Geeta Maharana)]

Hello,

I have an excel file with huge data. I need to identify whether there is any empty cell.
In another word, I need to get the count of empty cell anywhere in the datatable.

Thank you in advance for any help you can provide.

Regards,
Geet M

[ganesh4 (Ganesh)]

Do you want check row or column wise?

[iamgeet (Geeta Maharana)]

Any way will work, I just need to find if there any empty cell.

[ganesh4 (Ganesh)]

You can use LINQ query to get the empty count by column wise

[ganesh4 (Ganesh)]

For your reference

[poorna_nayak07 (Poornachandra M)]

Hi @iamgeet ,

Here is the solution.
Below expression will output boolean True if there in no empty cells in Datatable and False when there is atleast 1 empty cell in entire Datatable.

Replace TestDt with Excel datatable and use this expression in if condition

(From x In TestDt.AsEnumerable
Let count=x.ItemArray.Where(Function(y) String.IsNullOrEmpty(y.tostring.trim)).Count
Select count).Sum(Function(r) r)=0

You can find the same in this link

[iamgeet (Geeta Maharana)]

Yes, The below code worked for me.

It returns the total number of empty cell in the datatable.

TestDt.Rows.Cast(Of DataRow)().Where(Function(row) row.ItemArray.Any(Function(field) (TypeOf field Is System.DBNull))).Count()

[iamgeet (Geeta Maharana)]

Yes, the code you shared is also working.
Thank you.

[poorna_nayak07 (Poornachandra M)]

@iamgeet

TestDt.Rows.Cast(Of DataRow)().Where(Function(row) row.ItemArray.Any(Function(field) (TypeOf field Is System.DBNull))).Count()

I think the solution returns count of empty cells present in last row not the entire table. Please recheck the linq