How to set Initial Values of a collection from excel file?

#1
Hi guys,

this seems pretty basic but can't find solution for this anywhere.

What I need is a fixed collection with list of countries and country codes which I have in xls but need to have it in BP collection as initial values. Of course I know how to Get Worksheet as collection but this will store the data to "Current Values". Is there a way I can have it in Initial Values as without having to import it there each time?

Thanks
 

VJR

Well-Known Member
Staff member
#2
Hi michalides,

Looks like you would like to have the country details hard coded in the collection.
Create a new collection, provide the desired column names and click Add Row and type in your countries and their codes. Keep on Adding Rows till you add all the countries. I do not see a way to directly import this data from a file like Excel so you will have to manually add the data one by one.

When you start the Process and when it is just on the 'Start' stage, the Current values become the Initial Values. You can press F10 on the start stage and open the collection and check.
 
#3
Hi VJ,

that is exactly the problem - that I have to manually put 300 countries there. And have another big collection where I also need to have the data as initial values.

And I'm not sure what do you mean that Current values become Initial values when I start the process? I don't see anything like that happening when I start the process.
 

VJR

Well-Known Member
Staff member
#4
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.
 
#5
Hi VJ,

that is exactly the problem - that I have to manually put 300 countries there. And have another big collection where I also need to have the data as initial values.

And I'm not sure what do you mean that Current values become Initial values when I start the process? I don't see anything like that happening when I start the process.
Hi Michalides,

Did you get the solution done for this if yes please help me further in concatenation I tried N ways but failed to fetch data.

Thanks in advance
 
Top