DataSwitch Pro Private, in-browser data converter

Fix Garbled Characters (é, è, ????) in CSV Files Opened in Excel

By the DataSwitch Pro team · · 4 min read

A customer list exported from a website opens in Excel and René has become René, Fès is Fès, and Arabic or Chinese names are unreadable. Or the reverse: a file saved from Excel shows question marks or diamonds when it is imported into a database. Both are character encoding problems, and both are fixable.

What an encoding is

A text file stores bytes, not letters. An encoding is the rule that maps bytes to characters. Two encodings matter for CSV files:

Reading the symptoms

The first case is fully recoverable — the original bytes are still there. The second usually is not: characters replaced by ? when saving are gone from that file.

Why Excel gets it wrong: the byte-order mark

A UTF-8 file can start with three invisible bytes, EF BB BF, called the byte-order mark (BOM). When Excel opens a CSV by double-clicking it, the BOM is how it recognises UTF-8. Without it, Excel falls back to the computer's legacy encoding — typically Windows-1252 — and UTF-8 text turns into mojibake. Most web applications and scripts write UTF-8 without a BOM, which is why their exports so often look broken in Excel.

Fix 1: Import with the right encoding

  1. Open a blank workbook and go to Data → From Text/CSV.
  2. Select the file and set File Origin to 65001: Unicode (UTF-8).
  3. Check that the preview shows the correct characters and delimiter, then click Load.

Fix 2: Add a BOM or convert the file

If the file will be opened by double-clicking — by you or by colleagues — give it a BOM. Upload the CSV to the converter and download it again: downloaded CSV files are written as UTF-8 with a BOM. Uploaded files are also decoded automatically, whether they are UTF-8, UTF-16 or Windows-1252, so mojibake does not appear in the first place. Even simpler, convert it to an Excel workbook, which always stores text as Unicode.

Fix 3: Save CSV files from Excel as UTF-8

When you export from Excel, choose CSV UTF-8 (Comma delimited) in the Save as type list, not CSV (Comma delimited). The plain CSV option writes your legacy code page and replaces any character it cannot represent — Arabic, Chinese, many emoji — with question marks. The UTF-8 option keeps everything and adds a BOM.

Handling encodings in code

When reading CSV files in your own code, decode them explicitly instead of relying on defaults. In Python, the utf-8-sig codec reads UTF-8 with or without a BOM and removes the BOM so it does not end up in your first column name:

Python

import csv

with open("export.csv", newline="", encoding="utf-8-sig") as f:
    rows = list(csv.DictReader(f))

# Legacy file from old Windows software:
with open("old.csv", newline="", encoding="cp1252") as f:
    rows = list(csv.DictReader(f))

A forgotten BOM is also behind a classic bug: the first header becomes "\ufeffid" instead of "id", so lookups for id fail even though the column is clearly there. The CSV to JSON converter strips the BOM automatically for this reason.

Repairing text that is already garbled

If mojibake has been saved into a file or a database, the fix is to reverse the wrong step: encode the garbled text back as Windows-1252 to recover the original bytes, then decode those bytes as UTF-8. In Python that is broken.encode("cp1252").decode("utf-8"). Try it on a copy, and only when every bad character follows the Ã… pattern.

Summary

Garbled characters mean the file was read with a different encoding than it was written in. Use UTF-8 everywhere, add a BOM to CSV files that will be opened in Excel, import with File Origin: UTF-8 when in doubt, and save from Excel as CSV UTF-8.

Try it on your own file. Free, private, and processed in your browser.

Excel to CSVCSV to ExcelCSV to JSON

Frequently asked questions

Should every CSV file have a BOM?

Only files meant to be opened in Excel need one. Most other software accepts it, but a few strict parsers treat it as part of the first column name, so for data exchanged between programs plain UTF-8 is usually preferred.

What is UTF-16 and why does Excel produce it?

UTF-16 uses two or more bytes per character. Excel's Unicode Text export uses it with tabs as separators. Most tools expect UTF-8, so convert such files before importing them elsewhere.

Can a file contain two encodings at once?

Yes — when files from different sources are concatenated. Such files must be split and decoded separately, because no single encoding reads them correctly.

More guides