Creating a Dictionary of list from excel file

[Tuannna1 (Tuan Anh)]

Hi everyone,

I am working on a process which has a master excel. This excel contains around 40+ rows and 11+ columns. The first row header is unique for all the row. In the process we are passing the data table to each workflow to get the result.
What I am thinking is to convert the whole data table to a dictionary of key being the unique column and its value being the rest of the data in a list.
But I am facing some issues in creating the dictionary. Need help on it.
I have attached a file template that I am working with.
UiBank Accounts.xlsx (14.0 KB)

[Anil_G (Anil Gorthi)]

@Tuannna1

Please follow below steps

1. First initialize the dictionary - New Dictionary(Of String,List(Of String))
2. Use a loop to loop through each column Dt.Columns and change the type argument to System.Data.DataColumn
3. Inside use use assign
   dict(item.ColumnName) = st.AsEnumerable.Select(function(x) x(item.ColumnName).ToString).ToList()

cheers

[supermanPunch (Arpan)]

Hi @Tuannna1 ,

An Alternate using Linq approach :

1. Create a Dictionary variable and initialise it using Assign Activity, say dictOfColumnValues.

dictOfColumnValues = new Dictionary(Of String,List(Of String))

2. Next, you could use the below Expression using Assign Activity to get the Dictionary values created :

dictOfColumnValues = DT.Columns.Cast(Of System.Data.DataColumn).ToDictionary(Function(x)x.ColumnName,Function(y)DT.AsEnumerable.Select(Function(s)s(y.ColumnName).ToString).ToList)

Let us know if you are not able to get the required output.