Conversion of JSON data into Data Table

Jstext.json (5.5 KB)
Hello,

I’m trying to covert above JSON data into the data table

Got this error
Deserialize json: Unexpected JSON token when reading DataTable: StartObject. Path ‘[0].headers’, line 3, position 20.

Thanks in advance for help

@J0ska

Hi @harsh.jain

Try with this activity which convert json data to datatable

JSON / JArray Conversion Activities - RPA Component | UiPath Marketplace

Hope it helps you

Regards

Nived N :robot:

Happy Automation :relaxed::relaxed::relaxed:

1 Like

Hi @harsh.jain can u show how should the datatable look like in final

Hi,

—>header

—> line items

This the format for the Header and line item and these both data table I’m writing to the Excel file

I do not think a direct conversion from your JSON into a datatable is possible.
You will have to employ some enhanced technique.
First of all you want to populate two DT.
Next the header data are organized in single column in the JSON while you need it as single row to store in DT.

I will try to find a solution or you could ask someone else.

Cheers

You could use following approach for populating the header:
1/ create header DT (dtHeader) with column names matching JSON fields

2/ create working DT (dt2) with two columns - name, value

3/ get data from JSON into dt2 (objJSON is deserialized JSON file)
dt2 = (From a In objJSON("data")("headers").AsJEnumerable From b In dtHeader.Columns Where CStr(CType(a, JProperty).name).Equals(b.ToString) Let name = CType(a, JProperty).name, value = CType(a, JProperty).value.item("value") Select dt2.LoadDataRow(New Object() { name, value}, False) ).CopyToDataTable

4/ create empty data row (dr)
dr = dtHeader.NewRow

5/ for each row in dt2
dr.item(row("name").ToString) = row("value")

6/ add data row (dr) to dtHeader

Cheers

1 Like