cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
torkil
Regular Visitor

Formating and returning a datatable from Desktop to Cloud

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.

torkil_0-1647428408906.png

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:

torkil_1-1647428711747.png

 

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?

1 REPLY 1
torkil
Regular Visitor

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.

Helpful resources

Announcements
Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

New Ideas Forum MPA.jpg

A new place to submit your Ideas for Power Automate

Announcing a new way to share your feedback with the Power Automate Team.

MPA Virtual Workshop Carousel 768x460.png

Register for a Free Workshop

Learn to digitize and optimize business processes and connect all your applications to share data in real time.

365 EduCon 768x460.png

Microsoft 365 EduCon

Join us for two optional days of workshops and a 3-day conference, you can choose from over 130 sessions in multiple tracks and 25 workshops.

Top Solution Authors
Top Kudoed Authors
Users online (4,804)