topic RE: Find & Replace Value Within Collection in Product Forum

Find & Replace Value Within Collection

ZoltánTóth — Wed, 27 Oct 2021 09:40:00 GMT

Dear Community Members!

I have a collection with 10+ thousands rows and I need to rename some of its values. For example: "Cancellation Type A" should be "Cancellation", and "Cancellation Type B" should be "Cancellation" too. It is important that I am not looking for az exact match, for example "[Collection.Colunm] = "Result", I am looking for partial match, such as InStr([Collection.Column] = "Result" > 0. If this is True then I have to rename the value, but if it is False, then no action necessary.

I have a working solution with a loop, but it's fairly slow with this amount of rows. That is why I have been looking for a solution to resolve this with a code stage.

I could not find a solution within "Collection Manipulation" so I am here to ask the help of the community. 🙂

Thank you in advance!

Zoli

------------------------------
Zoltán Tóth
Communication specialist
Aegon Hungary Composite Insurance Company
Europe/Gibraltar
------------------------------

RE: Find & Replace Value Within Collection

shashank.kumar280 — Wed, 27 Oct 2021 11:12:00 GMT

Hi Zoltán,

I haven't tried this out for 10k rows but how about converting the collection to JSON using Utility - JSON and then updating/replacing all values in JSON, before converting it back to collection.

------------------------------
Shashank Kumar
DX Integrations Partner Consultant
Blue Prism
Singapore
+6581326707
------------------------------

RE: Find & Replace Value Within Collection

John__Carter — Wed, 27 Oct 2021 11:21:00 GMT

Is there a way to do the replacement when you read the 10K rows into BP? So rather than take the source data in its raw state, transform it as you import it, much like you could do with a database query. As you probably know, manipulating a huge collection can lead to memory/logging problems.

------------------------------
John Carter
Professional Services
Blue Prism
------------------------------

RE: Find & Replace Value Within Collection

ritansh.jatwani — Wed, 27 Oct 2021 11:34:00 GMT

Hi @ZoltánTóth,

In order to handle it efficiently with lesser time, i would suggest you to use Excel VBO to perform Find and Replace feature and then getting back the data to collection. If you try to perform it using "Collection manipulation", it will perform record by record and hence would be very time consuming and consuming a lot of memory, thereby causing memory leakage. If you need code stage to perform the "Find and Replace" feature, i can help you with that as well.

------------------------------
Ritansh Jatwani Senior Consultant
Consultant
EY
Gurgaon
*If you find this post helpful mark it as best answer
------------------------------

RE: Find & Replace Value Within Collection

ZoltánTóth — Wed, 27 Oct 2021 12:15:00 GMT

Yeah, the memory shortage is the reason why I am looking for a better solution.

I have tried every other solution rather than loop stage, but writing the data to Excel and creating a pivot is almost as time consuming as the loop itself, so I am in the same position.

Help with a code stage would be nice. 🙂

Thanks,
Zoli

------------------------------
Zoltán Tóth
Communication specialist
Aegon Hungary Composite Insurance Company
Europe/Gibraltar
------------------------------

RE: Find & Replace Value Within Collection

ZoltánTóth — Wed, 27 Oct 2021 12:18:00 GMT

It sounds about right, but "Replace" requires perfect match, but I have dynamic text such as "Cancellation Type A", "Type B", "Type C" and so on. The only thing sure is "Cancellation", that is why I can't use "Replace".

Regards,
Zoli

------------------------------
Zoltán Tóth
Communication specialist
Aegon Hungary Composite Insurance Company
Europe/Gibraltar
------------------------------

RE: Find & Replace Value Within Collection

michaeloneil — Wed, 27 Oct 2021 13:00:00 GMT

Hi Zoltan

As Ritansh has suggested try using excel to edit the data, you can do this using the filters in excel to get a non exact match e.g. Cancellation A, Cancellation B are values in a column but if you filter the column using the text Cancellation you will get both A and B returned. There isnt anything native to blueprism that will filter the columns for you but I previously built something to do this and have included the code below. Be aware this code is to filter the columns you must first turn filters on for the row you need to filter on. (I think BP has a set filters on/off action) Once you have filtered you can use the excel actions to manipulate the rows to what you need then remove the filters.

Filter columns by criteria code - input values handle, workbookname, worksheetname, range (this is the range for the rows you want to include e.g. A1:G75), columnnumber (e.g. A is 1) criteriastring.
-------------------------------------------------------------------------------------------------------------------------------------------------------
Dim ws as Object, strList as Object

ws = GetWorksheet(handle, workbookname, worksheetname)

Try

If (criteriastring<>"") Then
strList = Split(criteriastring,",",-1)
'ColNum = ws.Range(range).Find(columnname).Column
ws.Range(range).AutoFilter(Field:=columnnumber, Criteria1:= strList, Operator:=7)
success = True
Else
success = False
message = "CriteriaString should not be blank"

End If

Catch ex as exception

success = False
message = ex.Message

End Try

