How to check the encoding of a CSV file

Check three things in order. The first three bytes, with head -c 3 file.csv | xxd -p: efbbbf is a UTF-8 BOM. Whether the bytes decode as UTF-8 at all. And what the first column is called after parsing, because a BOM can end up inside that name.

Why check this

A CSV is the format a tester meets when data crosses a company boundary: a partner feed, a bank statement, a bulk import, an export handed to support. Run this check before loading such a file into a system under test, and again on any file your own product exports.

Two failures live here. A file in a single-byte encoding loads without an error and fills the database with the wrong letters. A file with a BOM parses cleanly, and the first column silently arrives under a name nobody matches, so every row imports with an empty id.

Prerequisites

• Node 22, iconv, and a shell with head, tail and xxd.
• Windows PowerShell for the import comparison in steps 3 and 4. The figures there come from Windows PowerShell 5.1 with the ANSI code page set to windows-1251.
• Excel is not installed on the machine that produced this page, so nothing here states what Excel does with a BOM. Check the importer you actually ship against.
• Three fixtures, one correct and two damaged in the two ways that matter.

printf 'id,city,note\n1,Kyiv,Тест\n2,Lviv,Обмін\n' > contacts-utf8.csv
printf '\xef\xbb\xbf' > contacts-bom.csv && cat contacts-utf8.csv >> contacts-bom.csv
iconv -f UTF-8 -t WINDOWS-1251 contacts-utf8.csv > contacts-1251.csv

• csvcheck.mjs, which gates on UTF-8 validity and then parses the file twice, once through each of the two ways Node turns bytes into a string.

import { readFileSync } from 'node:fs';
const file = process.argv[2];
const buf = readFileSync(file);
process.stdout.write(`${file}  ${buf.length} bytes  `);
try {
  new TextDecoder('utf-8', { fatal: true }).decode(buf);
  console.log('valid utf-8: yes');
  report();
} catch {
  console.log('valid utf-8: NO');
}
function parse(text) {
  const lines = text.split(/\r?\n/).filter((l) => l !== '');
  const cols = lines[0].split(',');
  return { cols, rows: lines.slice(1).map((l) => Object.fromEntries(l.split(',').map((v, i) => [cols[i], v]))) };
}
function report() {
  for (const [how, text] of [
    ['fs.readFileSync(f, "utf8") ', readFileSync(file, 'utf8')],
    ['new TextDecoder().decode(b)', new TextDecoder('utf-8').decode(buf)],
  ]) {
    const { cols, rows } = parse(text);
    const c = cols[0];
    console.log(`  ${how}  header[0] "${c}" U+${c.codePointAt(0).toString(16).toUpperCase().padStart(4, '0')}  row.id ${rows[0].id}  row.note ${rows[0].note}`);
  }
}

Steps

1. 1

   Step 1.

   Read the size and the first three bytes of every file in the batch.

   for f in contacts-*.csv; do printf '%-20s %3s bytes  first3 %s\n' "$f" "$(wc -c < $f)" "$(head -c 3 $f | xxd -p)"; done
   

   contacts-1251.csv     38 bytes  first3 69642c
   contacts-bom.csv      50 bytes  first3 efbbbf
   contacts-utf8.csv     47 bytes  first3 69642c

   69642c is the text id,. efbbbf is a BOM, and it is why that file is three bytes longer than the identical one next to it.

2. 2

   Step 2.

   Gate on UTF-8 validity, then parse each file both ways.

   for f in contacts-*.csv; do node csvcheck.mjs "$f"; done
   

   contacts-1251.csv  38 bytes  valid utf-8: NO
   contacts-bom.csv  50 bytes  valid utf-8: yes
   fs.readFileSync(f, "utf8")   header[0] "id" U+FEFF  row.id undefined  row.note Тест
   new TextDecoder().decode(b)  header[0] "id" U+0069  row.id 1  row.note Тест
   contacts-utf8.csv  47 bytes  valid utf-8: yes
   fs.readFileSync(f, "utf8")   header[0] "id" U+0069  row.id 1  row.note Тест
   new TextDecoder().decode(b)  header[0] "id" U+0069  row.id 1  row.note Тест

   One file, one runtime, two answers. row.id undefined is the import bug, and the only visible difference is a header called id instead of id.

