Convert datatable to dictionary with repeating values

Help Studio
1

Hello,
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.

olo

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.

2

Hi,

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)

Sample20210204-1.zip (8.9 KB)

Regards,

1 Like
3

@JavXult
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:
grafik

LINQ GroupBy Approach:
grafik

(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)

2 Likes
4

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

5

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?

6

a good starters are here:

2 Likes
7

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())
2 Likes
8

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