Rename Collection

[DhirenPatel]

Hi all,

Struggling to find an answer to this. I'm hoping someone has experienced and addressed an issue similar to what I'm having - so basically I'm loading a spreadsheet into a collection which is fine, but the issue in trying to resolve is sometimes the headers will change and can be like - 

Acc_Num,

Account_Number

Ac_Num

The Customer number can/will be in the same predicament

I' need these fields to always be Account Number & Customer Number, can anybody suggest the best way to do this?

I hope this makes sense. Example picture below - 

Thanks in advanced

------------------------------
Dhiren Patel
Robotic Process Automation Developer
Leeds Building Society
Europe/London
------------------------------

[MichealCharron]

@DhirenPatel

If the Account Number and Customer Number fields always come in at the first and second position, you can use the "Set Column Names From Expected Collection" action in BP's "Utility - Collection Manipulation" VBO. You just use a "Expected Fields/Columns" collection with those two field names (you don't have to list the other field names) and it will rename those two fields.

------------------------------
Micheal Charron
Senior Manager
RBC
America/Toronto
------------------------------

[DhirenPatel]

Hi - thanks for your reply. I wish they did! unfortunately they could be scattered around the input feed!

------------------------------
Dhiren Patel
Robotic Process Automation Developer
Leeds Building Society
Europe/London
------------------------------

[LeonardoSQueiroz]

Hello, one possibility is to use logic with a list of similar recurrences but it is not very safe, I believe that the safest way is to normalize the input, check if the requirement cannot be standardized, or if there is another possibility of extraction. Have you tried exporting this spreadsheet using other methods? Via database, changing the export type, etc? Is the input always the same?

Regards,

------------------------------
Leonardo Soares
RPA Developer Tech Leader
Bridge Consulting
América/Brazil
------------------------------

[MichealCharron]

@DhirenPatel

You could use stages similiar to the following:

1. Getting a listing of the fields ([Collection Fields]).

2. Using a regex pattern (that you could store in a BP Env Var to change on the fly) in the "Collection Contains Value" action in BP's "Utility - Collection Manipulation" VBO to see if the listing of fields finds any occurrence of the incorrect field names and if so, has that incorrect occurrence placed into the "Groups" collection .

	"^(Ac_Num|Account_Number|Acc_Num)$"

3. Check to see if the "Collection Contains Value" action found one of those incorrect occurrences. If not, throw an exception. 

4. If an incorrect occurrence was found, use the [Groups.Group] value to rename the field to "Account Number".

5. Repeat the blue section above for the Customer Number.

This assumes that you never get a field called "Customer Number" but if you did sometimes you can use the "Collection Contains Value" action with the "Exact Value" parameter to make a decision around all the above blue section stages.

------------------------------
Micheal Charron
Senior Manager
RBC
America/Toronto
------------------------------

[John__Carter]

Before reading the data, do a Search/Replace on the top line to normalise any variants of Account Number and Customer Number. Then read the data, then close the file without saving changes. For this to work you have to know what the variants could be.

------------------------------
John Carter
Blue Prism
------------------------------

[michaeloneil]

Hi @DhirenPatel

A simple solution might be to do a find and replace in excel but if not then you can get the worksheet/range as a collection (dont define the collection headers as this might cause an error). Use Get Fields action then loop and using a decision stage look for a name that closely matches what you are looking for e.g. the decision could have StartsWith(Lower([Fields.Name]), "ac") AND InStr(([Fields.Name]),"nu")>0 to match as account number. If true then use the action Rename Field to change the field to what you need. 

------------------------------
Michael ONeil
Technical Lead developer
NTTData
Europe/London
------------------------------