How to test csv export encoding in a spreadsheet

Compare what the bytes are with what the reader shows. Run node csvencoding.mjs report export.csv, then open the same file with sh calc.sh show export.csv. Here a correct windows-1252 export printed caf plus U+FFFD in LibreOffice Calc 24.2.3.2, because nothing in the file names its encoding.

Why check this

Run this on the first export of a release, and again when a ticket reports wrong letters in a file your product wrote.

A CSV carries no field that names its encoding. The bytes are one fact and the reader's assumption is a second, and only the first is in the file. A tester who checks the bytes and stops passes an export that opens wrong for everyone.

The failure this prevents is an export whose accented names reach the customer as replacement characters. Step 9 measures that file passing a UTF-8 validity test with the letters already gone.

Prerequisites

• Python 3.13 for the builder. Every non-ASCII character comes from chr(), because one typed into a shell argument can arrive re-encoded.
• Node 22 for the reader and the report.
• LibreOffice headless, on a Ukrainian UI locale, LCID 1058, which is what sdnum="1058;" reports below.
• Excel is not installed on the machine that produced this page, so nothing here describes it. Run these steps against the reader you ship.
• -env:UserInstallation gives each conversion its own profile. Keep that path short, or the build exits 127 and converts nothing.

make.py writes one table in three encodings. Its four values break at four points.

"""Write one CSV in three encodings. Every non-ASCII character comes from chr()."""
import codecs

LF = chr(10)
ACUTE = chr(0x00E9)   # LATIN SMALL LETTER E WITH ACUTE
GRIN = chr(0x1F600)   # GRINNING FACE, outside the BMP
NBSP = chr(0x00A0)    # NO-BREAK SPACE
RSQUO = chr(0x2019)   # RIGHT SINGLE QUOTATION MARK

rows = [
    ('kind', 'value'),
    ('acute', 'caf' + ACUTE),
    ('emoji', 'ok' + GRIN),
    ('nbsp', '1' + NBSP + '000'),
    ('rsquo', 'it' + RSQUO + 's'),
]
text = LF.join(','.join(r) for r in rows) + LF

open('export-utf8.csv', 'wb').write(text.encode('utf-8'))
open('export-utf8-bom.csv', 'wb').write(codecs.BOM_UTF8 + text.encode('utf-8'))

try:
    text.encode('cp1252')
    print('cp1252 strict: encoded')
except UnicodeEncodeError as e:
    bad = text[e.start]
    print('cp1252 strict: ' + e.__class__.__name__ + ' at position ' + str(e.start)
          + ', U+' + format(ord(bad), '04X'))

open('export-1252.csv', 'wb').write(text.encode('cp1252', errors='replace'))

for name in ('export-utf8.csv', 'export-utf8-bom.csv', 'export-1252.csv'):
    print(name.ljust(20) + str(len(open(name, 'rb').read())).rjust(3) + ' bytes')

calc.sh is the spreadsheet. show prints one <td> per cell; sdval is the stored value.

#!/bin/sh
# Hand a CSV to LibreOffice Calc headless.
#   sh calc.sh show f.csv       what Calc displays when it guesses the encoding
#   sh calc.sh show f.csv 76    same, with the encoding forced (76 UTF-8, 1 windows-1252)
#   sh calc.sh save f.csv       open, save back as CSV, print the bytes that came out
# LOPROFILE has to be a short path. A profile under a deep directory made this
# build exit 127 with no message and convert nothing.
SOFFICE="/c/Program Files/LibreOffice/program/soffice.com"
LOPROFILE=${LOPROFILE:-C:/Users/$USERNAME/AppData/Local/Temp/lop-csvenc}
PROFILE="-env:UserInstallation=file:///$LOPROFILE"
MODE=$1
FILE=$2
CS=$3
BASE=$(basename "$FILE" .csv)
FILTER="Text - txt - csv (StarCalc):44,34,$CS,1"

case "$MODE" in
  show)
    if [ -n "$CS" ]; then
      MSYS_NO_PATHCONV=1 "$SOFFICE" "$PROFILE" --headless --infilter="$FILTER" --convert-to html --outdir calc "$FILE" >/dev/null 2>&1
    else
      MSYS_NO_PATHCONV=1 "$SOFFICE" "$PROFILE" --headless --convert-to html --outdir calc "$FILE" >/dev/null 2>&1
    fi
    sed -e 's/ data-sheets-value="[^"]*"/ stored-as-text/' "calc/$BASE.html" | grep -o '<td[^>]*>[^<]*'
    ;;
  save)
    MSYS_NO_PATHCONV=1 "$SOFFICE" "$PROFILE" --headless --convert-to csv --outdir saved "$FILE" >/dev/null 2>&1
    echo "== saved/$BASE.csv  bytes $(wc -c < "saved/$BASE.csv")"
    xxd "saved/$BASE.csv"
    ;;
