Convert json to Datatable

{

"Name": "Karan",
"Adddress": "Kolkata",
"Job_Description": "Software Developer",
"request": {
	"createdBy": "unknown",
	"comment": "Software",
	"Closure":[  
        {  
           "type":"AB",
           "country":[  
              "India",
              "Pakistan"
           ]
        },
		{  
           "type":"CD",
           "country":[  
              "India",
              "Pakistan"
           ]
        }
     ]
}

}

I want to convert this json to datatable. I am getting error when i am trying to write the deserialized json in text. Error–>
“Accessed JArray values with invalid key value: “Name”. Int32 array index expected.”
Please help me to get the solution

2 Likes

I was able to deserialize the exact same json data you posted. Is there any specific thing that is throwing an error for you?. I was able to retrieve the data as well.

TestJSON.zip (2.1 KB)

Thanks,
Rammohan B.

1 Like

So I’m having the same error come up for me.
Here is the json file: [{“socialCode”:“791”,“accountNumber”:“00000xxxx”,“accountEodBalance”:“2351.86”,“accountOwnerName”:"test- ROTH - ",“ettRepName”:“Test Waters”,“ettRepInitials”:“D8L”,“ettRepExtension”:“xxxxx”},{“socialCode”:“791”,“accountNumber”:“xxxx”,“accountEodBalance”:“3735.85”,“accountOwnerName”:"Test - ROTH - ",“ettRepName”:“Test Waters”,“ettRepInitials”:“D8L”,“ettRepExtension”:“xxxxx”}]

And I’m just wanting to parse it out.
image

Error: For each - Data Results : Accessed JArray values with invalid key value: “results”. Int32 array index expected.

Also, I’ve done this and get same error:

Same error: Assign : Accessed JArray values with invalid key value: “socialCode”. Int32 array index expected.

Do you have any suggestions? Thanks!

Hi,

There is a simpler way to Deserialize JSON to Datable using the Deserialize JSON Activity.
The trick is to specify the Type argument in the property as bellow and A datatable variable as output.

image

The only requirement is to have a json representing tabular data, which is your case but was not the case of the OP.

I posted (see link bellow) quite a while ago another example to “navigate” through the jobject to reach the tabular data (once again you do not need it but this can help someone)

I am attaching example for your case

test.json (377 Bytes)
JsonToDT.xaml (5.7 KB)

Cheers

10 Likes

Thanks I will try this tomorrow morning

Thanks this worked. The only thing now is they have changed it and now i have to use a Token. So that is the hurdle I’m working on now.

So when you use Output Data table i’m going to try to write it to a csv since the output looks to be in that format.

Thanks so much for your assistance.

you code is not working

Can you explain the error that you seeing? Its working fine at my end.

Thanks,
Rammohan B.

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

Could you please help?

Hi @harsh.jain ,
try this
----JsonConvert.DeserializeObject(Of DataTable)(your json variable)

1 Like

How to get this into data table
[
{
“id”: 15,
“io-number”: “IND001000054”
},
{
“id”: 16,
“io-number”: “IND001000054”
},
{
“id”: 17,
“io-number”: “IND001000054”
},
{
“id”: 18,
“io-number”: “IND001000054”
},
{
“id”: 20,
“io-number”: “IND001000054”
},
{
“id”: 21,
“io-number”: “IND001000054”
},
{
“id”: 22,
“io-number”: “IND001000054”
},
{
“id”: 23,
“io-number”: “IND001000054”
},
{
“id”: 24,
“io-number”: “IND001000054”
},
{
“id”: 25,
“io-number”: “IND001000054”
},
{
“id”: 26,
“io-number”: “IND001000054”
},
{
“id”: 27,
“io-number”: “IND001000054”
},
{
“id”: 28,
“io-number”: “IND001000054”
},
{
“id”: 29,
“io-number”: “IND001000054”
},
{
“id”: 30,
“io-number”: “IND001000054”
},
{
“id”: 31,
“io-number”: “IND001000054”
},
{
“id”: 32,
“io-number”: “IND001000054”
},
{
“id”: 33,
“io-number”: “IND001000054”
},
{
“id”: 34,
“io-number”: “IND001000054”
},
{
“id”: 35,
“io-number”: “IND001000054”
},
{
“id”: 36,
“io-number”: “IND001000054”
},
{
“id”: 37,
“io-number”: “IND001000054”
},
{
“id”: 38,
“io-number”: “IND001000054”
},
{
“id”: 39,
“io-number”: “IND001000054”
},
{
“id”: 40,
“io-number”: “IND001000054”
},
{
“id”: 41,
“io-number”: “IND001000054”
},
{
“id”: 42,
“io-number”: “IND001000054”
},
{
“id”: 43,
“io-number”: “IND001000054”
},
{
“id”: 44,
“io-number”: “IND001000054”
},
{
“id”: 45,
“io-number”: “IND001000054”
},
{
“id”: 46,
“io-number”: “IND001000054”
},
{
“id”: 47,
“io-number”: “IND001000054”
},
{
“id”: 48,
“io-number”: “IND001000054”
},
{
“id”: 49,
“io-number”: “IND001000054”
},
{
“id”: 50,
“io-number”: “US032000050”
},
{
“id”: 51,
“io-number”: “IND001000054”
},
{
“id”: 52,
“io-number”: “IND001000054”
},
{
“id”: 53,
“io-number”: “US032000053”
},
{
“id”: 54,
“io-number”: “IND001000054”
}
]

