Find & Replace Value Within Collection

[ZoltánTóth]

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
------------------------------

[shashank.kumar280]

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
------------------------------

[John__Carter]

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
------------------------------

[ritansh.jatwani · Accepted solution]

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
------------------------------

[ZoltánTóth]

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
------------------------------

[ZoltánTóth]

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
------------------------------

[michaeloneil]

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
------------------------------

[ZoltánTóth]

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
------------------------------

[ritansh.jatwani]

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
------------------------------

[ZoltánTóth]

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
------------------------------