Creating UTF‑8 CSV Files in PHP That Open Correctly in Excel｜Handling Garbled Text, Double Quotes, Leading Zeros, and Line Breaks

Creating CSV Files in PHP That Open Correctly in Excel｜Handling Garbled Text, Double Quotes, Leading Zeros, and Line Breaks

When generating CSV files in PHP, you may encounter issues such as:
Garbled characters when opening the file directly in Excel,
Half‑width double quotes not being handled correctly inside cells,
Numbers starting with 0 losing their leading zeros,
Line breaks inside cells not being processed properly.

This article provides a detailed explanation—with complete source code—on how to solve all of these problems effectively.
A must‑read for anyone who wants to generate CSV files that display correctly in Excel!

Creating UTF‑8 CSV Files That Do Not Break When Opened Directly in Excel

When you generate a UTF‑8 CSV file in PHP and double‑click it to open in Excel, the content often appears garbled. Adding a UTF‑8 BOM to the beginning of the CSV file prevents this issue and allows Excel to read it correctly.

UTF‑8 BOM

pack('CCC', 0xEF, 0xBB, 0xBF)

UTF‑16 Little Endian BOM

pack('CC', 0xFF, 0xFE)

UTF‑16 Big Endian BOM

pack('CC', 0xFE, 0xFF)

※ In the pack function, the format character "C" means an unsigned char (1 byte). Therefore, "CCC" means three unsigned char values.

<?php
mb_internal_encoding("UTF-8");

// Add a UTF-8 BOM at the beginning of the CSV file
$csv = pack('CCC', 0xEF, 0xBB, 0xBF).  // UTF-8 BOM
  mb_convert_encoding(            // Converts to UTF-8 just in case; unnecessary if already UTF-8
    '"Item","Value1","Value2"'."\r\n".
    '"Item1",1,2'."\r\n".
    '"Item2",2,2'."\r\n".
    '"Item3",3,5'."\r\n",
    "UTF-8"
  );

// Get file size
$filesize = strlen($csv);

// If the filename contains multibyte characters, convert it to Shift-JIS for Internet Explorer compatibility.
$filename = mb_convert_encoding("CSV_Filename.csv", "SJIS", "UTF-8");

header('Content-Type: application/octet-stream');
header('Content-Length: '.$filesize);
header('Content-Disposition: attachment; filename='.$filename);

echo $csv;

How to Insert Double Quotes Inside CSV Cells

To include half‑width double quotes (") inside a CSV cell, convert each double quote into two consecutive double quotes (""). This is the standard CSV escaping rule.

<?php
mb_internal_encoding("UTF-8");

// Add a UTF-8 BOM at the beginning of the CSV file
$csv = pack('CCC', 0xEF, 0xBB, 0xBF).  // UTF-8 BOM
  mb_convert_encoding(                  // Converts to UTF-8 just in case
    '"Item","Value1","Value2"'."\r\n".
    '"""Item"" Value1",1,2'."\r\n",
    "UTF-8"
  );

// Get file size
$filesize = strlen($csv);

// If the filename contains multibyte characters, convert it to Shift-JIS for Internet Explorer compatibility.
$filename = mb_convert_encoding("CSV_Filename.csv", "SJIS", "UTF-8");

header('Content-Type: application/octet-stream');
header('Content-Length: '.$filesize);
header('Content-Disposition: attachment; filename='.$filename);

echo $csv;

Preventing Leading Zeros from Being Removed in CSV Cells

To ensure that numbers beginning with 0 are not truncated by Excel, wrap the value in the format ="000000". Excel interprets this as a string and preserves the leading zeros.

<?php
mb_internal_encoding("UTF-8");

// Add a UTF-8 BOM at the beginning of the CSV file
$csv = pack('CCC', 0xEF, 0xBB, 0xBF).  // UTF-8 BOM
  mb_convert_encoding(                  // Converts to UTF-8 just in case
    '"Item","Value1","Value2"'."\r\n".
    '="000001",1,2'."\r\n".
    '="000002",2,4'."\r\n",
    "UTF-8"
  );

// Get file size
$filesize = strlen($csv);

// If the filename contains multibyte characters, convert it to Shift-JIS for Internet Explorer compatibility.
$filename = mb_convert_encoding("CSV_Filename.csv", "SJIS", "UTF-8");

header('Content-Type: application/octet-stream');
header('Content-Length: '.$filesize);
header('Content-Disposition: attachment; filename='.$filename);

echo $csv;

Line Breaks Inside a CSV Cell

To include line breaks inside a CSV cell, wrap the entire value in double quotes: "string that contains line breaks". Excel will correctly interpret the line breaks within the quoted cell.

<?php
mb_internal_encoding("UTF-8");

// Add a UTF-8 BOM at the beginning of the CSV file
$csv = pack('CCC',0xEF,0xBB,0xBF).  // UTF-8 BOM
  mb_convert_encoding(              // Converts to UTF-8 just in case
    '"Item","Value1","Value2"'."\r\n".
    '"ABCDE'."\r\n".'FGHIJ",1,2'."\r\n",
    "UTF-8"
  );

// Get file size
$filesize = strlen($csv);

// If the filename contains multibyte characters, convert it to Shift-JIS for Internet Explorer compatibility.
$filename = mb_convert_encoding("CSV_Filename.csv", "SJIS", "UTF-8");

header('Content-Type: application/octet-stream');
header('Content-Length: '.$filesize);
header('Content-Disposition: attachment; filename='.$filename);

echo $csv;

PHP | Samples