Community Chat

last person joined: 4 days ago 

A space for discussion around the Community site and programs.

Expand all | Collapse all

Find & Replace Value Within Collection

Jump to Best Answer
  • 1.  Find & Replace Value Within Collection

    Posted 10-27-2021 10:40
    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
    ------------------------------


  • 2.  RE: Find & Replace Value Within Collection

    Posted 10-27-2021 12:12
    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
    ------------------------------



  • 3.  RE: Find & Replace Value Within Collection

    Posted 10-27-2021 13:18
    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
    ------------------------------



  • 4.  RE: Find & Replace Value Within Collection

    Posted 10-27-2021 14:00
    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
    ------------------------------



  • 5.  RE: Find & Replace Value Within Collection

    Posted 10-27-2021 14:15
    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
    ------------------------------



  • 6.  RE: Find & Replace Value Within Collection

    Posted 10-27-2021 15:11
    Hi @Zoltán Tó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
    ------------------------------



  • 7.  RE: Find & Replace Value Within Collection

    Posted 10-27-2021 15:18
    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
    ------------------------------



  • 8.  RE: Find & Replace Value Within Collection

    Posted 10-27-2021 15:27
    Hi @Zoltán Tó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
    ------------------------------



  • 9.  RE: Find & Replace Value Within Collection

    Posted 10-27-2021 16:01
    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
    ------------------------------



  • 10.  RE: Find & Replace Value Within Collection

    Posted 10-27-2021 12:21
    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
    ------------------------------



  • 11.  RE: Find & Replace Value Within Collection
    Best Answer

    Posted 10-27-2021 12:34
    Hi @Zoltán Tó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
    ------------------------------



  • 12.  RE: Find & Replace Value Within Collection

    Posted 10-27-2021 13:15
    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
    ------------------------------



  • 13.  RE: Find & Replace Value Within Collection

    Posted 10-28-2021 08:59
    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
    ------------------------------



Welcome to Community Chat!

A space for non-technical chat, tips for using the Community, and news from the Blue Prism Community Team.

This is not a Community for technical or Product related discussion. For a list of available communities, please visit our site map.

Introduce YourselfCommunity Quick TipsWomen in RPA