Parsing JSON file into individual rows

Help Studio
,
1

I am using an API interface to grab the following JSON file. Problem is when i Deserialize and insert into a Data Table, UI path is putting each element into one cell. i need each element inserted into their own row. I can use a Comma as the separator but i cant figure out the logic to accomplish this.

Any advise how to get each https//test-pcard… address into the new row using the comma as the seperator?

JSON Deserilized
image

Below is the JSON file inserted in the data table, however its in once cell

image
image1038×207 6.06 KB

2

one of many options:

we assume: dt1 - an empty datatable with 1 column - URL

  • Deserialize JSON Activity - myJObject
  • Assign Activity
    dt1 =
    myJObject("url").Select(Function (x) dt1.Rows.Add(new Object(){x.toString})).CopyToDataTable
3

Hey @Waterfowl_Waterfowl_hunte ,

Are you directly setting the Type argument as System.Data.DataTable as shown below

image
image1342×510 32.2 KB

If not,try this method once

4

I think i have the assign logic as you suggested. Please know that i am self taught and not a Pro VB coder

image
image1562×650 42.4 KB

5

When i change from object to System.Data.Datatable i receive the following

image
image728×113 5.84 KB

6

As an alternate we can also do:

grafik
grafik748×114 14.6 KB

7

change typeargument from deserialize JSON to JOBject

8

Convert it in variables panel to

9

This is what i received. ill be honest, i couldnt even begain to break down what you suggested.

image
image1277×395 20.5 KB

if it helps i can ask the JSON developer to remove the URL section of the JSON file.

I can ask him to change it

FROM
image

TO something like this.

{ [https://test-pcard.ccs.nrl.navy.mil/api/requisition/N0017323195300D],
[https://test-pcard.ccs.nrl.navy.mil/api/requisition/N001732319ABC12] }

10

Hi Quenton, I think your message was cut off.

11

Have you tried changing the datatype of JsonGrabIDs variable type into Datatable type
and also did you change the Type argment of Deserializer JSON activity to System.Data.Datable?

12

we do have a JSON string e.g. strJSON
grafik

we deserialize it
grafik
grafik

then we use an assign activity
grafik
dtData | Datatype: DataTable =

JArray.FromObject(myJObject("urls").Select(Function (x) JObject.Parse("{""url"":"""& x.toString &"""}"))).ToObject(Of DataTable)

ensure:
grafik

Thats all when doing this compact approach

13

Hey @Waterfowl_Waterfowl_hunte ,

I wanted to say that as you have changed the json Deserialize to DataTable, in variables panel change type of JsonGrabIDs to System.Data.DataTable

Another method would be to use regex, where:-

  1. (?<=[).*(?=,) gives https://test-pcard.ccs.nrl.navy.mil/api/requisition/N0017323195300D

  2. (?<=,).*(?=]) gives https://test-pcard.ccs.nrl.navy.mil/api/requisition/N001732319ABC12.

So if the datatable method doesn’t work you can do the below steps,

a) Create an empty datatable with one column named url and assign it a variable in the properties panel.

b) Use assign activity with left side as url1 and right side as System.Text.RegularExpressions.Regex.Match(JsonGrabIDs.ToString, “(?<=[).*(?=,)”).Value.Tostring

c) Then use add data row activity and in first field of activity set it as {url1} and datatable as the one you created

d) Use assign activity again with left side as url2 and right side as System.Text.RegularExpressions.Regex.Match(JsonGrabIDs.ToString, “(?<=,).*(?=])”).Value.Tostring

e) Then use add data row activity and in first field of activity set it as {url2} and datatable as the one you created

14

It looks like this worked!!! Thank you my friend. I owe you a beer

15

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