topic Replace a character in University Forum

Replace a character

shahs — Sun, 19 Jan 2020 17:06:00 GMT

Hi,

I want to replace the spaces with string "_" in multiple rows of a collection. Blue Prism allows to do so with replace function for first row only. How can it be done for multiple rows?

Thanks

------------------------------
shah s
------------------------------

RE: Replace a character

MetVonghiran — Mon, 20 Jan 2020 02:20:00 GMT

Hello, shah s

You can use loop stage to loop through all the rows in the collection.

Since you have successfully replace the character in one row of the collection, you should be able to achieve what you were trying to do with adding a loop.

Regards,

Met Vonghiran

------------------------------
Met Vonghiran
Consultant
Deloitte
Asia/Tokyo
------------------------------

RE: Replace a character

shahs — Mon, 20 Jan 2020 19:57:00 GMT

Thanks for checking. I have tried with loop but collection does not store all values. I want all rows stored in collection. Is there any way to fill "Substitute" function in excel through blue prism? I tried with entering formula in one cell in excel and then copy pasting in other cells but "=Substitute(A1," ", "_")" is not working.

------------------------------
shah s
------------------------------

RE: Replace a character

Carl__Carter — Mon, 20 Jan 2020 20:12:00 GMT

Hi,

A Collection does store rows of data, just like rows in an Access Database table, or even rows in an Excel Worksheet.

The Loop stage loops through the Collection rows 1 by 1 allowing you to use a Calculation stage to update the value of the row column.

The Replace function in a Calculation stage will provide the function you need.

There are other ways to achieve this if working with large Collections, but that's more advanced so use the Loop stage.

------------------------------
Carl Carter
Developer Program Manager
Blue Prism
Europe/London
------------------------------

RE: Replace a character

shahs — Tue, 21 Jan 2020 20:27:00 GMT

Thanks Vivek. Is this Xml globally available or you have coded it for this question?

------------------------------
shah s
------------------------------

RE: Replace a character

vivek.kumar.goel — Thu, 23 Jan 2020 05:23:00 GMT

Use the code below to create a custom action for this.
Make 3 Inputs:
Blanks- Collection name
TBR: The value to be replaced (_)
Value: The value which you want to replace by ("ABC")

try
  for each dr as datarow in blanks.rows
    for each c as datacolumn in blanks.columns
      if (dr(c.columnname)).ToLower() = (TBR).ToLower() then
        dr(c.columnname) = Value
      end if
    next
  next
  no_blanks = blanks
catch e as exception

end try

------------------------------
Vivek Goel
RPA Architect
Asia/Singapore
"If you like this post, please press the "Recommend" Button.
------------------------------

RE: Replace a character

Carl__Carter — Thu, 23 Jan 2020 09:00:00 GMT

@vivek.kumar.goel - your solution will only replace the Collection row value if it is equal to the value of TBR. In @shahs use case, the code would replace row values which are equal to a single space " ". All rows containing anything other than a single space will be unaffected.

I believe Shah is looking to replace all occurrences of the space char, and whilst some small changes to your code would achieve this, it is also possible through standard Blue Prism stages and the 'Utility - Collection Manipulation' Business Object.

I've provided an example of how to achieve this without using custom code:

Process xml attached.

Hope this helps.

------------------------------
Carl Carter
Developer Program Manager
Blue Prism
Europe/London
------------------------------

RE: Replace a character

RaviTeja — Tue, 04 Feb 2020 04:49:00 GMT

Hi Carl,

I have gone through code and found an issue that code is removing spaces in password fields also. Kindly check the below code which handled the bug through code stage.

Collection_Out=Collection_In.Copy

for each dr as Datarow in Collection_Out.Rows

  for each dc as DataColumn in Collection_In.Columns

   If( GetBluePrismDataType(dc.datatype)="text" )

     If(dr(dc.columnname).Contains(" ")) then
        dr(dc.columnname)=Replace(dr(dc.columnname)," ","_")
     end If

   End If
  
  Next

Next

------------------------------
Ravi Teja
RPA Developer
Accenture
Asia/Kolkata
------------------------------

RE: Replace a character

Carl__Carter — Wed, 05 Feb 2020 13:14:00 GMT

Hi Ravi,

I wouldn't expect a password field to contain spaces, however a simple decision stage added to the solution I provided could prevent a password field being updated. You can use the column name of the password column(s) in the decision stage, and ignore updating these columns.

------------------------------
Carl Carter
Developer Program Manager
Blue Prism
Europe/London
------------------------------