Append value on the first empty field in a specific row in Excel

Hi, I have a row “ID” in a table. Now I read a value from a program and I need to insert that value in the first empty field in “ID”. How could I do that?

Example.
Name: Height: ID:
Bob| 170cm| 555
Max| 185cm|
Sara| 165cm|

-> Then It should insert the next value in the ID field of Max.
Thank you

Read the Excel into Data Table using Read Range Activity under Excel Application Scope.

Loop thru each row and find the empty ID row. Place a counter inside the for loop to find the empty row index.

Counter = 0
foundRow = 0
For Each row in DataTable
   Counter = Counter + 1
   if row(2).ToString = "" Then
      foundRow = Counter
      Break Loop
   End if    
End For

Use Write Cell Activity, with address "C" + foundRow.ToString

If you have headers included while reading Excel ,use address "C" + (foundRow+1).ToString

If you want all the list of empty rows,
in place of foundRow, use an array to store all the row numbers and then use this array to insert IDs into empty ID column.

Regards,
Karthik Byggari

3 Likes

@KarthikByggari
Thank you, this helps a lot.

Hey @Jizh

you can also use Datatable.select() to find empty rows and then based on Row Index you can use assign and fill your values

Regards…!!
Aksh

1 Like

@Jizh

You can also try like this

Let us take you are having a value stra that you retrieved from program

Now the Index of first Empty value of Column ID

intIndex= (From p In dta.Select
               Where string.IsNullOrEmpty(Convert.ToString(p("ID"))
             Select Convert.ToInt32(dta.Rows.IndexOf(p)))(0)

In Assign Activity
dta.Rows(intIndex)(“ID”)=stra

Regards,
Pradeep

1 Like

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