skip to content

In PHP, what must a fputcsv() export of a membership list do so that Excel shows accented names and splits the columns correctly?

level: middleimportance: should knowfreq 38%

answer

  1. fputcsv never writes a byte-order mark
  2. three bytes before the first row
  3. separator follows the reader's locale
  4. eol argument since 8.1
  5. strip the BOM when importing back

basics

~10 s

Write the UTF-8 byte-order mark "\xEF\xBB\xBF" before the first row, since fputcsv() never adds one, choose the separator the audience's Excel expects, and pass escape '' and, if wanted, eol "\r\n".

solid answer

~40 s

`fputcsv()` writes bytes exactly as given and never adds a **byte-order mark**. Excel tends to read a CSV without one in a legacy regional encoding, so `Zoë` turns into mojibake. Writing `"\xEF\xBB\xBF"` with `fwrite()` before the header row marks the file as UTF-8. Columns are the second problem: Excel splits on the list separator of the user's regional settings, which is a semicolon in many locales, so pick `separator: ';'` or `','` for the audience. Pass `escape: ''` so quotes are only doubled, and `eol: "\r\n"` (added in 8.1) if the consumer wants CRLF line ends. Stream rows to `php://output` so memory stays flat. On the way back in, the BOM ends up glued to the first header cell unless you strip it.

code

php · 17 lines
php
<?php
declare(strict_types=1);

$members = [
    ['name' => 'Zoë Müller', 'email' => '[email protected]', 'joined' => '2025-03-14'],
    ['name' => 'Ana "Nita" Silva', 'email' => '[email protected]', 'joined' => '2024-11-02'],
];

$out = fopen('php://output', 'w');
fwrite($out, "\xEF\xBB\xBF"); // UTF-8 byte-order mark

$opts = ['separator' => ';', 'enclosure' => '"', 'escape' => '', 'eol' => "\r\n"];
fputcsv($out, ['Name', 'Email', 'Joined'], ...$opts);
foreach ($members as $m) {
    fputcsv($out, [$m['name'], $m['email'], $m['joined']], ...$opts);
}
fclose($out);

go deeper

for a junior

Recall that fputcsv() writes a row to a handle and that a UTF-8 BOM has to be written by hand before the first row.

for a middle

Explain why Excel misreads encoding and columns, which fputcsv() arguments control separator, escape and line ends, and how to stream to php://output.

for a senior

Handle the round trip: strip the BOM and detect the separator when the edited file comes back, and keep large exports streaming.

for a principal

Judge when a CSV export is the wrong deliverable for spreadsheet users and a typed spreadsheet format or an API is worth its cost.

## Why a correct CSV can still look wrong in Excel The classic support ticket: a PHP admin page exports the club's membership list, and when the secretary double-clicks the file, names like `Zoë Müller` appear garbled, or every row sits in column A. PHP produced valid CSV in both cases; the file simply did not tell Excel what it needed. Two facts about `fputcsv()` matter: - It writes the bytes of each string **as they are**. If your data is UTF-8, the file is UTF-8, but nothing in it says so. - It never writes a **byte-order mark (BOM)**, the three bytes `EF BB BF` that some programs use to recognise UTF-8. ## Encoding: write the BOM yourself Excel commonly falls back to a legacy regional code page for a CSV without a BOM, which decodes each multi-byte UTF-8 character as two or three wrong characters. Writing the BOM first fixes it: ```php fwrite($out, "\xEF\xBB\xBF"); ``` This assumes the strings really are UTF-8. Converting data from another encoding is a separate step done before `fputcsv()`. ## Columns: the separator When opened by double-click, Excel uses the **list separator** from the user's regional settings. That is a comma in some locales and a semicolon in many others, typically where the comma is the decimal mark. So: - for a known audience, pass the matching `separator` (`';'` or `','`); - for an unknown audience, document the separator, or offer an XLSX export alongside; - remember `fputcsv()` encloses any field containing the separator, so a comma inside a name is safe either way. ## The other fputcsv() arguments | Argument | Default | Recommended for this export | |---|---|---| | `separator` | `","` | the audience's list separator | | `enclosure` | `'"'` | keep the default | | `escape` | `"\\"` (deprecated to rely on since 8.4) | `''`, so quotes are only doubled | | `eol` | `"\n"` (parameter added in 8.1) | `"\r\n"` if the consumer expects CRLF | `fputcsv()` returns the number of bytes written, or `false` on failure. ## Streaming the download For a large list, do not build the CSV in a string. Open `php://output`, a write-only stream into the response body, after sending the download headers, and write rows as you fetch them: 1. send the content-type and attachment headers; 2. `$out = fopen('php://output', 'w');` 3. write the BOM, then the header row, then each member row; 4. `fclose($out);` For a file you attach to an email or store, `php://temp` gives a stream that spills to disk when large; `rewind()` it and read it back with `stream_get_contents()`. ## Reading such a file back When a user edits the export in Excel and uploads it, the file usually starts with the BOM again. `fgetcsv()` has no BOM handling, so the first header cell becomes `"\xEF\xBB\xBFName"` and a lookup of `'Name'` fails. Strip it once: - read the header row; - `if (str_starts_with($header[0], "\xEF\xBB\xBF")) { $header[0] = substr($header[0], 3); }` - continue as normal. Also detect the separator the edited file uses, since Excel saves with its own list separator. ## What PHP controls and what the spreadsheet decides | Concern | Decided by | What you do in PHP | |---|---|---| | Byte encoding of the text | your data | make sure it is UTF-8 before writing | | Recognising UTF-8 | the reader, helped by the BOM | `fwrite()` the BOM first | | Column splitting | the reader's list separator | pick `separator` for the audience | | Quotes inside values | `fputcsv()` | `escape: ''`, so quotes are doubled | | Line ends | `fputcsv()`'s `eol` | `"\r\n"` if required | ## A checklist for the export 1. Send the download headers before any output. 2. Open `php://output` and write the BOM once. 3. Write the header row, then each member row, with the same four arguments. 4. Close the handle; do not echo anything else into the response.

  • Why not just convert the whole list to Windows-1252 instead of adding a BOM?
    A single-byte code page cannot represent every name a membership list holds, so some characters would be lost or replaced. UTF-8 with a BOM keeps every character and still tells Excel how to decode the file.
  • How do you produce the same CSV as a string for an email attachment?
    Open `php://temp` with mode `'r+'`, write the BOM and rows with `fputcsv()`, then `rewind()` the handle and read it with `stream_get_contents()`. `php://temp` keeps small content in memory and moves to a temporary file when it grows.

saying these in an interview costs you the question

  • Assuming fputcsv() adds a UTF-8 byte-order mark automatically
  • Blaming fputcsv() quoting when the real issue is the missing BOM
  • Believing every Excel installation splits CSV on commas
  • Building the whole export in one string before echoing it
  • Forgetting the BOM is glued to the first header cell on re-import