How to check if a datatable (excel) contains empty cells

Help
1

I want to find an approach to check if there is any empty cells in a certain datatable.
I know I can use “for each row” activity + “if” to check if a certain column contains empty cells.
But in my senerio, there are too many columns in excel, and the total amount of column and column names are dynamic, which can make the approach mentioned above extremely complex and unstable.
I want to find a way to check the datatable as whole wether it contains empty cells.
Anyone who leaves comment is highly appreciated.

1 Like
2

Hi @eb978xz
Dt.columns.count.ToString.contains(“”)

Thanks
Ashwin.S

3

Hi @eb978xz ,

Try like this,

Table.Rows.OfType(Of DataRow)().Any(Function(r) r.ItemArray.Any(Function(o) o = DBNull.Value))

4

Hi AshwinS2,

Thank you for your reply, but if I understand correctly, your solution counts the amount of column and to check if the count is nothing?
I does not think this will solve my problem but thats for your help.

5

This will work for sure, I have tested it.

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

6

Hi sarathi125,

You absolutely rock! I tested it but it returned 0 as blank field count for some reason…
They only variable I passed in is “dtNew”, did I do something wrong?

7

dtNew is the datatable which I have used, I read a excel as datatable with some blank cell values and tested it.
May I know how you are testing and where you are getting data for your datatable.

8
  1. I used workbook read range to output datatable called “testDT” from an testing excel form with 2 randomly distributed blank cells within.
  2. I assigned a int32 varible called “count” = testDT.Rows.Cast(Of DataRow)().Where(Function(row) row.ItemArray.Any(Function(field) (TypeOf field Is System.DBNull))).Count()
    3.I write line “count.ToString” and it returned “0”
9

Then I think those cells are not blank, it may have some blankspace/whitespace. Please check in the excel file itself before run the workflow.

10

I am 100 % sure no space or anything within cells.
ahh… frastrated.

11

laptop.xlsx (12.8 KB)
DataTable.xaml (7.3 KB)

Check with this samples and let me know if you are facing any issues. Compare the workflow with yours for any datat ype mismatch

4 Likes
12

Your code works smooth. The problem is caused by me using workbook read range and you used excel application scope. Now it is solved.
Many thanks!

1 Like
13

Thank you so much you saved my day!