How to extract the data from Json to Excel datatable

Help
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.

convert%20json%20to%20Datatable
convert%20json%20to%20Datatable2066×1540 117 KB

Json to Datatable convertion
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

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/

6

@Joeysugar did you get anything else because I am also getting error message like
Deserialize json : Unexpected JSON token when reading DataTable. Expected StartArray, got StartObject. Path ‘’, line 1, position 1.

7

I’m having the same issue and would like to know if anyone found a solution.
Thanks!