Convert datatable to dictionary with repeating values

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.


There are some ways to achieve it.
For example, the following sample will work.

dict = dt.AsEnumerable.Select(Function(r) r("Store ID").ToString).Distinct.Cast(Of String).ToDictionary(Function(x) x,Function(x) dt.AsEnumerable.Where(Function(y) y("Store ID").ToString=x).Select(Function(z) z("Product ID").ToString).ToList) (8.9 KB)


1 Like

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.

Input, Output:

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)

Find starter help here:
DTToDictionary_GroupBy_ValueList.xaml (7.3 KB)


Thanks! it works!, It is possible that you can recommend some documentation to me so I understand and perform the functions in that way?

It also worked with your example, and like in the other example, It is possible that you can recommend some documentation to me so I understand and perform the functions in that way?

a good starters are here:


Use group by
very simple

dict = dt.AsEnumerable().GroupBy(Function(r) r(1), Function(r) r(0)).
ToDictionary(Function(g) g.Key, Function(g) g.ToList())

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.