How to write values to empty cells in excel Dynamically

Help
, ,
1

Hi All,

I hope everyone is having a good day, i am am having a problem an i need to see how to solve this, nasically if you take a ,ook at this example sheet: Example Sheet.xlsx (8.6 KB), for every empty cell I need to add the N/A value, now i can do this with the write cell activity, but i need the boot to add the N/A to all cells in the whole range that the Read range activity gets, any ideas on how to do this?

2

@Sidney_Vogel

Try below steps.

  1. Use Read Range activity to read the data from excel file and will give you output as DataTable and say ‘inputDT’.

  2. And then use ForEach Row activity to iterate the data one by one row.

    ForEach row in inputDT
    IF row(“ColumnName”).Tostring.Length = 0
    Then row(“ColumnName”) = “NA”

  3. Finally use Write Range activity to write into Excel file.

3

Do i need to do this:
ForEach row in inputDT
IF row(“ColumnName”).Tostring.Length = 0
Then row(“ColumnName”) = “NA”

For each Row? is there not a more dynamic way where it checks the whole range an sets the empty cells to N/A?

4

Here you go with a xaml

NA.zip (10.4 KB)

Cheers @Sidney_Vogel

1 Like
5

Hi @Palaniyappan, thank you for the example, but i am missing a activity

Missing%20Activity
Missing%20Activity1921×1080 125 KB

6

kindly remove the project.json file and try opening the xaml alone
Cheers @Sidney_Vogel

1 Like
7

It seems like the activity is incomplete, while loop is empty

Incomplete
Incomplete1920×1075 114 KB
@Palaniyappan

1 Like
8

you need to open it to see what is inside… btw if you have empty excel cells, then row(0).ToString will give you errors…

9

Double Click on that while loop
Cheers @Sidney_Vogel

1 Like
10

Hi @Palaniyappan, you solution worked, thank you so much for the example, i have now learned something more, thank you so much for the help, BTW, is the language Visual Basic? do you know any good resources for learning VB?

11

@bcorrea, thanks for the heads up, i guess it would make since since there is no data in the datatable

1 Like
12

Hi A am getting an error when using it in my project, take a look please.

Error
Error1920×1041 216 KB
. Do you know why?

13

Hi @Palaniyappan never mind, i solved the problem, the counter in my other project was set as generic value instead of Int32. Thanks all for the awesome help.

1 Like
14

Cheers @Sidney_Vogel

15

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