Is it possible deserialize json from excel

Help Studio
, ,
1

Hi All

I export the report from orchestrator queue and i got the excel like this

image
image1223×192 5.7 KB

is any option available to deserialize the json and get the string values with all the details in the report?

2

You can postprocess the result

  • loop over all rows and take the Specific Data JSON - strSPJSON
  • Parse strSPJSON into a JObject
  • then perform the Extraction

We would assume that a common structure is to expect and maybe a postparsing into a datatable will serve

3

Can you explain in details?

4

can you share sample data?

5

{“DynamicProperties”:{“PatientAccountNumber”:“1000.0-1”,“PatientName”:“XXXX, YYYY”,“PlanGroupName”:“Aetna”,“PlanRecordId”:“T001\r”,“TransmissonMode”:“F”,“AddressLine”:null,“City”:null,“State”:null,“PhoneNumber”:null,“ZipCode”:null,“FaxNumber”:“1234563”,“JobQueueTransactionId”:“99cdf826-7213-4215-8d9a-af5b7f2197aa”}}

6

Is this okey or should i share the excel?

7

Hi @divya.17290

To extract data from a JSON representation within an Excel sheet in UiPath, you can follow these general steps:

  1. Read Excel File: Use the “Read Range” activity to read the Excel file into a DataTable. This will give you access to the JSON data stored in the Excel sheet.
  2. Deserialize JSON: Iterate through the DataTable rows, and for each row containing JSON data, use the “Deserialize JSON” activity to convert the JSON string into a JSON object or JObject.
  3. Extract Data: Once you have the JSON object, you can access its properties to extract the required data.

Thanks!

8

Hi @divya.17290

you can use following steps:

  1. Read Range: Read the Excel data into a DataTable (e.g., dtExcelData).

  2. Deserialize JSON: Use the Deserialize JSON activity. Set the InputString property to dtExcelData.Rows(0)(“ColumnName”).ToString(), where “ColumnName” is the name of the column containing the JSON data.

  3. Access Deserialized Data: You can access the deserialized JSON data as a Dictionary or JObject, and then extract values as needed.

9

@divya.17290

Actually you can directly get the data using get queue items api…and then use deserialize json to get each value as needed

Or pass this string to deserialize json and from output jobj("DynamicProperties")("PatientName").ToString will give the name similarly get other details

Cheers