Data Table to JSON (Array)

[prasath17 (Guru)]

Hi I would like to convert the the data table(excel file) to JSON. Some of my columns are in array format as shown below(Column W and Y), which is not converting in the array format.

I have tried both Seralize and Datatable to JSON activity , but both of them not yielding the correct results.

Here is my JSON file: DocumentProcessing_Act.json (2.2 KB)

Any suggestion please?

[loginerror (Maciej Kuźmicz)]

Hi @prasath17

It would seem like it can’t be done automatically with the available tools. Rather than that, you will need to create an empty shell for your JSON and then loop over your data and add it.

If the source Excel file looks exactly as the one above, then you could leave what you have already to generate the initial JSON, which contains three array elements, and then adapt it.

To adapt it, you could do this:

1. Loop over the JArray elements and for each occurrence of the these values , add them to the nested JArray of the first JArray element
   This can be done with an Invoke Method activity and an Add method
2. Either transfer the first JArray element to a new separate JArray or remove the other two main JArray elements.

I hope it makes sense to you. Feel free to ask if it is not clear enough.

[prasath17 (Guru)]

Thanks @loginerror… I will try to follow your approach…if I get stuck, I will respond back here…if not, I will mark your response as “Solution”

[prasath17 (Guru)]

hi @loginerror… I have tried something like this and i am not able to proceed after this… Main.xaml (14.6 KB)

Could you please assist?

[loginerror (Maciej Kuźmicz)]

I was thinking more like that (I had to adjust my original idea a bit though):
prasath17.zip (4.2 KB)

[prasath17 (Guru)]

Thank you so much @loginerror…