Collection matching/comparing without header

[StanfordM]

I am currently learning Blue Prism.  I am trying to find the best way to take the data from one system [Collection 1] match it to data from another system [Collection 2], and when there is a match load the matching row to a third collection to export it into a csv or xlsx file.  One issue that I have is that collection 2 file does not have a header row.  So matching email address from collection 1 to the email address from collection 2 is proving to be a challenge.  How would you all handle this scenario? 

------------------------------
Stanford Moore
------------------------------

[PabloSarabia]

Hi @Stanford Moore

Everything is possible here 🙂

For join the collections, you can use some Linq code, with a Code Stage in a VBO to perform this.

Here you have some links that can helps you:

- How to add Linq to VBO (dll and namespaces references) use LINQ to collection
- This one for the Syntax of Linq: https://www.codeproject.com/Questions/1067490/Csharp-Linq-join-two-DataTables


For write the result in a CSV file, you must to use this actions:

1- From the Object "Utility - Strings", use the action "Get Collection as CSV", its returns you all the text.
2.- Then write all the text in a file using the Object "Utility - File Management", action "Write Text File". 



Hope this helps you!

See you in the community, bye 🙂

------------------------------
Pablo Sarabia
Architect
Altamira Assets Management
Madrid
------------------------------

[StanfordM]

Thanks for the information.  I am not sure if I am joining tables, unless I need to join with another collection/table in order to assign headers to [collection 2].

This is more what I am trying to accomplish.

Collection 1
Email1
Email3
Email4


Collection 2
--------
EmpName Grade   Email         Certification <---- Added headers just to show, but file does not contain headers.
Name1         2         Email1       Certification1
Name2         2         Email2       Certification2
Name3         2         Email3        Certification3
Name4         5         Email4       Certification4
Name5          5        Email5       Certification5

Expected Output: (email address matches the email column in collection 2, then writes complete row to collection 3.  Hard to match because collection 2 file does not have header row)
----------------
EmpName Grade    Email     Certification
Name1         2          Email1   Certification1
Name3         2          Email3   Certification3
Name4         5          Email4   Certification4

------------------------------
Stanford Moore
------------------------------

[devneetmohanty07]

Hi Stan,

For this scenario, if you are sure that the number of columns are always same in the second collection then I would suggest go ahead and create a define collection with a schema consisting of all the columns like EmpName, Grade and so on.

You can then add all the rows from the second collection to this defined collection which can give you a collection with headers and values from the collection without any headers. Then you can simply loop over the second collection and use a filter collection action on the 'Email' column from first collection with the current email value of second collection and get an output with the same data being reflected. Store this output as a collection with name 'Rows Matching From First Coll'.

Now do the the same check by looping over first and use a filter collection action on the 'Email' column from second collection with the current email value of first collection and get an output with the same data being reflected. collection. Store this output as a collection with name 'Rows Matching From Second Coll'. 

At the end just append both the output collection you received into a Final Collection. That is how you can do it easily

------------------------------
----------------------------------
Hope it helps you out and if my solution resolves your query, then please mark it as the 'Best Answer' so that the others members in the community having similar problem statement can track the answer easily in future

Regards,
Devneet Mohanty
Intelligent Process Automation Consultant | Sr. Consultant - Automation Developer,
Wonderbotz India Pvt. Ltd.
Blue Prism Community MVP | Blue Prism 7x Certified Professional
Website: https://devneet.github.io/
Email: devneetmohanty07@gmail.com

----------------------------------
------------------------------

[StanfordM]

Thank you Devneet, I was thinking along these lines.  I did have trouble joining the header collection to collection 2.  But I will work on it some more.

Also I heard you talk in the Blue Prism communication member appreciation day today.  Congrats on your award today. 

------------------------------
Stanford M
------------------------------

[StanfordM]

Devneet, 

What would be the best action to use to write(add) the collection without headers to the header collection?

------------------------------
Stanford M
------------------------------

[devneetmohanty07]

Hey Stanford,

Thanks a lot for your wishes 🙂 It means a lot.

Coming to your question, when you say a collection without headers what you mean exactly like there are arbitrary column names Column1, Column2 and so on since every collection must have some header when it lies inside Blue Prism. If this is what you mean, then first I would suggest rename the headers where you can map the column names to a header collection by using 'Set Column Names From Expected Collection' or 'Rename Collection Fields' and then use the 'Append Rows To Collection' action. We are renaming the column names as we have need to have similar schemas between two collection which needs to be appended.

Otherwise, you will have to loop through one collection and use 'Add Row' action with the output collection and set the current cell values which is the traditional approach.

------------------------------
----------------------------------
Hope it helps you out and if my solution resolves your query, then please mark it as the 'Best Answer' so that the others members in the community having similar problem statement can track the answer easily in future

Regards,
Devneet Mohanty
Intelligent Process Automation Consultant | Sr. Consultant - Automation Developer,
Wonderbotz India Pvt. Ltd.
Blue Prism Community MVP | Blue Prism 7x Certified Professional
Website: https://devneet.github.io/
Email: devneetmohanty07@gmail.com

----------------------------------
------------------------------