Replace a character

[shahs]

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

[MetVonghiran]

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

[shahs]

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

[Carl__Carter]

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

[shahs]

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

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

[VivekGoel]

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

[Carl__Carter]

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

[RaviTeja]

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

[Carl__Carter]

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