Create Dictionary with string as key and list/array as values

My project has an excel in the data folder that contains translation data. So A1 might be “Dog” and then “A2” might be “Perro” and “A3” might be “Chien” and so on. I want to load this into memory, so that given any word in English I can quickly find the translation to Spanish or French (this is just a made up example).

There will be about 20 columns and 3-4 rows.

My thought is that this is a good use of a dictionary with the key as a string, and the value as a list or a string array.

However, I cannot get this to work.

Could someone please assist m with this? I would very much appriciate it.

lets assume following table:

with following LINQ we can construct a nested Dictionary:

(From d In dtData.AsEnumerable
Let cn = dtData.Columns.Cast(Of DataColumn).Skip(1).Select(Function (x) x.Columnname).toArray
Let dtrs = d.ItemArray.Skip(1).Select(Function (x,i) Tuple.Create(cn(i),x.toString.Trim)).toDictionary(Function (k) k.Item1, Function (v) v.Item2)
Select Tuple.Create(d(0).toString.Trim, dtrs)).toDictionary(Function (t) t.Item1, Function (t) t.Item2)

and will return us a Dictionary(Of String, Dictionary(Of String,String))

LINQ can be optimized by bringing following line into a variable and using it within LINQ without the need to construct again and again:

Let cn = dtData.Columns.Cast(Of DataColumn).Skip(1).Select(Function (x) x.Columnname).toArray


find starter help here (The approach is dynamic and extending a new lang requires just adding a new column to the datatable):
NestedDict_TranslationMapper.xaml (7.6 KB)

1 Like