Power Platform Community Forum Thread Details

I have a desktop flow running an SQL query with the "Execute SQL statement" action, and it is generating a Datatable with the resultset, which in turn is put in an output variable "Return".

The preview of the SQL result in a datatable looks good: Headers are there and data is well separated, even if it the data contains the comma separator symbol.

I assign this datatable to an output variable called "Return".

When I trigger this desktop flow from a cloud flow, I get something that looks like CSV data in the JSON return body:

{
 "statusCode": 200,
 "headers": {...},
 "body": {
 "Return": "1234, John Doe, Main Street 1, 12345 Someplace\r\n1235, Jane Doe, Main Street 2, 12345 Someplace\r\n"
 }
}

Screenshot from Power Automate:

This CSV format in the returndata is useless of course, because commas appear both as data and as field separators, so when parsing this data I won't be able to separate the fields properly.

So I am wondering what are the best practices for returning a datatable (or table-like data if you will) from Desktop to Cloud, in a format less prone to errors? Preferably some way that also includes the column headers in the output?

Categories:

Power Automate Desktop

I may be on to something. I tested inserting the data table into a custom object variable:

SET ReturnObject TO {{ }}
SET ReturnObject['Rows'] TO QueryResult
Variables.ConvertCustomObjectToJson CustomObject: ReturnObject Json=> ReturnObjectAsJSON
SET Return TO ReturnObjectAsJSON

This, however, returns the data as JSON code as a string inside the JSON response:

{
 "statusCode": 200,
 "headers": {...},
 "body": {
 "Return": "{\"Rows\":[{\"CustomerNumber\":1234,\"Name\":\"John Doe\",\"Address\":\"Main Street 1, 12345 Someplace\"},{\"CustomerNumber\":1235,\"Name\":\"Jane Doe\",\"Address\":\"Main Street 2, 12345 Someplace\"}]}"
 }
}

JSON is not as effective either, with the column header being repeated on every line it effectively increases the data size by a large percentage, at least for this example.