Write empty fields in Excel

Hi everyone,
hopefully you are doing well.

I would like to know how I can write into empty Excel cells through automation. My file I download always has empty cells in a column (for example ‘G’) and I want to write something into those empty cells with a robot. Is that possible somehow?

Thank you in advance and best regards :blush:

Hi @crazyronny

You can use an activity called write cell.
You need to specify the file name, the cell name (example G12) and the sheet name.

In case you want to write into the entire column, you can use a for each loop.
You can try and do it like this:
Read the excel data into a DT
Indexing for DT starts with 0 and Excel columns start with 1. Lets say A is column 1 and in DT its index is 0. Hence for G it will be 7 in Excel and 6 in DT.
Logic:
For each row in DT
row.Item(ColumnNumberforGinDT)=“Enter your value here”

After the for loop, write the DT back to the excel.

Let me know if it worked.

1 Like

@songoel Thanks for answering!
Yes I know the activity. It seems like a solution for writing into the column but I need to write only into the empty cells. How can that be done?

So, in the for each loop, use a if condition and check if the cell is empty.

Use a if activity with condition:
(row.item(“columnname”))="" or String.IsNull(row.item(columnname))

Also, if you want assign a single value to all the empty cells, you can try using a LINQ query.

Use a LINQ Query to find all empty rows in the DT.
Define an array of DataRows say arr
Use
arr = DT.Select("[ColumnName] is Null")

Once you have arr, use a for each
For each item in arr
item.Item(ColumnName) = Value

1 Like

Check this below link query, @crazyronny
Before using below query in assign create a datatable called “dtCorrected”,
dtCorrected = DataTable.Clone , like this and then use below linq
=>(From r In DataTable.AsEnumerable
Select ia = r.ItemArray.ToList
Select ic = ia.ConvertAll(Function (e) e.ToString.Trim.Replace("",“NA”)).ToArray()
Select dtCorrected.Rows.Add(ic)).CopyToDataTable()
Then write range the dtCorrected datatable.
Hope this may help you :slight_smile:

1 Like

@songoel That looks pretty complicated :smiley:
I tried it but I get an error in the if condition (see screenshot)
It says: “Compiler error(s) encountered processing expression “(row.item(“Kreditor”))=”” or String.IsNullOrEmpty(row.item(“Kreditor”))".
“Option Sctrict On” doesn’t allow operators of the type “Object” für the “=” operator. Use the Is-Operator if you want to test the identity ob the object.
“Option Strict on” doesn’t allow converting from Object into String."

Now I don’t know where I made a mistake.
By the way in the range I wrote the index “7” because it is index of the column ‘G’ I have to put the value in, since UiPath starts counting from 0 right?
And it is column ‘H’ sorry :smiley:

Can you maybe quickly create the workflow because you seem to be very expertised in UiPath :smiley:

use row.Item(“Keditor”).ToString

1 Like

@songoel I tried to put the “ToString” everywhere:

String.IsNotNull(row.Item(Keditor).ToString)

@songoel Sorry, but I don’t make it :sweat_smile:
Can you try it out at your UiPath Studio? Maybe you get an other error

@crazyronny
See the screenshot attached below:
No errors.

1 Like

@songoel Oh okay now it doesn’t give me an error.
I ran it and the robot didn’t stop but nothing really happened. I put the Write Cell activity into then but it doesn’t go into the then field because there seems to be an error or something. I put an easy write cell activity into else with writing a word in cell H3 and it worked. When I put the actual write cell activity into both fields it says that the range does not exist. I don’t see anything wrong actually because the name of the column is “Kreditor”.
Here is a screenshot:

The write cell used here has wrong values.

In Range you need to specify values likes “G”+rowcounter.ToString
In Input you need to specify the cell value. Lets say in all empty cells you want to write “hello”, then specify “hello” instead of “7”.