esac

csvencoding.mjs is the deliverable: report gates a file, cells reads back the display, header measures the mark.

import { readFileSync } from 'node:fs';

const FFFD = String.fromCharCode(0xfffd);
const BOM = [0xef, 0xbb, 0xbf];
const hex = (n) => n.toString(16).padStart(2, '0');
const cp = (s) => [...s]
  .map((c) => 'U+' + c.codePointAt(0).toString(16).toUpperCase().padStart(4, '0'))
  .join(' ');

// Bytes 0x80 to 0x9F that windows-1252 leaves undefined, read out of the
// decoder rather than typed: those are the ones that come back as a C1 control.
const dec1252 = new TextDecoder('windows-1252');
const undefined1252 = new Set();
for (let b = 0x80; b < 0xa0; b += 1) {
  const c = dec1252.decode(Uint8Array.of(b)).codePointAt(0);
  if (c >= 0x80 && c <= 0x9f) undefined1252.add(b);
}

function report(file) {
  const buf = readFileSync(file);
  const bom = BOM.every((b, i) => buf[i] === b);
  const body = bom ? buf.subarray(3) : buf;
  let valid = true;
  let text = '';
  try {
    text = new TextDecoder('utf-8', { fatal: true }).decode(body);
  } catch {
    valid = false;
    text = new TextDecoder('utf-8').decode(body);
  }

  // Every run of bytes above 0x7F, which is what a legacy reader would
  // render as one or more extra characters.
  let runs = 0;
  let silent = 0;
  let run = [];
  const flush = () => {
    if (run.length === 0) return;
    runs += 1;
    if (run.every((b) => !undefined1252.has(b))) silent += 1;
    run = [];
  };
  for (const b of body) {
    if (b > 0x7f) run.push(b);
    else flush();
  }
  flush();

  const lost = [...text].filter((c) => c === FFFD).length;
  const first3 = [...buf.subarray(0, 3)].map(hex).join(' ');
  console.log(file.padEnd(24) + String(buf.length).padStart(3) + ' bytes  first3 ' + first3);
  const row = (k, v) => console.log('  ' + k.padEnd(24) + v);
  row('bom', bom ? 'yes' : 'no');
  row('valid utf-8', valid ? 'yes' : 'no');
  row('non-ascii runs', runs + ', of which ' + silent + ' decode as windows-1252 with no error');
  row('u+fffd on a utf-8 read', String(lost));
  let verdict;
  if (!valid) verdict = 'not utf-8. a reader that assumes utf-8 destroys ' + runs + ' runs';
  else if (lost > 0) verdict = 'utf-8 and already damaged, ' + lost + ' characters are gone';
  else if (runs === 0) verdict = 'ascii only, no reader can get this wrong';
  else if (bom) verdict = 'utf-8, and the bom says so';
  else verdict = 'utf-8, nothing in the file says so, ' + silent + ' runs read as windows-1252 silently';
  row('verdict', verdict);
}

// Read back what Calc displayed, from the html it wrote.
function cells(file) {
  const html = readFileSync(file, 'utf8');
  const out = [];
  let i = html.indexOf('<td');
  while (i !== -1) {
    const gt = html.indexOf('>', i);
    const lt = html.indexOf('<', gt);
    out.push(html.slice(gt + 1, lt));
    i = html.indexOf('<td', lt);
  }
  const entity = (s) => s
    .split('&nbsp;').join(String.fromCharCode(0xa0))
    .split('&quot;').join(String.fromCharCode(34))
    .split('&lt;').join('<')
    .split('&gt;').join('>')
    .split('&amp;').join('&');
  for (let r = 0; r + 1 < out.length; r += 2) {
    const value = entity(out[r + 1]);
    console.log(entity(out[r]).padEnd(7) + String([...value].length).padStart(2) + ' chars  ' + cp(value));
  }
}

// What a reader that was handed the file as text gets for the first column name.
function header(file) {
  const text = readFileSync(file, 'utf8');
  const name = text.split(String.fromCharCode(10))[0].split(',')[0];
  console.log(file.padEnd(24) + String(readFileSync(file).length).padStart(3) + ' bytes  header[0] '
    + String([...name].length) + ' chars  ' + cp(name));
}