Hope this helps

------------------------------
Michael ONeil
Technical Lead developer
Everis Consultancy
Europe/London
------------------------------

RE: Find & Replace Value Within Collection

ZoltánTóth — Wed, 27 Oct 2021 13:15:00 GMT

Thank you, Michael!

It is almost what I need. As I mentioned above to use Excel VBO I have to write my collection into Excel, then run your code. Writing a collection into an Excel file is still very time consuming.

I am looking for a solution that can manipulate the values in my collection without using a loop or Excel VBO.

Thanks,
Zoli

------------------------------
Zoltán Tóth
Communication specialist
Aegon Hungary Composite Insurance Company
Europe/Gibraltar
------------------------------

RE: Find & Replace Value Within Collection

ritansh.jatwani — Wed, 27 Oct 2021 14:11:00 GMT

Hi @ZoltánTóth,

What is the source of your collection data?

------------------------------
Ritansh Jatwani Senior Consultant
Consultant
EY
Gurgaon
*If you find this post helpful mark it as best answer
------------------------------

RE: Find & Replace Value Within Collection

ZoltánTóth — Wed, 27 Oct 2021 14:18:00 GMT

Our company's CRM system. It is not a public CRM system, it is built by the company.

There is no way to extract it as a .csv, .xlsx. The only thing I can do is copy to clipboard and then paste it into a collection.

------------------------------
Zoltán Tóth
Communication specialist
Aegon Hungary Composite Insurance Company
Europe/Gibraltar
------------------------------

RE: Find & Replace Value Within Collection

ritansh.jatwani — Wed, 27 Oct 2021 14:27:00 GMT

Hi @ZoltánTóth,

In such a scenario, rather than pasting it to collection, we can directly paste it in Excel since pasting operation would be much faster than writing collection in Excel. Once the data gets pasted in Excel, you can use code stage to perform your tasks.

------------------------------
Ritansh Jatwani Senior Consultant
Consultant
EY
Gurgaon
*If you find this post helpful mark it as best answer
------------------------------

RE: Find & Replace Value Within Collection

ZoltanBALINT1 — Wed, 27 Oct 2021 15:01:00 GMT

Hello Zoltan,

You would be better off if you did these manipulations in a database.
In the assumption that a DB is not an option, you can indeed try a code stage.

A crude draft in VB.net would look like this:

		' If you pass your collection as input argument, it comes in as DataTable type
		' Let's assume you called it myInputTable, and it had two columns with text fields [Customer, Cancellation type]
		
		' Here we create a new table to copy the stuff over
        Dim localTable As DataTable = New DataTable()
		localTable.Columns.Add("Customer", GetType(String))
        localTable.Columns.Add("CancellationType", GetType(String))
        

        Dim myCurrentTableRow As DataRow
		Dim tmpStr as String
        For Each myCurrentTableRow In myInputTable.Rows
			tmpStr = myCurrentTableRow.GetValue(1)
			If tmpStr.Contains("ellation") OR tmpStr.Contains("CANC") Then 
				tmpStr = "Cancellation"
			End If
            localTable.Rows.Add(myCurrentTableRow.GetValue(0), tmpStr)
		Next
			
		' you could return localTable or create a copy and decalare myOutputTable as the output collection in the code stage output parameters	
			myOutputTable = localTable

DISCLAIMER: I did not test this, so the sample is meant just to get you going with the data types and the methods you need. Surely can google the rest.

------------------------------
Zoltan BALINT
------------------------------

RE: Find & Replace Value Within Collection

michaeloneil — Thu, 28 Oct 2021 07:59:00 GMT

Hi Zoltan

If I'm understanding you right then what you are wanting to do is have all your data in a collection, search the entire collection for a specific term and replace all values matching that term with a new value? in that case then the below code will do that. However please note that although this will work faster than a loop query it still works through each row of a collection so with very large data volumes I'm not sure how fast it will be but this is the only way I know of doing what you want with a collection in a code stage. Please also be aware that this is using a loose search term with term greater or equal to the term you enter. If it is exact match you want then remove the > .

Inputs Collection In, Search Term, New Row Value - Outputs Collection Out
-----------------------------------------------------------------------------------
try
for each dr as datarow in Collection_In.rows
for each c as datacolumn in Collection_In.columns
if dr(c.columnname) >= Search_Term then
dr(c.columnname) = New_Row_Value
end if
next
next
Collection_Out = Collection_In
catch e as exception

end try

------------------------------
Michael ONeil
Technical Lead developer
Everis Consultancy
Europe/London
------------------------------

RE: Find & Replace Value Within Collection

salman_shaik — Fri, 05 Aug 2022 19:11:00 GMT

Hi
can you share how to use find and replace feature in BP

------------------------------
Salman Shaik
------------------------------

RE: Find & Replace Value Within Collection

salman_shaik — Fri, 05 Aug 2022 19:52:00 GMT

Hi Ritansh Jatwani,

can you share it

------------------------------
Salman Shaik
------------------------------