In DataTable, how to fill empty column data with the same name as the above one?

I know the title is hard to understand, so let me elaborate on this.

I have an excel sheet with data like the following.
bad

As you can see, there are lots of space under Name column.
But you can easily tell A3 through A5 are also of Michael’s.
Likewise, A7 is of Tony’s, A9 through A11 are of Kevin’s, and so on.

I used Read Range and retrieved a DataTable object from it, but it has, of course, spaces where it has spaces in the Excel sheet. Now I want to somehow modify the DataTable object so the result of Write Range from the DataTable object will look like below:
good

How could I achieve this in an elegant way?

1 Like

Hello.

I don’t know about elegant, but you will want to run the rows through a ForEach loop. Then, you can use an If activity to check for empty values, and if it is not empty then store to another variable to be used to assign to the empty cell.

Here is some pseudocode:

Declare variable previousName

For each row in dt1
    If activity: condition: row("Name").ToString.Trim = ""
      Then Assign activity: row("Name") = previousName
      Else Assign activity: previousName = row("Name").ToString

Hope that helps.

Regards.

6 Likes

Hi @tomato25

As Clayton mentioned used string is null or empty method then assign row ("name)= loop the name

Thanks
Ashwin.S

2 Likes

Hi @tomato25
You can follow these steps:

Read Range sheet1 —> dt

For each row: In dt (DataTable variable)
If : row(“Name”)=nothing
Then: Assign: row(“Name”) = dt.Rows(dt.Rows.IndexOf(row)-1)(“Name”).ToString

Write Range sheet2 —> dt

I’m attaching my sample workflow here for your understanding. MissingData.zip (19.3 KB)
a B

4 Likes

Thank you everyone! It is working great!! Love you all

2 Likes

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