Hi michalides,
I don't see
a direct way to have those 300 entries into the Collection at design time
without manually entering them or through Get Wksheet as Collection.
Alternatively you can do a back-door entry approach
if you have access to the Blue Prism database.
The procedure is as follows-
- Create a Collection with the desired columns for the Countries and their codes
- Add one or two rows of data to it. You can add valid countries to it too
- A collection will be stored as an xml in the BP database
For eg;
Below is my collection with two rows
View attachment 1538572259086.png
- Do a copy (Ctrl C) on the collection and then paste (Ctrl v) onto a text editor which will give you the xml code behind as follows
View attachment 1538573824064.png
- All you need to do is have a way to update this xml with the country data you need. Insert the column names in the red area and the countries and their codes in the blue section.
This can be done in multiple ways. If you are aware of any programming language like .net, scripting, vba etc, then you can directly read your source excel file and form the xml string to match the above format along with the country data.
- To do it non-programmatically you can use the same Excel you already have and use a concatenation formula to append the country data in the formula for each excel row.
<row>
<field name="Column1" type="text" value=
<COUNTRY NAME HERE> />
<field name="Column2" type="number" value=
<COUNTRY CODE HERE> />
</row>
The non-bold text can be hard coded and the bold ones will come from the Excel formula (from the country data that you already have).
You just need to put the formula for the first row of your excel data and drag to the 300th one.
- Once the xml string is generated with all the 300 countries,
Paste it at the correct location (blue marked) in the xml that was generated at the beginning of this activity (Screenshot #2) and then go to the Blue Prism database.
Table Name:
dbo.BPAProcess.
In this table, the Name column has the process or the object name, the ProcessType column contains whether it is a Process or an Object, and the ProcessXml column stores the xml. Here you can paste (or use Update SQL query) your Collection xml generated above. All care needs to be taken to ensure the stage id, process name etc. match between the xml and the database entry so that the correct collection is overwritten with this new collection data of 300 entries.
Before trying the entire xml just manually add 1 row of data to the xml in the database and see if the collection generates that 1 row or if you have any access issues, etc.
Make sure to take the necessary backups before the activity since you are directly interacting with the BP database.