const mode = process.argv[2];
if (mode === 'report') for (const f of process.argv.slice(3)) report(f);
else if (mode === 'cells') cells(process.argv[3]);
else if (mode === 'header') for (const f of process.argv.slice(3)) header(f);

Build the fixtures.

python make.py

cp1252 strict: UnicodeEncodeError at position 30, U+1F600
export-utf8.csv      61 bytes
export-utf8-bom.csv  64 bytes
export-1252.csv      54 bytes

The legacy encoding has no code for the emoji, so that file was written with errors='replace'. The substitution happened in the exporter.

Steps

1. 1

   Step 1.

   Read the bytes.

   for f in export-utf8.csv export-utf8-bom.csv export-1252.csv; do echo "== $f"; xxd "$f"; done
   

   == export-utf8.csv
   00000000: 6b69 6e64 2c76 616c 7565 0a61 6375 7465  kind,value.acute
   00000010: 2c63 6166 c3a9 0a65 6d6f 6a69 2c6f 6bf0  ,caf...emoji,ok.
   00000020: 9f98 800a 6e62 7370 2c31 c2a0 3030 300a  ....nbsp,1..000.
   00000030: 7273 7175 6f2c 6974 e280 9973 0a         rsquo,it...s.
   == export-utf8-bom.csv
   00000000: efbb bf6b 696e 642c 7661 6c75 650a 6163  ...kind,value.ac
   00000010: 7574 652c 6361 66c3 a90a 656d 6f6a 692c  ute,caf...emoji,
   00000020: 6f6b f09f 9880 0a6e 6273 702c 31c2 a030  ok.....nbsp,1..0
   00000030: 3030 0a72 7371 756f 2c69 74e2 8099 730a  00.rsquo,it...s.
   == export-1252.csv
   00000000: 6b69 6e64 2c76 616c 7565 0a61 6375 7465  kind,value.acute
   00000010: 2c63 6166 e90a 656d 6f6a 692c 6f6b 3f0a  ,caf..emoji,ok?.
   00000020: 6e62 7370 2c31 a030 3030 0a72 7371 756f  nbsp,1.000.rsquo
   00000030: 2c69 7492 730a                           ,it.s.

   Three correct files. c3 a9 and e9 are the same letter written two ways, and 3f is the exporter's substitute for the emoji.

2. 2

   Step 2.

   Ask a byte-level tool what they are.

   file --mime-encoding export-utf8.csv export-utf8-bom.csv export-1252.csv
   

   export-utf8.csv:     utf-8
   export-utf8-bom.csv: utf-8
   export-1252.csv:     unknown-8bit

   unknown-8bit is the honest answer for a single-byte file: those bytes are legal in a dozen encodings and the file names none.

3. 3

   Step 3.

   Open all three in the spreadsheet.

   for f in export-utf8.csv export-1252.csv export-utf8-bom.csv; do echo "== $f"; sh calc.sh show "$f"; done
   

   == export-utf8.csv
   <td height="21" align="left" stored-as-text>kind
   <td align="left" stored-as-text>value
   <td height="21" align="left" stored-as-text>acute
   <td align="left" stored-as-text>café
   <td height="21" align="left" stored-as-text>emoji
   <td align="left" stored-as-text>ok😀
   <td height="21" align="left" stored-as-text>nbsp
   <td align="right" sdval="1000" sdnum="1058;">1000
   <td height="21" align="left" stored-as-text>rsquo
   <td align="left" stored-as-text>it’s
   == export-1252.csv
   <td height="21" align="left" stored-as-text>kind
   <td align="left" stored-as-text>value
   <td height="21" align="left" stored-as-text>acute
   <td align="left" stored-as-text>caf�
   <td height="21" align="left" stored-as-text>emoji
   <td align="left" stored-as-text>ok?
   <td height="21" align="left" stored-as-text>nbsp
   <td align="left" stored-as-text>1�000
   <td height="21" align="left" stored-as-text>rsquo
   <td align="left" stored-as-text>it�s
   == export-utf8-bom.csv
   <td height="21" align="left" stored-as-text>kind
   <td align="left" stored-as-text>value
   <td height="21" align="left" stored-as-text>acute
   <td align="left" stored-as-text>café
   <td height="21" align="left" stored-as-text>emoji
   <td align="left" stored-as-text>ok😀
   <td height="21" align="left" stored-as-text>nbsp
   <td align="right" sdval="1000" sdnum="1058;">1000
   <td height="21" align="left" stored-as-text>rsquo
   <td align="left" stored-as-text>it’s

   This reader assumes UTF-8 and never asks, so the correct windows-1252 file lost three characters on the way to the screen. The no-break space went the other way: the encoding was right and Calc stored 1000.

