Replace a specific value in a field/entire collection with a new value

[vinodchinthakin]

Hi All,

I have a scenario where I have to Replace a specific text in a column/entire collection with a new value.
Now I have implemented using Decision stage(finding specific value using InStr func) then Calculation stage(using Replace fun). Do we have any action or code for this requirement.

Let me give an example
Input Collection
FieldA                       FiledB                         FiledC
BP community        vinod                            ABC
XYZ                          DX BP                           123
NY BP                      AB1                              vinod

Scenario 1: Condition : Replace Text "BP" with "Blueprism"
Required Output Collection
FieldA                                            FiledB                         FiledC
Blueprism community                  vinod                            ABC
XYZ                                                DX Blueprism               123
NY Blueprism                                 AB1                             vinod


Scenario 2: Condition : Replace Text "BP" with "" or Remove Text "BP"
Required Output Collection
FieldA                          FiledB                         FiledC
community                  vinod                            ABC
XYZ                                DX                               123
NY                                 AB1                             vinod


------------------------------
vinod chinthakindi
------------------------------

[devneetmohanty07 · Accepted solution]

Hi Vinod,

Thanks for reaching out to the community. As per your requirement, I have created a separate code on VB .NET only which you can easily use while extending the Utility - Collection Manipulation VBO. Please follow the below steps:

1) Create and action called 'Find And Replace Value In Collection' with the given input parameters for the action: in_DT (Collection), in_Value To Find (Text) and in_Value To Replace (Text) along with output parameters: out_DT Collection) as shown below:



2) Add the code stage named 'Find And Replace Value In Collection' with the below parameters:




3) In the Code tab insert the following code:

For Each Row As DataRow In in_DT.Rows

	For Each Column As DataColumn In in_DT.Columns

		If (Row(Column.ColumnName).Contains(in_ValueToFind)) Then

			Row(Column.ColumnName) = Row(Column.ColumnName).ToString.Replace(in_ValueToFind,in_ValueToReplace)

		End If

	Next
	
Next

out_DT = in_DT​

Test Results:

Inputs:






NOTE: If you want to execute the second condition, just pass the value for in_Value To Replace as "" (Blank)


Hope it helps you out 🙂

------------------------------
----------------------------------
Hope it helps you and if it resolves you query please mark it as the best answer so that others having the same problem can track the answer easily

Regards,
Devneet Mohanty
Intelligent Automation Consultant
Blueprism 6x Certified Professional
Website: https://devneet.github.io/
Email: devneetmohanty07@gmail.com

----------------------------------
------------------------------

------------------------------
----------------------------------
Hope it helps you out and if my solution resolves your query, then please mark it as the 'Best Answer' so that the others members in the community having similar problem statement can track the answer easily in future

Regards,
Devneet Mohanty
Intelligent Process Automation Consultant | Sr. Consultant - Automation Developer,
Wonderbotz India Pvt. Ltd.
Blue Prism Community MVP | Blue Prism 7x Certified Professional
Website: https://devneet.github.io/
Email: devneetmohanty07@gmail.com

----------------------------------
------------------------------

[vinodchinthakin]

Hi @devneetmohanty07.

Your code works Perfect​!
And I have replaced ​"Column.ColumnName" with "Field_Name" with an additional input parameter. so that it creates another action Find And Replace Value In a specific field of a Collection' And it also works perfect!

For Each Row As DataRow In in_DT.Rows

	For Each Column As DataColumn In in_DT.Columns

		If (Row(Field_Name).Contains(in_ValueToFind)) Then

			Row(Field_Name) = Row(Field_Name).ToString.Replace(in_ValueToFind,in_ValueToReplace)

		End If

	Next
	
Next

out_DT = in_DT​

------------------------------
vinod chinthakindi
------------------------------

[devneetmohanty07]

Glad to know that @vinod chinthakindi. Actually in your example, I can see that you gave the keyword BP in two different fields:


So, I was not sure if you required a column specific operation or not. but glad to see you being able to modify it as well :)​

------------------------------
----------------------------------

Regards,
Devneet Mohanty
Intelligent Process Automation Consultant | Sr. Consultant - Automation Developer,
Wonderbotz India Pvt. Ltd.
Blue Prism Community MVP | Blue Prism 7x Certified Professional
Website: https://devneet.github.io/
Email: devneetmohanty07@gmail.com

----------------------------------
------------------------------