Is it possible to convert a dictionary to a data table with multiple columns?

Hello! I have a pretty long dictionary<string, object> (around 20 key-value pairs, will grow even bigger later) which I want to output to an excel sheet. Let’s say my dict looks like this: {‘companyID’:‘11111111’, ‘companyName’:‘Comp1’, ‘companyAddress’:‘11-111 Some Avenue City1’, ‘companyRepresentativeName’:‘Some Dude’} with more similar key-value pairs.

My workflow is taking companyID number as input and retrieving all the aformentioned values to a dictionary (named CaseData). With every iteration the values in CaseData are overwritten, the key names are always the same. The output I want to get is an excel file looking roughly like this:

I tried:

  1. Using the Build Data Table activity and manually adding column names identical to the keys in my dictionary - the output is dtCaseData. All the columns are of type object.
  2. Using Build Collection activity to build listCaseData.
  3. Inside a for each loop that goes through my dictionary (with loop object type set as key-value pair) adding item.Value to listCaseData via the Add To Collection activity.
  4. Inside Excel Application Scope using Add Data Row with listCaseData.ToArray in the “ArrayRow” input and dtCaseData in “DataTable” input. Then doing Append Range on dtCaseData.
  1. Using Clear Collection on listCaseData.


The data is being written to the .xlsx file, but it’s very chaotic. The column names aren’t there, some data is written in the wrong columns and for some reason it starts at row 275 and from there it writes to every other row. Is there any way to get the output I want?

  1. convert your dictionary to json using (just assign this to a string variable e.g. dictJson)
    Newtonsoft.Json.JsonConvert.SerializeObject(dict)
    where dict = your dictionary variable

    1a- if you print dictJson, it will show something like
    [{"companyID":"c1","companyName":"name1"},{"companyID":"c2","companyName":"name2"}]

  2. then assign the following statement to a Datatable variable Newtonsoft.Json.JsonConvert.DeserializeObject(Of Datatable)(dictJson)

3 your datatable variable should now look something like this

2 Likes

Thanks for the answer!
The first assign works as expected, however the second one gives an error:
error1

Moreover, your solution assumes I have the data of all the companies, but my dict only holds the data of the company it’s currently checking at any given time, so the dictJson looks like this: {“companyID”:“c1”,“companyName”:“name1”}. If my train of thought is correct then even if the second assign worked it would’ve kept overwriting the data instead of adding them as new rows. Any idea how to modify it?

Try removing the [ and ] from the JSON string. [ and ] delineate an object. { and } delineate an array.

//If my train of thought is correct then even if the second assign worked it would’ve kept overwriting the data instead of adding them as new rows. //

Then you need to have two DTs. One that you convert the current dictionary values into, and another that you merge the first DT into.

Thanks! I did what you suggested (at least I think so…) by assigning Newtonsoft.Json.JsonConvert.SerializeObject(dict.ToArray) in the first step. It works, but writes my data in this format after I use Append Range:
Capture3

Any way I can force it to give me the output described in the question? Or maybe at least something like this, so that I can hopefully transpose it later:
Capture4

I think you shouldn’t convert the dictionary to array. That changes the format. Just let the serializer work directly on the dictionary.

then instead of
assign Newtonsoft.Json.JsonConvert.SerializeObject(dict) to dictJson
you can do this
assign dictJson + Newtonsoft.Json.JsonConvert.SerializeObject(dict).Replace("[","").Replace("]","") to dictJson

this means instead of overwriting the data, it will append it so you get something like this
{“companyID”:“c1”,“companyName”:“name1”},{“companyID”:“c2”,“companyName”:“name2”}

finally you need to make it compatible to convert it to a datatable by adding the square brackets back
dictJson = "[" + dictJson + "]"
result will look like
[{“companyID”:“c1”,“companyName”:“name1”},{“companyID”:“c2”,“companyName”:“name2”}]

Then finally convert to DT

  1. assign the following statement to a Datatable variable Newtonsoft.Json.JsonConvert.DeserializeObject(Of Datatable)(dictJson)

Thanks once again for all the replies! I’m closing the topic.
For anyone with a similar problem: in the end none of the solutions proposed worked for me (though they probably would have if I could keep all my data in the dictionary at the same time). So I was left with building the data table by hand, then creating an array (myArray) and assigning myDict.Values.ToArray to it. Then I just used the Add Data Row activity with myArray as ArrayRow input. Note that this only worked because my dictionary always had the same keys, even if their values could be empty.

1 Like

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