Power Platform Community Forum Thread Details

I’m working with Power Automate, and I receive an HTTP response body that looks like this:

I understand that this represents a binary file (an Excel .xlsx file), but it is encoded with Unicode escapes. How can I transform this data in Power Automate to save it as a usable .xlsx file?

Categories:

Building flows

Hi @RO-13120305-0,

Saving an HTTP response containing Unicode-escaped binary data (like your Excel .xlsx file) into a usable file requires decoding and properly saving the binary content. Here's how you can achieve this in Power Automate:

Step-by-Step Solution

Understand the Input Format:
- The HTTP response body you’re receiving (PK\u0003\u0004\u0014\u0000\b\b\b\u0000...) is a binary file encoded in Unicode escape sequences.
- The \u sequences need to be decoded into binary data before saving the content.

Decode the Unicode Escapes:
Power Automate doesn’t natively handle decoding Unicode escapes, but you can use an Azure Function or an inline expression in Power Automate to decode it.

Option 1: Use Azure Function (Preferred for Complex Data):
- Create an Azure Function that decodes the Unicode escapes and returns the binary file content.
- In your flow:
  1. Send the HTTP response body to the Azure Function.
  2. Retrieve the decoded binary data from the function’s response.
Option 2: Decode with a Premium Connector (Expression-Based):
- Use the Data Operations - Compose action with a decoding script in a service like Power Apps or Azure Logic Apps.

Save the Decoded File:
After decoding the response, use Power Automate’s Create file action to save the binary content as an .xlsx file.
- Example Configuration:
  - File Name: Report.xlsx
  - File Content: Use the decoded output from the previous step.

Simpler Alternative: Direct Handling for Base64 Encoding

If the HTTP response is Base64-encoded (a common format for binary files), the process is simpler:

Use the Base64 to binary expression in Power Automate:

base64ToBinary(outputs('HTTP_response_body'))
Pass this binary output directly to the Create file action.

Debugging and Verification

Verify the Input Format:
- Ensure the HTTP response is truly in Unicode escape format and not Base64 or plain binary.
- Add a Compose action to inspect the response body.
Validate the Decoded Output:
- Before saving the file, confirm that the output of the decoding step is valid binary data (e.g., inspect the output in the flow run history).

Additional Notes

If you cannot use Azure Functions or premium connectors, you may need to preprocess the data outside Power Automate or use a custom API.
Ensure you set the correct MIME type (application/vnd.openxmlformats-officedocument.spreadsheetml.sheet) when handling the file in downstream systems.

Let me know if you need further clarification or help implementing these steps!

Best regards,
@Ninjasabi