JSON to DT

[mz3bel (Mohssine HILAL)]

Hello,

I’m trying to figure out how to convert a JSON to a DataTable, using something like this, EXAMPLE : (From p In DictionaryJSON Select DT_JSON.Rows.Add(p.Key.ToString,p.Value.ToString)).ToArray.CopyToDataTable

PS : Data needed in the DT is : “irdrh”, “…BENEFICIAIRE”, “…IBAN”, “…SWIFT”

PS : See attached file

example.json (745 Bytes)

Also, if you have any courses I can follow, or tips on how I can learn to use it, I would be very grateful.

Thank you in advance !

Regards,
Mohssine

[Sudharsan_Ka]

HI @mz3bel

Checkout this

How to create datatable from Json object?

Hi @sonaliaggarwal47 … For your reference…

UiPath: Json to DataTable

How to convert a JSON data To DataTable in UiPath| RPA| rpalearners

Convert Simple JSON To DataTable With Just One Activity In UiPath

Regards
Sudharsan

[ppr (Peter)]

we can deserialize the JSON into a JObject
- myJObject

then we can convert it into a DataTable by:

JArray.FromObject(myJObject.SelectTokens("..field").Select(Function (x) x.First())).ToObject(Of DataTable)

afterwards we can rename / delete columns… as we do need

[mz3bel (Mohssine HILAL)]

@ppr

JArray.FromObject(myJObject.SelectTokens("..field").Select(Function (x) x.First())).ToObject(Of DataTable)

Thank you so much for your help.

[mz3bel (Mohssine HILAL)]

I have a few questions if you don’t mind, how does it work “…field”, is it a word used by the LINQ ? Also, if I want to specify few fields only is it possible ? or I have to clean the DT later ?

[ppr (Peter)]

@mz3bel

“…field”,

SelectTokens from Newtonsoft:

JToken.SelectTokens Method (String)

Using A JSONPath:
JSONPath - XPath for JSON

Kindly note: LINQ is also often only the container for using/interacting with code stamements / API Calls

@mz3bel

if I want to specify few fields only is it possible ? or I have to clean the DT later ?

do it afterwards as mentioned:

@ppr

afterwards we can rename / delete columns… as we do need

e.g. remove data column activity…
myTable.Columns(ColNameOrIndex).ColumnName = “NewName”

[mz3bel (Mohssine HILAL)]

@ppr

field

Thanks for the explanations !

I just noticed something, the filed “irdrh” is missing, using “…Demande” doesn’t get it ? How can I get them both please ?

[ppr (Peter)]

hope you do not mistake the JSON Path "..field" - ..field is the property name as yello marked:

[mz3bel (Mohssine HILAL)]

No, I understood that part, what troubles me, I can’t get the “irdh”, even though I used “Demande” as in the picture below.

I kind need all fields, from “Demande” and “field” on the same DT.

I must be doing something wrong, I get error when I tried to get each apart, since I don’t know how to get them both same time

Error

[mz3bel (Mohssine HILAL)]

I finally found a solution for my problem :

> JArray.FromObject(ObjetJSON("Demandes")("Demande").Select(Function(d) New With {.idrh = d("idrh"), .dt_RIBMODI_IBAN = d("fields")("field")(0)("dt_RIBMODI_IBAN"), .dt_RIBMODI_BENEFICIAIRE = d("fields")("field")(0)("dt_RIBMODI_BENEFICIAIRE"), .dt_RIBMODI_SWIFT = d("fields")("field")(0)("dt_RIBMODI_SWIFT")}).ToList()).ToObject(Of DataTable)()

Result :