JSON to DT

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

Capture

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

HI @mz3bel

Checkout this

Regards
Sudharsan

1 Like

we can deserialize the JSON into a JObject
grafik - 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

2 Likes

Thank you so much for your help.

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 ?

SelectTokens from Newtonsoft:

Using A JSONPath:
JSONPath - XPath for JSON

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

do it afterwards as mentioned:

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

1 Like

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 ?

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

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

58604570688a36ee9e65c034bd48c3471bee7d75

Error

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 :

Capture

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