How to Control CSV Encoding in Excel — The UTF-8 BOM Problem Explained

When exporting CSV files from Excel, the presence or absence of the UTF-8 BOM (Byte Order Mark) can cause data import failures. CSV encoding issues are the most common problem when integrating systems. This guide covers practical solutions from Excel export methods to programmatic control of the BOM across different platforms and languages.

Note: Steps in this article are based on Excel for Microsoft 365 on Windows. Behavior may differ on macOS Excel.

What the BOM Is — Three Bytes That Break Imports

A Byte Order Mark (BOM) is a three-byte sequence (0xEF 0xBB 0xBF) that appears at the start of UTF-8 encoded text files. Originally designed to indicate byte order (endianness), the BOM is technically unnecessary for UTF-8. However, Excel automatically adds it when using the "CSV UTF-8" save format, while Linux, Python, and many web systems default to BOM-free UTF-8. This incompatibility is the root cause of import failures and encoding mismatches.

BOM vs. No-BOM at the Byte Level (Comparison Table)

The BOM adds three bytes of overhead and inserts an invisible control character at the file start, which can shift field parsing, particularly affecting the first column of headers.

Real Scenarios Where the BOM Causes Failures

When importing a CSV into cloud services like Google Sheets or Salesforce, a BOM-inclusive file may trigger an error saying "the first column header is not recognized correctly." This occurs because the invisible BOM characters interfere with the parsing of the first field. Similarly, when using custom CSV parsers in Python or Node.js that expect BOM-free UTF-8, a BOM-inclusive file will be read with the first line appearing as fieldname1,fieldname2—the BOM manifests as an invisible character. This breaks key matching against databases and implicit type conversions.

During migrations from legacy encodings like Shift-JIS to UTF-8, intentional BOM addition or removal may be necessary to maintain backward compatibility with older systems.

Saving CSVs as UTF-8 from Excel

Using the "CSV UTF-8" Save Format (BOM Included)

The quickest way to export from Excel is to use the built-in "CSV UTF-8 (Comma Delimited)" format.

Save steps:

This method automatically includes the BOM. The advantage is simplicity; the disadvantage is that downstream systems expecting BOM-free UTF-8 will require preprocessing to remove it.

Exporting with Power Query for Encoding Control

Excel for Microsoft 365's Power Query feature offers more granular control over data preparation, though it does not provide direct BOM removal options. The practical approach is to use Power Query to prepare your data, then use an external tool (text editor or Python script) to strip the BOM afterward.

Power Query excels at handling multiple CSV formats, complex data transformations, and deduplication before final export. In practice, perform data validation and cleaning with Power Query first, then apply encoding adjustments as the final step.

Stripping or Adding the BOM with a Text Editor

For saved CSV files, the most reliable way to control the BOM is using a text editor like Visual Studio Code or Notepad++.

BOM control in text editors (VS Code):

VS Code displays the file encoding in the status bar, making BOM presence visually obvious. Notepad++ offers similar functionality via the Encoding menu. This manual approach works well for a handful of files, but automating with scripts is essential for bulk processing.

Controlling the BOM Programmatically

Writing BOM / No-BOM UTF-8 in Python

The standard way to control UTF-8 encoding in Python is via the encoding parameter.

Writing BOM-free UTF-8:

import csv

data = [
    ['Name', 'Age', 'Department'],
    ['John Smith', '35', 'Sales'],
    ['Jane Doe', '28', 'Marketing'],
]

with open('output.csv', 'w', encoding='utf-8', newline='') as f:
    writer = csv.writer(f)
    writer.writerows(data)

This outputs standard BOM-free UTF-8.

Writing UTF-8 with BOM:

import csv

with open('output_with_bom.csv', 'w', encoding='utf-8-sig', newline='') as f:
    writer = csv.writer(f)
    writer.writerows(data)

Using encoding='utf-8-sig' (UTF-8 with Signature) automatically prepends the three-byte BOM.

Detecting and removing existing BOMs:

def check_bom(filepath):
    with open(filepath, 'rb') as f:
        first_bytes = f.read(3)
        if first_bytes == b'\xef\xbb\xbf':
            print(f"{filepath}: Contains BOM")
        else:
            print(f"{filepath}: No BOM")

def remove_bom(filepath):
    with open(filepath, 'rb') as f:
        content = f.read()
    
    if content.startswith(b'\xef\xbb\xbf'):
        content = content[3:]
    
    with open(filepath, 'wb') as f:
        f.write(content)
    print(f"{filepath}: BOM removed")

Removing the BOM in PowerShell

In Windows environments, PowerShell can batch-remove the BOM from multiple files.

$filePath = "C:\path\to\file.csv"

# Read file with UTF-8 encoding
$content = Get-Content -Path $filePath -Encoding UTF8

# Write back without BOM
$content | Out-File -FilePath $filePath -Encoding UTF8NoBOM

