mz3bel
(Mohssine HILAL)
March 3, 2023, 11:29am
1
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
ppr
(Peter)
March 3, 2023, 1:48pm
3
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
2 Likes
mz3bel
(Mohssine HILAL)
March 3, 2023, 1:53pm
4
Thank you so much for your help.
mz3bel
(Mohssine HILAL)
March 3, 2023, 1:59pm
5
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)
March 3, 2023, 2:06pm
6
mz3bel:
“…field”,
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
mz3bel
(Mohssine HILAL)
March 3, 2023, 2:13pm
7
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)
March 3, 2023, 2:16pm
8
hope you do not mistake the JSON Path "..field"
- ..field
is the property name as yello marked:
mz3bel
(Mohssine HILAL)
March 3, 2023, 2:54pm
9
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)
March 3, 2023, 5:04pm
10
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 :
system
(system)
Closed
March 6, 2023, 5:04pm
11
This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.