1.Deserialize the Array
2.Assign a datatable to JsonConvert.DeserializeObject(of DataTable)(YourJArray.ToString)
3.print the datatable

Hi @Florent_Salendres,
Could you please help me understand, how can store all the values from json to DataTable variable.

{
“itemsPerPage” : 1,
“startIndex” : 1,
“Resources” : [ {
“id” : “D3648B0BD6A8D0F2E050230ACA3CE482”,
“meta” : {
“resourceType” : “User”,
“created” : “2021-12-29 02:06:12.000”,
“lastModified” : “2022-08-05 08:50:09.000”
},
“schemas” : [ “urn:scim:schemas:core:2.0:User”, “urn:scim:schemas:extension:fa:2.0:faUser” ],
“userName” : “Aarona.Rimgailaite@gmail.com”,
“name” : {
“familyName” : “Rimgailaite”,
“givenName” : “Aarona”
},
“displayName” : “Aarona Rimgailaite”,
“preferredLanguage” : “en”,
“active” : false,
“emails” : [ {
“primary” : true,
“type” : “W”,
“value” : “Aarona.Rimgailaite@gmail.com
} ],
“roles” : [ {
“id” : “9F17454AF9CD455B98D0EAE5A025B010”,
“displayName” : “Misc View”,
“value” : “MiscView”
}, {
“id” : “47AA2F0066384F5785C028B2423F39AA”,
“displayName” : “Product View and Approve”,
“description” : "Custom Role for uses with view only access on item but some functions on change orders including but not limited to approvals. ",
“value” : “ProductViewandapprove”
}, {
“id” : “6C94C80883FA41EC83F8F5EE60AA8F1A”,
“displayName” : “Reporting User”,
“value” : “ReportingUser”
} ],
“urn:scim:schemas:extension:fa:2.0:faUser” : {
“workerInformation” : {
“personNumber” : “387”,
“manager” : “null”,
“department” : “null”
},
“userCategory” : “DEFAULT”,
“accountType” : “Person”
}
} ]
}

That means the JSON file is not in pure format. Also which one you have tried Deserialize activity or deserialize command?

There is a command named Select Token. Make use of it. Please reply this if you need more details.

try this way.

you can ask chatgpt by copying this code and it will give you instruction as below:

To convert the provided JSON data into a DataTable in UiPath, you can follow these steps:

  1. Use the Deserialize JSON activity:

    • Drag and drop the “Deserialize JSON” activity from the Activities panel onto your workflow.
    • In the Properties pane, set the following:
      • Input → YourJsonString (a string variable containing the JSON data).
      • Result → Create a new variable of type JObject (e.g., named jsonObject).
  2. Build the DataTable structure:

    • Add a new Assign activity after the Deserialize JSON activity.
    • Set the left side of the Assign activity to a new variable of type DataTable (e.g., named dataTable).
    • Set the right side of the Assign activity to the following expression:
      New System.Data.DataTable()
      
  3. Extract the data from the JSON object and populate the DataTable:

    • Add a For Each activity after the Assign activity.
    • Set the TypeArgument of the For Each activity to Newtonsoft.Json.Linq.JToken.
    • Set the Values property of the For Each activity to the following expression:
      jsonObject("Resources").Children()
      
    • Add a For Each activity inside the outer For Each activity.
    • Set the TypeArgument of this inner For Each activity to Newtonsoft.Json.Linq.JProperty.
    • Set the Values property of the inner For Each activity to the following expression:
      item.Children()
      
    • Inside the inner For Each activity, add a Switch activity.
    • Set the Expression property of the Switch activity to the following expression:
      item.Name.ToString()
      
    • Add cases for each property you want to extract, such as “userName”, “displayName”, etc.
      • For each case, add an Assign activity to extract the value and add it to the DataTable.
        • Example: To extract the “userName” property, you can use the following Assign activity:
          dataTable.Rows.Add(item.Value.ToString())
          
    • Add a Break activity at the end of each case to exit the inner For Each activity.
  4. Use the resulting DataTable:

    • You can now use the populated DataTable in your UiPath workflow for further processing or output.

Remember to adjust the variable names and expressions according to your specific scenario.