Orchestrator HTTP request to Data Table

Hello,

I’m trying to get queue item information using Orchestrator HTTP Request (because the Get Queue Item doesn’t have all the information required)
I’m using ““odata/QueueProcessingRecords/UiPathODataSvc.RetrieveQueuesProcessingStatus””
as the endpoint
and I get a JSON response but, I need to turn that into a datatable for it to be easily used.

Does anyone have an example for this?

Thanks,

We have some activities from @sandeep13 which is to convert JSON to Datatable. He will help you for sure.

And you can simply deserialize the JSON and use it right? Any particular reason to convert it to datatable?

1 Like

the deserialization to Datatable didn’t work for me gave me an error.
we have an internal reporting tool and it’d be easier if it was already in a table format.

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

JSON String: “”{"@odata.context":“https://platform.uipath.com/****************/********t/odata/$metadata#Collection(UiPath.Orchestrator.Application.Dto.Queues.QueueProcessingStatusDto)","@odata.count":4,“value”:[{“ItemsToProcess”:0,“ItemsInProgress”:0,“QueueDefinitionId”:229734,“QueueDefinitionName”:“AcmeQueue”,“QueueDefinitionDescription”:null,“QueueDefinitionAcceptAutomaticallyRetry”:true,“QueueDefinitionMaxNumberOfRetries”:1,“QueueDefinitionEnforceUniqueReference”:false,“ProcessingMeanTime”:0.000000,“SuccessfulTransactionsNo”:0,“ApplicationExceptionsNo”:0,“BusinessExceptionsNo”:0,“SuccessfulTransactionsProcessingTime”:0.00,“ApplicationExceptionsProcessingTime”:0.00,“BusinessExceptionsProcessingTime”:0.00,“TotalNumberOfTransactions”:0,“LastProcessed”:“9999-12-31T23:59:59.9999999Z”,“ReleaseName”:null,“SpecificDataJsonSchemaExists”:false,“OutputDataJsonSchemaExists”:false,“AnalyticsDataJsonSchemaExists”:false,“ProcessScheduleId”:null,“Id”:229734},{“ItemsToProcess”:0,“ItemsInProgress”:0,“QueueDefinitionId”:229720,“QueueDefinitionName”:“InHouse_Process4”,“QueueDefinitionDescription”:"Generate Yearly Reports”,“QueueDefinitionAcceptAutomaticallyRetry”:true,“QueueDefinitionMaxNumberOfRetries”:1,“QueueDefinitionEnforceUniqueReference”:false,“ProcessingMeanTime”:128.120000,“SuccessfulTransactionsNo”:10,“ApplicationExceptionsNo”:0,“BusinessExceptionsNo”:0,“SuccessfulTransactionsProcessingTime”:128.12,“ApplicationExceptionsProcessingTime”:0.00,“BusinessExceptionsProcessingTime”:0.00,“TotalNumberOfTransactions”:10,“LastProcessed”:“2019-08-12T02:11:32.0533333Z”,“ReleaseName”:null,“SpecificDataJsonSchemaExists”:false,“OutputDataJsonSchemaExists”:false,“AnalyticsDataJsonSchemaExists”:false,“ProcessScheduleId”:null,“Id”:229720},{“ItemsToProcess”:0,“ItemsInProgress”:0,“QueueDefinitionId”:275724,“QueueDefinitionName”:“KwikSurvey”,“QueueDefinitionDescription”:"",“QueueDefinitionAcceptAutomaticallyRetry”:true,“QueueDefinitionMaxNumberOfRetries”:1,“QueueDefinitionEnforceUniqueReference”:false,“ProcessingMeanTime”:25.670000,“SuccessfulTransactionsNo”:64,“ApplicationExceptionsNo”:0,“BusinessExceptionsNo”:0,“SuccessfulTransactionsProcessingTime”:25.67,“ApplicationExceptionsProcessingTime”:0.00,“BusinessExceptionsProcessingTime”:0.00,“TotalNumberOfTransactions”:64,“LastProcessed”:“2019-11-01T19:35:11.3233333Z”,“ReleaseName”:“Back-Office_-Loan_Enquiries-KwikSurvey-_Performer_Demo Environment”,“SpecificDataJsonSchemaExists”:true,“OutputDataJsonSchemaExists”:true,“AnalyticsDataJsonSchemaExists”:true,“ProcessScheduleId”:76086,“Id”:275724},{“ItemsToProcess”:0,“ItemsInProgress”:0,“QueueDefinitionId”:275726,“QueueDefinitionName”:“TransactionsUiDemo”,“QueueDefinitionDescription”:"",“QueueDefinitionAcceptAutomaticallyRetry”:true,“QueueDefinitionMaxNumberOfRetries”:1,“QueueDefinitionEnforceUniqueReference”:false,“ProcessingMeanTime”:0.000000,“SuccessfulTransactionsNo”:0,“ApplicationExceptionsNo”:0,“BusinessExceptionsNo”:0,“SuccessfulTransactionsProcessingTime”:0.00,“ApplicationExceptionsProcessingTime”:0.00,“BusinessExceptionsProcessingTime”:0.00,“TotalNumberOfTransactions”:0,“LastProcessed”:“9999-12-31T23:59:59.9999999Z”,“ReleaseName”:null,“SpecificDataJsonSchemaExists”:true,“OutputDataJsonSchemaExists”:true,“AnalyticsDataJsonSchemaExists”:true,“ProcessScheduleId”:null,“Id”:275726}]}""

Try pasting the xml after replacing all the double quotes inside with single quotes and then pass it as string surrounding with double quotes

Tried that didn’t work.
FYI, The JSON String is directly from Orchestrator HTTP Request activity’s output
So I’m trying to figure out how to get that activities response using “odata/QueueProcessingRecords/UiPathODataSvc.RetrieveQueuesProcessingStatus”
as the End Point then turning the JSON Response into a data table
Both deserialization JSON and JSON array give me the same error

As the response is directly from server, it will be a perfect JSON. So, just for checking, just try pasting and manually and check where it is missing @alan.man.k

Thanks for the suggestion Hareesh

I’ll try this out

Hi,

The JSON response with odata.metadata throws an error when trying to Deserialize the data into datatable in Deserialize activity.

ODATA is standard protocol and the response JSON is well defined. Hence the conversion to datatable should be straightforward.

Is there any better way to do this ?

Thank You
VJ

Can you post the error you are getting?

Yes, the conversion should be straight forward. Not sure why that is erroring out in your case.

Hi @HareeshMR

I figured out this one that when there is an sub node within Json is an JSON Array, direct conversion to datatable is not possible. It will work with Json response having only key, value pairs.

I have written VB.Net code and used Invoke code activity to build the datatable.

Thank You
VJ