The -Encoding UTF8NoBOM parameter explicitly specifies BOM-free UTF-8. This parameter requires PowerShell 6 (Core) or later. In Windows PowerShell 5.1 (the default on Windows 10/11), -Encoding UTF8 outputs with BOM — there is no built-in way to write BOM-free UTF-8. For multiple files, loop through them:

$csvFiles = Get-ChildItem -Path "C:\data\" -Filter "*.csv"

foreach ($file in $csvFiles) {
    $content = Get-Content -Path $file.FullName -Encoding UTF8
    $content | Out-File -FilePath $file.FullName -Encoding UTF8NoBOM
    Write-Host "$($file.Name): BOM removed"
}

Handling BOM in Node.js and PHP

Node.js (reading and writing BOM-free):

const fs = require('fs');
const csv = require('csv-parser');

fs.createReadStream('input.csv', { encoding: 'utf-8' })
  .pipe(csv())
  .on('data', (row) => {
    console.log(row);
  });

Node.js standard file reading assumes BOM-free UTF-8. To handle BOM-inclusive files correctly, preprocess with Python or PowerShell, or embed this BOM-stripping function:

function stripBOM(content) {
  if (content.charCodeAt(0) === 0xFEFF) {
    return content.slice(1);
  }
  return content;
}

const fileContent = fs.readFileSync('input.csv', 'utf-8');
const cleanContent = stripBOM(fileContent);

PHP (reading BOM-free):

<?php
function removeBOM($content) {
    if (substr($content, 0, 3) === pack('CCC', 0xEF, 0xBB, 0xBF)) {
        return substr($content, 3);
    }
    return $content;
}

$csvContent = file_get_contents('input.csv');
$cleanContent = removeBOM($csvContent);
$lines = explode("\n", $cleanContent);

foreach ($lines as $line) {
    $data = str_getcsv($line);
    print_r($data);
}
?>

When using file_get_contents() in PHP, the BOM persists as the first three bytes. The removeBOM() function explicitly strips it, ensuring reliable processing.

Preventing Encoding Issues Before They Happen

Standardize on "No-BOM UTF-8" Across Your Team

When your organization handles CSV files, establish and document a standard: "All CSV files are BOM-free UTF-8."

Benefits of BOM standardization:

Check the Receiving System's Spec First

Before importing a CSV into external systems or SaaS platforms, explicitly confirm whether they support "BOM-inclusive" or "BOM-free" UTF-8.

Major SaaS platforms' BOM support:

If not documented, send a test CSV sample to verify compatibility. Creating a reference table of system specs speeds operational decision-making.

Automate Encoding Checks in CI

Integrate automated CSV encoding checks into your CI/CD pipeline (GitHub Actions, GitLab CI, etc.) to prevent unintended BOM-inclusive files from being committed.

GitHub Actions with Python:

name: Check CSV Encoding

on: [push, pull_request]

jobs:
  check-encoding:
    runs-on: ubuntu-latest
    steps:
      - uses: actions/checkout@v2
      - name: Check CSV encoding
        run: |
          python3 << 'EOF'
          import os
          import glob
          
          csv_files = glob.glob('data/**/*.csv', recursive=True)
          has_bom_files = []
          
          for filepath in csv_files:
              with open(filepath, 'rb') as f:
                  first_bytes = f.read(3)
                  if first_bytes == b'\xef\xbb\xbf':
                      has_bom_files.append(filepath)
          
          if has_bom_files:
              print("Files with BOM detected:")
              for f in has_bom_files:
                  print(f"  - {f}")
              exit(1)
          else:
              print("All CSV files are BOM-free")
          EOF

This script runs on every push and pull request, marking the build as failed if any BOM-inclusive files are detected.

Using the file command (Linux):

#!/bin/bash
for csv_file in data/*.csv; do
  if file "$csv_file" | grep -q "BOM"; then
    echo "Error: $csv_file has BOM"
    exit 1
  fi
done
echo "All CSV files are BOM-free"

The file command is a standard Unix utility that detects file types and BOM presence. Combined with shell scripts, it integrates easily into pre-commit hooks or CI workflows.

Think of the BOM as a Dialect Between Systems

The UTF-8 BOM issue is difficult to resolve without understanding encoding fundamentals. However, once you recognize the BOM as a three-byte control character that can be deliberately added or removed, nearly all scenarios become manageable. Excel's "CSV UTF-8" format includes the BOM, but building a conversion step to match your downstream system's requirements is essential for stable operations.

Python, PowerShell, text editors, and shell scripts offer multiple encoding control options. The key is establishing a unified standard across your team and automating conversions where needed. As organizations migrate to cloud platforms and integrate multiple systems, the BOM becomes a "dialect" between systems. Confirming the receiving system's specification, implementing automatic conversion, and incorporating CI/CD quality checks will prevent encoding failures and ensure reliable data transfer across your infrastructure.