Removing/Replacing text from a whole collection

[Sophie_Katherin]

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

[david.l.morris]

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

[david.l.morris · Accepted solution]

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

[TinnaHolst_Lars]

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

[NickLeGuerrier]

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

[KarinaHansen]

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

[NickLeGuerrier]

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

[VictorOkorie]

Hello Nick,

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

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

[NickLeGuerrier]

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