How to Check if table contains an empty cell... and

[AaronMark]

Hi,
I’m trying to set up a verification flow, to make sure a web grid is filled in correctly.
I have performed an example of processing.
I need to search if there are empty cells, in the columns:
F; G; H; I; J; K and then take the value of column A for each row.

In the attached example:

Cell F14 is empty.
So I need to retrieve the value entered in cell A14. “AVL0006725”.

and, again, the cell H18 is Empty… then I need value in A18 (AVL0006747)…

The data collected in Cell A, I need to go to the Web to correct the specific row.

So for each ROW, with an empty value, I have to perform some dispositive actions.
So maybe I need to create a new table, with only the rows to be corrected? I do not know…

The number of lines is variable.
The number of columns is fixed.
The Range only affects columns F to J. (or alternatively A and J).

New Microsoft Excel Worksheet.xlsx (11,1 KB)

Can anyone tell me how to do it?

[RyanHarris]

Hi @AaronMark

For getting those rows where the columns contain empty values, you can use our Filter data table activity.

Add several OR conditions on the filter datacolumns for those columns that you want to check for empty values, and it will return a new data table only the rows with values missing.

You can then loop over that data table and perform the corrections required.

Hope this helps,

Ryan.

[NIVED_NAMBIAR (NIVED N)]

Hi @AaronMark

For this use way

1. using read range activitiy read this excel file and store in a datatable dt1

2. create an integer variable let’s say index and assign it as 2

3. use for each row to.loop through each row in dt1
   Inside the for each row use this if condition with condition as row(5).ToString.Equals("") Or row(6).ToString.Equals("") Or row(7).ToString.Equals("") Or row(8).ToString.Equals("") Or row(9).ToString.Equals("") Or row(10).ToString.Equals("")

If the condition is true

In then section use read cell to read value at cell in A column corresponding to that empty value

with cell as “A”+ index.ToString and store the retrieve value in varaible and do necessary operation with it

After that in then section increment index by one

If the condition is false then in else section do necessary operation and then increment index by one in else section

Hope you got it

Nived N

Happy Automation

[Pratik_Wavhal]

Hi @AaronMark

Below is the Linq which will give you the Index for that particular Row where Cell Value is Empty for the Columns F to J

Use this in Assign Activity in ForEachRow :-

Int32 index = dt.AsEnumerable.ToList.FindIndex(Function (r) r(5).ToString.Equals(String.Empty) Or r(6).ToString.Equals(String.Empty) Or r(7).ToString.Equals(String.Empty) Or r(8).ToString.Equals(String.Empty) Or r(9).ToString.Equals(String.Empty)) 

So then once you get the index pass it into the Read Cell Activity as shown below :-

Mark as solution and like it

Happy Automation

Best Regards
Er Pratik Wavhal

[AaronMark]

Thanks for all the answers.
Now I try to figure out which one is best for my flow.
If I can integrate …
maybe you find a different path … I don’t know if I’m going in the right direction.

The need to verify the contents of a cell is part of a bot currently in use.
This bot, after a series of operations, has to fill out a table on a ServiceNow web page.
(from the CoupaTimesheet column at the end).

These cells are originally empty.
They are compiled at the end of the stream using variables created in the previous steps.
Ok.

Everything works correctly,
but i noticed that, if the number of table rows (which is variable) is very high, every now and then, it fails to fill in the cell.

So I’m looking for a way to go back and repeat the operation, if any line is wrongly filled.
For this I thought about scraping the table and checking that all the cells were full.
Maybe there is a better way?
Tell me if I am going on the right path.

[AaronMark]

I have tried all three solutions.
All three work.
But I will use the first solution, the one with the use of the Filter.
I managed in a simple way not only to create the table of empty elements, but, by inserting the name of the variables in the corresponding cells, also to verify that the value is the correct one.
It would have been the next step, but I solved both problems.
Thanks Ryan.
and thanks to all … really.