4. 4

   Step 4.

   Measure what the byte order mark costs.

   node csvencoding.mjs header export-utf8.csv export-utf8-bom.csv
   

   export-utf8.csv          61 bytes  header[0] 4 chars  U+006B U+0069 U+006E U+0064
   export-utf8-bom.csv      64 bytes  header[0] 5 chars  U+FEFF U+006B U+0069 U+006E U+0064

   Three bytes, and the first column is called something else. Step 3 shows both files displaying the same ten cells, so the mark bought nothing.

5. 5

   Step 5.

   Force a legacy code page on the correct file.

   sh calc.sh show export-utf8.csv 1
   

   <td height="21" align="left" stored-as-text>kind
   <td align="left" stored-as-text>value
   <td height="21" align="left" stored-as-text>acute
   <td align="left" stored-as-text>café
   <td height="21" align="left" stored-as-text>emoji
   <td align="left" stored-as-text>ok😀
   <td height="21" align="left" stored-as-text>nbsp
   <td align="left" stored-as-text>1Â&nbsp;000
   <td height="21" align="left" stored-as-text>rsquo
   <td align="left" stored-as-text>it’s

   1 is this filter's code for windows-1252. No byte was lost, so reading the file again with the right code undoes it.

6. 6

   Step 6.

   Read the same cells back as code points.

   node csvencoding.mjs cells calc/export-utf8.html
   

   kind    5 chars  U+0076 U+0061 U+006C U+0075 U+0065
   acute   5 chars  U+0063 U+0061 U+0066 U+00C3 U+00A9
   emoji   6 chars  U+006F U+006B U+00F0 U+0178 U+02DC U+20AC
   nbsp    6 chars  U+0031 U+00C2 U+00A0 U+0030 U+0030 U+0030
   rsquo   6 chars  U+0069 U+0074 U+00E2 U+20AC U+2122 U+0073

   U+0178, U+02DC and U+2122 name the pair: those code points sit at bytes 9f, 98 and 99 in windows-1252 and nowhere in latin-1. U+00A0 renders as a gap, so read the nbsp row by its count.

7. 7

   Step 7.

   Force the same encoding on the file that is in it.

   sh calc.sh show export-1252.csv 1
   

   <td height="21" align="left" stored-as-text>kind
   <td align="left" stored-as-text>value
   <td height="21" align="left" stored-as-text>acute
   <td align="left" stored-as-text>café
   <td height="21" align="left" stored-as-text>emoji
   <td align="left" stored-as-text>ok?
   <td height="21" align="left" stored-as-text>nbsp
   <td align="right" sdval="1000" sdnum="1058;">1000
   <td height="21" align="left" stored-as-text>rsquo
   <td align="left" stored-as-text>it’s

   One argument at import brings back three of the four values. ok? stays, because that loss happened in the exporter.

8. 8

   Step 8.

   Open each file and save it back.

   for f in export-utf8.csv export-utf8-bom.csv export-1252.csv; do sh calc.sh save "$f"; done
   

   == saved/export-utf8.csv  bytes 64
   00000000: 6b69 6e64 2c76 616c 7565 0d0a 6163 7574  kind,value..acut
   00000010: 652c 6361 66c3 a90d 0a65 6d6f 6a69 2c6f  e,caf....emoji,o
   00000020: 6bf0 9f98 800d 0a6e 6273 702c 3130 3030  k......nbsp,1000
   00000030: 0d0a 7273 7175 6f2c 6974 e280 9973 0d0a  ..rsquo,it...s..
   == saved/export-utf8-bom.csv  bytes 67
   00000000: efbb bf6b 696e 642c 7661 6c75 650d 0a61  ...kind,value..a
   00000010: 6375 7465 2c63 6166 c3a9 0d0a 656d 6f6a  cute,caf....emoj
   00000020: 692c 6f6b f09f 9880 0d0a 6e62 7370 2c31  i,ok......nbsp,1
   00000030: 3030 300d 0a72 7371 756f 2c69 74e2 8099  000..rsquo,it...
   00000040: 730d 0a                                  s..
   == saved/export-1252.csv  bytes 65
   00000000: 6b69 6e64 2c76 616c 7565 0d0a 6163 7574  kind,value..acut
   00000010: 652c 6361 66ef bfbd 0d0a 656d 6f6a 692c  e,caf.....emoji,
   00000020: 6f6b 3f0d 0a6e 6273 702c 31ef bfbd 3030  ok?..nbsp,1...00
   00000030: 300d 0a72 7371 756f 2c69 74ef bfbd 730d  0..rsquo,it...s.
   00000040: 0a                                       .

   The save writes UTF-8 whatever went in, keeps a mark that was there, and rewrites every terminator to CRLF. Byte e9 came back as ef bf bd, U+FFFD in UTF-8, so the misreading is now the content.

