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

[michalides]

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]

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.

 

[michalides]

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]

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.

 

[Babjee Vangipurapu]

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

 

[michalides]

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

Hello my friend,

no I didn't. I have excel spreadsheets for this purpose which I load to empty collections. It should work for everything.

 

[Pete_L]

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.

I think VJ just mistyped. For a predefined collection, Initial values become Current values once the process starts.

 

[Pete_L]

I will suggest a different approach, that does not involve using a predefined collection.

Basically, the concept here is that you load the work queue up front with the data from the Excel country file, and in your process you use the work queue as sort of a lookup table to find the country name associated with a country code that you pass in as input.

Create a new work queue called Country Code, then populate this queue with the data in your Excel file (using the Add to Queue action of the Work Queues VBO). Use country code as the key field for the queue. Then, in your process, whenever you need to look up a country code, do the following:
1. Use the Work Queues VBO's Is Item In Queue action to get the Item ID of the row associated with the country code that you pass in. Store the output from this action in a collection called Item IDs. As long as the country codes are unique, you will only have 1 row in this collection.
2. Loop through the Item IDs collection and within the loop, use the Work Queues VBO's Get Item Data action to pull the country code and any data associated with it, like country name. To do this, pass in the Item ID from the Item IDs collection (i.e., pass in
[Item IDs.Item ID]). Store that in a data item so you can refer to it later.
3. You have just done a lookup using a work queue! I suggest putting this lookup code into a separate page in your process so you can call it whenever you need to.

Don't forget to clear the Country Codes queue and reload it from the Excel file each time you run. This will ensure that the most recent country code data is used each time (in case it changes in the Excel file). Changes are not likely in a list of countries, but you mentioined that you have another large file that you need to do the same thing with, and IDK if that file will be updated going forward.

Good luck!