I am currently going through an automation problem. I am trying to convert an excel document into a datatable and then convert it into a dictionary, for a better context the excel workbook would be looking like this.
I have a column with unique values "Product ID" and another with repeated values "Store ID", what I am trying to do is make a type of query in which I convert these values into a dictionary where the main key is “Store ID” and the value of this would be a List with all the “Product ID” that correspond to the store.
I am still new to UiPath and have tried to use methods like LINQ query but have not been able to perform the transformation correctly as the key values are repeated in the original file. What tool, methods or activities do you recommend using for such a situation, without having to use so many validations.
we did work with a smaller sample set and with the assumption of dictionary is of String, String. Adoptions to this can be done as well.
LINQ GroupBy Approach:
(From d In dtData.AsEnumerable
Group d By k=d("StoreID").toString.Trim Into grp=Group
Let ls = String.Join(",", grp.Select(Function ( p ) p("ProductID")))
Select Tuple.Create(k,ls)).toDictionary(Of String, String)(Function (x) x.Item1,Function (x) x.Item2)