Excel to Dictionary

Help Activities
, , ,
1

Hello,
our goal is to convert an excel file to a dictionary (of String, String).
The Excel file has 100+ columns so we don´t want the manual effort of typing in every value and key of the dictionary.

Is there any possibility to convert all data (Column Name = Tkey; Row Value = TValue) automatically to the dicitionary.

2

Assign activity
dictKV | Dictionary(Of String, String) =

YourDataTableVar.AsEnumerable().ToDictionary(Function (x) x("KeyColName").toString.Trim,  function (x) x("ValueColName").toString.Trim)

Ensure that all Keys are distinct

3

@florian.eggert

Assign Activity:
dictionaryData = New Dictionary(Of String, String)()

For Each Activity (For Each row In excelData.Rows):
For Each Activity (For Each column In excelData.Columns):
Assign Activity:
columnName = column.ColumnName
cellValue = row(columnName).ToString()

    Assign Activity:
    dictionaryData(columnName) = cellValue
4

to interpret it within the direction that 100+Columns are to bring within the Dictionary where Columnname is the key we can do it for 1 row (using the second row would result to duplicated keys)

Assign activity
dictKV | Dictionary(Of String, String) =

(From i in Enumerable.Range(0,YourDataTableVar.Columns.Count)
Let k = YourDataTableVar.Columns(i).ColumnName
Let v = YourDataTableVar.Rows(0)(i).toString.Trim
Select t = Tuple.Create(k,v)).ToDictionary(Function (t) t.Item1, Function (t) t.Item2)

here we transfer the first row into a dictionary

6

Thanks for your Information.
I got the Error “BC30451 “x” was not declared…”

grafik

What value should I replace X with?

7

Sorry, did fix the missing function (x). Just refer to the updated LINQ
ich prüfe es parallel gleich nochmal gegen
EDITED: passed the cross check

8

Hi @florian.eggert

Use below expression in Assign Activity

DT.AsEnumerable().ToDictionary(Function(row) row(“KeyColumnName”).ToString().Trim(),Function(row) row(“ValueColumnName”).ToString().Trim())

Cheers!!

9

Hallo,
thanks for your answer.

I receive the following error message

grafik
grafik1646×185 8.38 KB

I

  • also create a new process to check your code. There was the same error message.
  • validating the Project using Analyze file button → No error message

What can be the problem?

10

@florian.eggert

Its working fine

  1. Excel with Column name as Key and Value
    image

  2. Initialize a Dictionary (of String,String)

    image
    image1477×50 2.57 KB

  3. Flow

DT.AsEnumerable().ToDictionary(Function(row) row(“Key”).ToString().Trim(),Function(row) row(“Value”).ToString().Trim())

image
image1082×377 16.1 KB

  1. Value I get
    image

Hope it helps you to understand better :slight_smile:

11

Hello,
thank you for all your solutions.

Unfortunately, we couldn’t solve our problem, because our Excel file structure is different.

Her is an example of our Excel file:

grafik
grafik1240×148 7.14 KB

Thank you for your help!

12

it is about the menitoned 1 row case (read range and set first line as header will result to a 1 row DT)

13

My first question is why. Why are you trying to convert a datatable to dictionary, when you can just reference the values from the datatable directly?

Anyway, you can’t convert an entire datatable to dictionary. Dictionaries only store one value per key. You could convert a datatable row to a dictionary, but again there isn’t any point to this. What are you trying to accomplish?

14

Hi @florian.eggert

  1. Read Excel File and stored DT as Variable (DataTable)
    image

  2. Initialize Dictionary (of String,String)

    image
    image1858×63 3.08 KB

  3. Use below Expression in Assign Activity

DT.Columns.Cast(Of DataColumn)().ToDictionary(Function(col) col.ColumnName, Function(col) DT.Rows(0)(col).ToString.Trim)

image
image880×288 16.3 KB

OUTPUT:
image

Hope it will helps you :slight_smile:
Cheers!!

15

Thanks.
Now it´s works.

I make a mistake by copy your code in the first time.

16

@florian.eggert
Perfect, so the topic can be closed
Forum FAQ - How to mark a post as a solution - News / Tutorials - UiPath Community Forum