9. 9

   Step 9.

   Run the gate over a clean and a damaged file.

   node csvencoding.mjs report export-utf8.csv export-1252.csv saved/export-1252.csv
   

   export-utf8.csv          61 bytes  first3 6b 69 6e
   bom                     no
   valid utf-8             yes
   non-ascii runs          4, of which 4 decode as windows-1252 with no error
   u+fffd on a utf-8 read  0
   verdict                 utf-8, nothing in the file says so, 4 runs read as windows-1252 silently
   export-1252.csv          54 bytes  first3 6b 69 6e
   bom                     no
   valid utf-8             no
   non-ascii runs          3, of which 3 decode as windows-1252 with no error
   u+fffd on a utf-8 read  3
   verdict                 not utf-8. a reader that assumes utf-8 destroys 3 runs
   saved/export-1252.csv    65 bytes  first3 6b 69 6e
   bom                     no
   valid utf-8             yes
   non-ascii runs          3, of which 3 decode as windows-1252 with no error
   u+fffd on a utf-8 read  3
   verdict                 utf-8 and already damaged, 3 characters are gone

   Three verdicts for three states. Fear the last: the file passes a UTF-8 validity check with the letters gone, so a gate testing validity alone reports it clean.

What the wrong characters mean:

| What the cell shows | Source character | Bytes in the file | Which pair collided | | --- | --- | --- | --- | | café | é U+00E9 | c3 a9 | UTF-8 read as windows-1252 | | it’s | ’ U+2019 | e2 80 99 | UTF-8 read as windows-1252 | | 1Â and a gap | U+00A0 | c2 a0 | UTF-8 read as windows-1252 | | ok😀 | 😀 U+1F600 | f0 9f 98 80 | UTF-8 read as windows-1252 | | caf� | é U+00E9 | e9 | windows-1252 read as UTF-8 | | ok? | 😀 U+1F600 | 3f | the exporter substituted at write time |

How to read the result

| What you see | What it means | What to do | | --- | --- | --- | | valid utf-8 yes and readable cells | The file and the reader agree | Record which encoding the export writes, so the agreement is a decision rather than luck. | | valid utf-8 no and U+FFFD on screen | The bytes are legal and the reader guessed wrong | Force the encoding at import, as step 7 does. The file needs no change. | | A two-character sequence starting Ã or â | UTF-8 was read as a single-byte code page | Nothing is lost yet. Re-open with the right code and follow the mojibake check. | | valid utf-8 yes with U+FFFD in the bytes | A reader already replaced those bytes and something saved the result | Re-export from the source. This copy cannot be repaired. | | A ? where a character should be | The exporter could not encode it | Fix the export encoding. No import setting brings it back. | | A cell with sdval where you wrote text | The reader typed the field | Step 3 turned a no-break space into the number 1000. Follow the leading-zeros check. |

Common mistakes

Thresholds

What to check next

• How to check the encoding of a CSV file: the byte-level half.
• How to check for garbled characters from the wrong encoding: repairing step 5's text.
• How to check leading zeros in a csv export: the same reader typing a field.
• How to test csv import validation: the file reaching a service.
• How to compare two csv files: proving two copies differ.

FAQ

Why does a utf 8 csv open with the wrong characters?

The file does not say it is UTF-8 and the reader has a default. Step 5 forces windows-1252 on a correct file and each accented letter becomes two.

Should an export write a utf 8 bom?

On the reader measured here, no. Step 3 shows the same ten cells with and without it, and step 4 shows it adding U+FEFF to the column name.

How do I open a csv file with utf 8 encoding?

Name the encoding at import. Here that is --infilter="Text - txt - csv (StarCalc):44,34,76,1", where 76 is UTF-8. Excel is not on this machine, so its dialog is undescribed.

Can question marks in an exported column be recovered?

No. A question mark is byte 3f, written when the target encoding had no code for the character. Re-export in an encoding that covers the data.

Verified