JsonArray to String /Data table

Hi ,

Can any one please help me to convert from JsonArray to String or JsonArray to Datatable.

Hi @prasanna.Ui,
U can try this:
DataTable = JsonConvert.DeserializeObject(Of DataTable)(your JsonArray)

Hi ngotantinh96,

i am getting below error.

your jarray needs converted to a string before attempting to deserialize it

you can use the read text file activity to read the JSON file into a string as a first step

image

and then deserialize that

image

Hi,
I am using serviceNow scope to read all incident and it returning Jarray variable.

I dont have any text file.
How can i convert Jarray to string using read Text file.

You can simply read the json array output as a string:

image

One thing to note there was a bug whereby the Get All Incidents request only outputted to a new version of Newtonsoft Objects but the Deserialise JSON Array only worked with the older version. Therefore the two were incompatible and returned an error. There was a promise to fix this in the next studio release so it may now work as expected.

Hi,
I am doing same process.

1st Step : Copying Jarray to text file by using write text file activity
2nd Step : reading text file to a string variable using read text file activity.
finally trying to desalinizing using desalinize activity.

But i am getting below error.

.

Try using the deserialisze json array activity.

Previously when I developed ServiceNow integration I ended up using Excel Power Query as a workaround due to the bug I explained earlier. It may still be an issue here.

I found that PowerQuery works well with the outputs from ServiceNow as a workaround

Hi ,
Thank you for your help.

I am bit confused , below is my understading can you please correct me if i am wrong.
Form your post i observed that in the excel scope You provided .xlsm file.
Do you want me to create a new .xlsm macro file ?
ExMacro
After that i need to execute query

i dont have knowledge on macro and how to execute it in uipath.
Can you please explain me how to execute this.
It is very useful if you can provide any sample file for the same.

I cannot post my full ServiceNow integration workflow here which uses PowerQuery, however I can provide the section which does this particular step. To replicate you need to do the following, however this video will also help you understand this part:

  1. Create a Macro-Enabled Workbook in Excel
  2. Add the Macro syntax I posted above. Save the workbook.
  3. On the Excel ribbon, go to Data->New Query->From Other Sources->Blank Query
  4. Click on ‘Advanced Editor’
  5. Paste the following, ensuring you add your json Incidents source file in the section provided:

let
Source = Json.Document(File.Contents(“YourFile.json”)),
#“Converted to Table” = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error)
in
#“Converted to Table”

  1. Save the query. You will want to play around with this to get the format you need, expand on the ‘records’ to reveal the columns for example.

image

You should see your Incidents now set out as a regular table in Excel. Once happy, save the workbook.

The way this works now is you have your Excel Workbook as a middle man to help transform your JSON data into the format you need. It will always reference the Incidents json file output by the ServiceNow activity. You just need to refresh the query in the excel to make it dynamic and provide the most recent data from your JSON. you can do this by executing the refresh macros you added to your workbook and then reading the data from the file.

ServiceNow_ReadExcelJSON.xaml (7.3 KB)

ExMacro