Creating a CSV File with Byte Order Mark (BOM) in Power Automate

In Power Automate, creating a CSV file with a Byte Order Mark (BOM) can help ensure compatibility with applications that require UTF-8 encoding. This guide will walk you through setting up a flow that creates a CSV file with BOM in OneDrive.

Step-by-Step Guide

1.Create an Instant Flow

• Start by creating an Instant Cloud Flow in Power Automate.
• Click New flow > Instant cloud flow > Enter Flow name
• Select Manually trigger a flow > Click Create

2.Initialize CSV Data Variable with Sample Data

• Add an Initialize Variable action.
• Set Name to varCSVData.
• Set Type to Array.
• In the Value field, enter your sample data directly, like this:
• Optionally rename the action : Initialize varCSVData

[
  {
    "Name": "John Doe",
    "Email": "john@example.com",
    "Country": "USA"
  },
  {
    "Name": "Jane Smith",
    "Email": "jane@example.com",
    "Country": "Canada"
  }
]

3.Create CSV Table with Array

• Use the Create CSV Table action and set From to varCSVData.
• Set Columns to Automatic to automatically include all fields as columns.

4.Add BOM to CSV Data

• Add an Initialize Variable action for the final CSV content:
• Name: varCSVwithBOM
• Type: String
• Value: concat(base64ToString('77u/'), body('Create_CSV_table'))
• Optionally rename the action : Initialize varCSVwithBOM

 The base64ToString('77u/') prefix adds the BOM

5.Create the File in OneDrive

• Use the Create File action:
• Folder Path: Choose your location (e.g., Root).
• File Name: SampleWithBOM.csv.
• File Content: Set to varCSVwithBOM.

Final Steps and Testing

• Save and run the flow to verify that the CSV file is created in OneDrive with BOM.

• Once created, download the file and open it with a compatible editor to confirm the BOM is present.

Advantages of Adding a BOM in CSV Files

1.Improved Compatibility:

The BOM prefix ensures the CSV file is correctly interpreted as UTF-8, preventing encoding issues when opened in applications like Microsoft Excel. Without a BOM, special characters or non-ASCII text can appear garbled or incorrect.

2. Reliable Multi-language Support:

CSV files with BOM can handle data in multiple languages, supporting characters from non-Latin alphabets like Chinese, Japanese, Arabic, and Cyrillic. This is particularly useful in international settings or global applications.

3. Prevention of Data Loss or Corruption:

With the BOM, data corruption issues from encoding errors are minimized. This can be essential in workflows where CSV files are exchanged between different systems, some of which might not natively handle UTF-8 without BOM

Conclusion:

By creating CSV files with BOM in Power Automate, you can ensure data integrity and compatibility across different applications and languages, making your workflows smoother and more reliable.