You’re offline. This is a read only version of the page.
12
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:
Share
Report{
"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?
I may be on to something. I tested inserting the data table into a custom object variable:
All responses (
Answers (
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.
