Filter Data Table and Return blank cells

Help
1

Hi there.

I’m attempting to read a data table in from an excel sheet, and read a particular column to detect any blank criteria. When these blank rows in the DT are detected, i then need to populate a column to the right of the blank rows with text information.

Refering to the example below, i’ve provided a visual example of what i need to happen. I’ve already used a read range activity to bring the entire target excel sheet in as a data table, now i’m attempting to filter it. But it won’t work at the minute.

DataTable
DataTable1260×503 107 KB

Any help would be greatly appreciated!

Kind Regards,
Tom

2

Hi Tommy,

Please correct me if i am wrong.

the steps for the process are

  1. read range and get data table
  2. filter table and get blank cell rows and then do the needfull

or
you can just use formula to set the string message in excel column.

3
  1. Read excel - read range
  2. For each row in Main_Dt (Read range output)
  3. If condition : row(0).ToString.Trim.Contains(“”) or row(0).ToString is Nothing
  4. Assign row(whichever column number you want) = “yourText”

This is should be super easy to understand :slight_smile:

1 Like
4

Yes, that’s correct. The table is read in ok using a read range activity, but I need to filter it to target blank entries in the ‘A’ column. Once I have those blank rows, it needs to enter text into the “AB” Column but only for the ones that are blank, too.

5

I’ll give that a go and see if it will work :slight_smile: Cheers!

6

I’ve got it right up until the end. How do I assign to the particular column I want? I’ve got something like this…If_Statement

So I can check if the row is empty, or contains nothing. it’s just the assign at the end I need to do. How do I pass the particular column name in?

7

Hi @TommyHudd

In assign activity just put row(“Additional details”) or
you can use column number as well like row(0 - First Col, 1- Second Col, 2- Third column …).

In your case it row(27). You can assign it like row(27) = “Value”

Note : please make sure the AddHeaders property is checked in Read Range activity.

Regards,
Vijay.

8

I’ll give it a go :slight_smile:

9

Hi, I tried the assign and got an error.

Error
Error877×318 19.2 KB

Here’s my assign values as well, I put a “Test” value in to make sure it works.

IfAndAssign
Cheers again

Tom

10

Hi @TommyHudd

I think the spelling of Additional(you gave Additonal) is wrong.
can you please check in excel file and give exact value here ??

Regards,
Vijay.

11

Hi, I tried it again - it presented no error, but it also didn’t work :frowning:

Kind Regards
Tom

12

Hi @TommyHudd

It should work. can you please check excel file thoroughly? because i am using same code in my flow and it is working fine.

Regards,
Vijay.

13

At this moment, my robot is still running on debugger mode. I Think the problem is that it hasn’t correctly identified which rows are blank and is instead looping through each row, which the workbook has up to 900 rows.

Kind Regards,
Tom

14

@TommyHudd - Agreed with you.

Use String.IsNullOrEmpty(row(0).ToString.Trim) to check given cell value is empty or not.

please let me know it worked or not.

regards,
Vijay.

15

Should this be the new IF statement criteria?

Cheers,
Tom

16

@TommyHudd - Replace the old condition to check cell is empty or not with String.IsNullOrEmpty(row(0).ToString.Trim)

Capture

Please change the condition which is highlighted in red box.

Regards,
Vijay.

17

I tried it, no luck unfortunately. the Cells remain blank in the column “AB”. Perhaps it I having trouble locating the column / associated rows?

18

Perhaps using a read column activity will be better?

19

@TommyHudd - Are you writing the datatable back to Excel file after adding the value?

In the above solution we are trying to add data to datatable. now to reflect those changes in excel file you have to write the datatable back to excel file.

are you using Write Range activity to write datatable back to excel file ??

Regard,
Vijay.

20

Hi, yes i’m trying to write the new column value back to the Excel file.

I’ve included a write range activity now, i’ll try it and see if it works.

Regards,
Tom