How can i write dictionary values to excel

Help Studio
, ,
1

In dictionary if i have key as “Name”,“Age”,“Id” and value as “Raju,Susha,Madhu” ,“21,22,23” ,“101,102,103”.how can i write this in to an excel as

NAME Age Id
Raju 21 101
Susha 22 102
Madhu 23 103
Can anyone please suggest how can i do this

3

@Chippy_Kolot

4

Hi Chippy_Kolot,
Follow the steps as below:

  1. Use a BUILD DATATABLE activity and create a datatable with similar column structure of your excel and get the output with a variable of type datatable named dt
  2. Now use a FOR EACH activity and pass the dictionary variable as input and change the type argument as System.Collections.Generic.KeyValuePair<TKey, TValue>
  3. Inside the loop use a add datarow activity and mention the array row property like this
    {item.Key.ToString,item.Value.ToString}
    assuming that we have only two columns

now after this FOR EACH loop we can use APPEND RANGE Activity inside EXCEL APPLICATION SCOPE and pass the filepath of excel as input so that it will append this datatable at the end

image

Cheers!

5

Hi @Chippy_Kolot

Check out the below thread.

Regards

6

@Chippy_Kolot

You can follow the steps

  1. Create a datatble with 3 columns usign build datatable say dt and create an integer variable indexvar
  2. Now use a for loop with input as dict("Name").ToString.Split(","c) and assign indexvar to index in for loop properties
  3. Inside loop use add data row with dt as datatable and arrayrow as {currentItem,dict("Age").ToString.Split(","c)(indexvar),dict("Id").ToString.Split(","c)(indexvar)}
  4. After loop use write range with dt

Hope this helps

cheers

7

With some optimistics (always the same length) we can do it with LINQ

grafik
out: dtData

Assign Activity:
SegmentSplit | int32 =
myDict.Values.Max(Function (x) x.Split(","c).Length)

Assign Activity:
dtData =

(From t In myDict.Values.SelectMany(Function (x,i) x.split(","c)).Select(Function (x,i) Tuple.Create(x.Trim,i+1))
Group t By  k=t.Item2 Mod SegmentSplit Into grp=Group
Let ra = grp.Select(Function (gt) gt.Item1).Cast(Of Object).toArray
Select r = dtData.Rows.Add(ra)).CopyToDataTable

grafik

ADDED: incorporated dynamic Segmentation depending on SplitArray Length

8

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