topic RE: Removing/Replacing text from a whole collection in Product Forum

Removing/Replacing text from a whole collection

Sophie_Katherin — Wed, 19 Jun 2019 14:22:00 GMT

Hi all,

I have a collection which contains about 80 columns. Some of the values (across columns and rows) contain the value "-" and I want to replace all of these with nothing. Is there any way to do this? I can see there is a way to do this by each column but looking for a solution that will look for and replace that value within the whole collection without having to do 80 different actions. The only other way I could think would be to loop through and keep sending the different column name each time but again seems very long winded!

------------------------------
Sophie Katherine Smith
HR Systems, Data and Quality Assurance Manager
Arup
Europe/London
------------------------------

RE: Removing/Replacing text from a whole collection

david.l.morris — Wed, 19 Jun 2019 16:05:00 GMT

You could adjust the code from the 'Utility - Collection Manipulation' action 'Remove dots from headers' to remove hyphens instead. At this point, it would be worth changing the hardcoded character to use an input parameter so that any character could be removed from headers in the future.

For Each Column As DataColumn In Input_Collection.Columns
Column.ColumnName=Microsoft.Visualbasic.Replace(Column.ColumnName,"-","")
Next
Output_Collection = Input_Collection

------------------------------
Dave Morris
3Ci @ Southern Company
Atlanta, GA
------------------------------

RE: Removing/Replacing text from a whole collection

david.l.morris — Wed, 19 Jun 2019 16:08:00 GMT

My fault. I just realized you mentioned you want the hyphens removed from the entire collection. In that case, you could convert the collection to CSV, use the Replace() function on the CSV text data item, and then convert back to Collection.

------------------------------
Dave Morris
3Ci @ Southern Company
Atlanta, GA
------------------------------

RE: Removing/Replacing text from a whole collection

TinnaHolstLArsen — Mon, 08 Feb 2021 06:25:00 GMT

Hi Sohpie

Did you ever find af sollution for this? I have the same issue.

------------------------------
Tinna Holst Larsen
Koordinator
IBC International Business College
Europe/Copenhagen
------------------------------

RE: Removing/Replacing text from a whole collection

NickLeGuerrier — Mon, 08 Feb 2021 15:55:00 GMT

Hi Tinna,

Here is a quick code stage that removes "-" from columns and rows. Keep in mind this can be done using loops in Blue Prism as well, but I find it faster to write it this way.

foreach (DataColumn c in dtIn.Columns)
{
c.ColumnName = c.ColumnName.Replace("-","");

foreach (DataRow r in dtIn.Rows)
{
r[c.ColumnName] = r[c.ColumnName].ToString().Replace("-","");
}
}

dtOut = dtIn;

------------------------------
Nick LeGuerrier
Senior RPA Developer
MD Financial Management
Ottawa
------------------------------

RE: Removing/Replacing text from a whole collection

KarinaHansen — Tue, 09 Feb 2021 14:09:00 GMT

Thanks Nick! I have been needing that as well.

Is it possible to alter the code, so it will only apply to the values of a specific column?

------------------------------
Karina Hansen
------------------------------

RE: Removing/Replacing text from a whole collection

NickLeGuerrier — Tue, 09 Feb 2021 14:28:00 GMT

Yes, definitely! Here you are:

In this case columnName is an input which you can provide to the method to find the column name you are looking for.

foreach (DataRow r in dtIn.Rows)
{
r[columnName] = r[columnName].ToString().Replace("-","");
}

dtOut = dtIn;

------------------------------
Nick LeGuerrier
Senior RPA Developer
MD Financial Management
Ottawa
------------------------------

RE: Removing/Replacing text from a whole collection

VictorOkorie — Fri, 26 Feb 2021 22:09:00 GMT

Hello Nick,

adding this code in a Blueprism code stage brings numerous errors, Can it be written in VB?

------------------------------
Victor Okorie
------------------------------

RE: Removing/Replacing text from a whole collection

NickLeGuerrier — Mon, 01 Mar 2021 14:09:00 GMT

Hi Victor,

My VB is a little rusty, but I think this should do it:

for each r as DataRow in dtIn.Rows
r(columnName) = r(columnName).ToString().Replace("-","")
next

dtOut = dtIn

------------------------------
Nick LeGuerrier
Senior RPA Developer
MD Financial Management
Ottawa
------------------------------