3. 3

   Step 3.

   Load the same three files through a CSV importer and read the code points, not the rendered text.

   foreach ($f in 'contacts-utf8.csv','contacts-bom.csv','contacts-1251.csv') { $r = (Import-Csv "./$f")[0]; $codes = (($r.note.ToCharArray() | ForEach-Object { 'U+{0:X4}' -f [int]$_ }) -join ' '); '{0,-18} cols {1,-12} id {2,-3} note {3}' -f $f, (($r.PSObject.Properties.Name) -join ','), $r.id, $codes }
   

   contacts-utf8.csv  cols id,city,note id 1   note U+0422 U+0435 U+0441 U+0442
   contacts-bom.csv   cols id,city,note id 1   note U+0422 U+0435 U+0441 U+0442
   contacts-1251.csv  cols id,city,note id 1   note U+FFFD U+FFFD U+FFFD U+FFFD

   This importer strips the BOM and keeps id usable. The windows-1251 file imported with the right column names, the right row count and four replacement characters where the text was. No error was raised.

4. 4

   Step 4.

   Compare that importer with the plain file read in the same shell.

   'Get-Content default : ' + (Get-Content ./contacts-utf8.csv)[1]; 'Import-Csv default  : ' + (Import-Csv ./contacts-utf8.csv)[0].note; 'Import-Csv -Encoding Default on the 1251 file : ' + (Import-Csv ./contacts-1251.csv -Encoding Default)[0].note
   

   Get-Content default : 1,Kyiv,Тест
   Import-Csv default  : Тест
   Import-Csv -Encoding Default on the 1251 file : Тест

   Two cmdlets in one shell, on one file, with different defaults: Get-Content used the ANSI code page and Import-Csv used UTF-8. Naming the encoding explicitly is the only way to make the two agree.

5. 5

   Step 5.

   Strip the BOM and confirm the header recovered.

   tail -c +4 contacts-bom.csv > no-bom.csv && node csvcheck.mjs no-bom.csv
   

   no-bom.csv  47 bytes  valid utf-8: yes
   fs.readFileSync(f, "utf8")   header[0] "id" U+0069  row.id 1  row.note Тест
   new TextDecoder().decode(b)  header[0] "id" U+0069  row.id 1  row.note Тест

   47 bytes, the same size as the file without a BOM, and both readings now agree.

6. 6

   Step 6.

   Convert the single-byte file and gate it again.

   iconv -f WINDOWS-1251 -t UTF-8 contacts-1251.csv > from-1251.csv && node csvcheck.mjs from-1251.csv
   

   from-1251.csv  47 bytes  valid utf-8: yes
   fs.readFileSync(f, "utf8")   header[0] "id" U+0069  row.id 1  row.note Тест
   new TextDecoder().decode(b)  header[0] "id" U+0069  row.id 1  row.note Тест

   The text is back and the file is byte-identical in size to the original UTF-8 fixture.

How to read the result

| What you see | What it means | What to do | | --- | --- | --- | | first3 efbbbf | The file carries a UTF-8 BOM | Decide whether your importer strips it. If it does not, remove it with tail -c +4. | | valid utf-8: NO | The file is in some single-byte encoding | Convert with iconv once you know which. Do not guess from the column names. | | A header named id | The BOM reached the header row | Every lookup by that column name returns undefined. Fix the file or the reader. | | U+FFFD in a value | Bytes were decoded as UTF-8 and replaced | The data is already lost in that string. Re-import from the original bytes. |

Common mistakes

What to check next

• How to check the encoding of a file: the ruling-out method behind step 2, for files with no parser on top.
• How to check for garbled characters from the wrong encoding: what to do once a column of text is already damaged.
• How to check JSON encoding: the same BOM problem in a format that forbids the ambiguity.
• How to check database collation: where an imported CSV loses characters after it parsed correctly.
• How to test unicode input: the same data entering through a form instead of a file.

FAQ

How do I check if a CSV has a UTF-8 BOM?

head -c 3 file.csv | xxd -p. Output efbbbf means yes. The size difference also shows it: the BOM file in step 1 was 50 bytes against 47 for the identical file without one.

Should a CSV have a BOM or not?

That depends on the importer, and it is a decision to record rather than a default to pick. A BOM tells editors and several Windows readers that the file is UTF-8. It also reaches the header row in readers that do not strip it, which is the bug in step 2.

How do I remove a BOM from a CSV?

tail -c +4 file.csv > fixed.csv drops the three bytes. Confirm afterwards that the file is exactly three bytes shorter and that the first header name starts at U+0069, or whatever your first column begins with.

The file parses and the accents are wrong. Where did the data go?

If you see U+FFFD, the characters are gone and only the original bytes can restore them. If you see readable letters from the wrong alphabet, the bytes are intact and iconv can convert them, as in step 6.

Verified