Hi All,
I have a excel sheet where few values of the “Status” column is empty. I need to fill only those column values (Value like reload) without using for each loop activity. Can you please help with the steps if We can able to perform it.
Attached screenshot of excel sheet.
Here, In Row 2, 7 and 11 the “Status” and “Environment” column are empty which needs to be filled without looping through each row. Can someone please help with it.
Thanks,
Nancy Belvania.A
Hi @Nancy29
Use Find/Replace Activity, which helps to replace the empty fields with the value we provided.
Hope it helps!!
It was the extension for the above response… @Nancy29
The Find/Replace Value activity is the modern design excel activities.
→ Use the Excel Process scope activity insert Use excel file inside of it.
→ Give the File path in the Use excel file activity.
→ Insert Find/Replace Value activity inside the Use excel file activity.
→ In Find/Replace Value activity, select the replace all option in operation dropdown, In where to search give the range, In Value to find give Type.Missing and in Replace with field give the value you want to replace with empty cells.
Check the below workflow for better understanding,
Hope you understand!!
Thank you for quick help. Let me try it.
Regards
its better to use invoke code activity to update the rows
For Each r As datarow In dt.AsEnumerable
If r("Status").ToString.Equals("") Or String.IsNullOrEmpty(r("Status").ToString) Then
r("Status")="Updated"
r("Environment")="Updated"
End If
Next
Try this and let me know , if you want to check for 2 columns then use the below code
For Each r As datarow In dt.AsEnumerable
If r.ItemArray.skip(2).All(Function(a) String.IsNullOrEmpty(a.ToString)) Then
r("Status")="Updated"
r("Environment")="Updated"
End If
Next
Sure. Thanks a lot. Let me try this as well.
Regards
Simply you can use the LinQ Expression to do the same… @Nancy29
→ Use the Read range workbook activity to read the excel and store in a datatable called dt_Input.
→ Use assign activity and give the below linQ Expression,
- Assign -> dt_Output = (From row In dt_Input
Let Status = If(String.IsNullOrEmpty(row("Status").toString) AndAlso String.IsNullOrWhiteSpace(row("Status").toString),"Updated",row("Status").toString)
Let Environment = If(String.IsNullOrEmpty(row("Status").toString) AndAlso String.IsNullOrWhiteSpace(row("Status").toString),"Updated",row("Status").toString)
Select dt_Input.Clone().Rows.Add({row("Number"),row("EFFECTIVE_DATE"),Status,Environment})
).Copytodatatable()
→ Use Write range workbook activity to write the dt_Output datatable to same sheet.
Check the below workflow for better understanding,
Hope it helps!!
Hi,
Input:-
Output:-
Xaml File:-
fill empty excel column forum.zip (9.3 KB)
It this works for you, please mark this as a solution.
Thanks
This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.
