How to extract the data from Json to Excel datatable

excel
json

#1

Hi,

I am trying to download Json file with HTTP request. The next step for me is extracting data from the Json file to Excel data table. Please advice how could I extract/convert it into Excel format. please see the attached XAML file.

Thank you.
invoice extraction.zip (174.9 KB)


#2

Hi @Joeysugar You can use “Deserialize json” activity to convert the json to Datatable and then push the datatable to Excel file using “Write Range” activity. In order to convert the json (output of http request) in datatable, you can specify the “TypeArgument” of “Deserialize json” activity as “System.Data.Datatable”. Once you change the type, you can get the output as datatable. Please see the screenshot attached.


#3

Hi Priya,

I changed the type argument data table, but robot has thrown an exception as follow-

Source: Deserialize json

Message: Unexpected JSON token when reading DataTable. Expected StartArray, got StartObject. Path ‘’, line 1, position 1.

Exception Type: JsonSerializationException


#4

Hi @Joeysugar
SImply you make a call Newtonsoft.json library.

For example
In the Assign activity
DT1 = Newtonsoft.Json.JsonConvert.DeserializeObject(json).Tables[“Table1”]

See also
https://www.newtonsoft.com/json/help/html/DeserializeDataSet.htm


#5

@Joeysugar It seems that the json string which you are getting as output from http request is not in the correct json format. Please validate the json using below URL. https://jsonlint.com/