Problem converting JSON Array to DataTable to then append in an Excel file

[alex87 (Alex)]

Hello,

I call an API every 4 hours that returns me an update of some data by project.
I need to put the new values bellow the existing ones in an Excel file (append).

Here is an example of the API output:
example-json-data.json (517 Octets)

example-json-data

{
    "extrapolationLevel": 1,
    "columnNames": [
      "Project",
      "Satisfied",
      "Tolerating",
      "Frustrated"
    ],
    "values": [
      [
        "Project name 1",
        1722,
        1735,
        0
      ],
      [
        "Project name 2",
        8929,
        1827,
        10
      ],
      [
        "Project name 3",
        5243,
        229,
        7
      ],
      [
        "Project name 4",
        1625,
        2025,
        18
      ]
    ]
  }

I use StudioX 2023.10.7

I get my data from the API and I’m able to put the value node in a JSON Array:

From there, I convert my JSON array to a DataTable with the following expression:

The result of the dataTableValues.RowCount is 12 (I don’t know why 12 and not 16 as I have 4 group of 4 values in the array “values”…)

I also tried the expression JSonArrayValues.ToObject(Of DataTable)() with the same following result.

The next step is to open an Excel file and write the datatable to a sheet in an Append mode.

The problem is that the Excel file isn’t completed at all and beside my headers, the cells remains empty.

The variables I use and that can be seen in the screenshots are:

• dataTableValues: Data Type = System.Data.DataTable, Default Value = new DataTable()
• JSonArrayValues: Data Type = Newtonsoft.Json.Linq.JArray, no default value

The process doesn’t look complicated but I’m surely missing a step to set the DataTable correctly so it reflects in the Excel file .

Thank you

[J0ska]

Your “values” is JSON array of arrays.
You could “flatten” it into datatable using below LINQ expression

dt = (from a in objJSON("values").AsEnumerable 
from b in a.AsEnumerable 
select row = {b}
select dt.rows.add(row)).CopyToDataTable

[alex87 (Alex)]

Where do you set the code in StudioX?

Beside some small code like in the Set Variable Value > Value to save Advanced editor, it doesn’t look like StudioX allow code.

Good call for the array of arrays. As I don’t think StudioX allows me to do some real code, I’ll try to populate the DataTable with 2 foreach loops and see if it’s better.

Edit2: I’m migrating to Studio so I will have more latitude.

[alex87 (Alex)]

Directly from the JSON Array I got from the API I was able in Studio to put it in a DataTable and then in Excel without issues: