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
------------------------------
Original Message:
Sent: 10-27-2021 13:17
From: Zoltán Tóth
Subject: Find & Replace Value Within Collection
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
Original Message:
Sent: 10-27-2021 12:12
From: Shashank Kumar
Subject: Find & Replace Value Within Collection
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
Original Message:
Sent: 10-27-2021 10:39
From: Zoltán Tóth
Subject: Find & Replace Value Within Collection
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
------------------------------