如何修剪通过Excel VBO「Copy as Collection」获取的集合列名多余空格？

Got it, this is a super common pain point when working with dynamic collections pulled directly from Excel—those trailing spaces in column names can mess up downstream logic. Here's a robust, dynamic solution that works without hardcoding column names, so it'll sync with any Excel changes automatically:

Step-by-Step Solution #

1. Extract Original Column Names #

First, grab all the column names from your original collection using Blue Prism's built-in function:

• Create a Text Array variable (e.g., original_col_names)
• Assign it the value: Collection.ColumnNames(YourOriginalCollection)

2. Trim Spaces from Each Column Name #

You can do this with either a Blue Prism loop or a quick code stage (the code stage is faster for larger collections):

Option A: Code Stage (C#)

Add a Code Stage with:

• Inputs: original_col_names (Text Array)
• Outputs: trimmed_col_names (Text Array)
• Code:

trimmed_col_names = original_col_names.Select(col => col.Trim()).ToArray();

(This trims both leading and trailing spaces—perfect if Excel ever adds leading spaces too)

Option B: Pure Blue Prism Loop

• Initialize an empty Text Array variable trimmed_col_names
• Add a Loop action that iterates over original_col_names
• In each loop iteration:
  • Trim the current column name: Trim(Current Item)
  • Append the trimmed name to trimmed_col_names using Collection.Add To Array

3. Create a New Collection with Clean Column Names #

• Use the Collection.Create action
• Set the Columns parameter to trimmed_col_names
• Store the result in a new collection variable (e.g., cleaned_collection)

4. Copy Data from Original to Clean Collection #

Again, two options—code stage for speed, or pure Blue Prism actions for no-code:

Option A: Code Stage (C#)

Add another Code Stage with:

• Inputs: YourOriginalCollection (Collection), trimmed_col_names (Text Array)
• Outputs: cleaned_collection (Collection)
• Code:

foreach (DataRow row in YourOriginalCollection.Rows)
{
    DataRow newRow = cleaned_collection.NewRow();
    for (int i = 0; i < YourOriginalCollection.Columns.Count; i++)
    {
        newRow[trimmed_col_names[i]] = row[original_col_names[i]];
    }
    cleaned_collection.Rows.Add(newRow);
}

Option B: Pure Blue Prism Loop

• Add a Loop action that iterates over YourOriginalCollection
• In each iteration:
  • Use Collection.Add Row to add a blank row to cleaned_collection
  • Add a nested Loop that iterates over original_col_names
  • Inside the nested loop:
    • Get the value from the original collection: Collection.Field(YourOriginalCollection, Current Row, Current Item)
    • Find the matching trimmed column name (use Array.FindIndex to get the index of Current Item in original_col_names, then fetch the value from trimmed_col_names at that index)
    • Set the value in the clean collection: Collection.SetField(cleaned_collection, Current Row, trimmed_col_name, extracted_value)

Why This Works #

Since we're dynamically pulling column names from the original collection and trimming them before creating the new collection, this solution will automatically adapt if you add, remove, or rename columns in Excel—no hardcoding required.

内容的提问来源于stack exchange，提问作者rpa_learning