Fix Weird Characters in a CSV (é, â, ’) Once and for All

You open a CSV and "café" reads as "café", "naïve" becomes "naïve", and a curly apostrophe shows up as "’". This garbled text has a name — mojibake — and it always comes from the same root cause: the file was saved in one character encoding and read in another. Once you understand the mismatch, the fix is quick.

What's Actually Happening

Most modern data is encoded as UTF-8, where accented and special characters are stored as multiple bytes. Mojibake appears when those UTF-8 bytes are read as a single-byte encoding like Windows-1252 or Latin-1. Each byte gets shown as its own character, so the one character "é" turns into the two characters "é".

How a single character breaks apart:

Correct (UTF-8 read as UTF-8):   café
Broken  (UTF-8 read as Latin-1): café

Common offenders:
é  →  é
â  →  â
ï  →  ï
'  →  ’   (curly apostrophe)
"  →  “   (curly quote)
–  →  –   (en dash)

Where Mojibake Comes From

On Windows, Excel saves a plain "CSV (Comma delimited)" file in the system locale encoding — often Windows-1252 — instead of UTF-8. Text pasted from Word, PDFs, or older systems frequently carries smart quotes and dashes that only survive in UTF-8. When that file is later opened by a tool that assumes UTF-8 (or the reverse), the bytes are misread and you get mojibake.

Step 1: Work Out Whether the Damage Is Baked In

This determines which fix you need. There are two different situations that look identical on screen. In the first, the file's bytes are fine and only the program reading it picked the wrong encoding — reopen it with the right one and the text is perfect. In the second, someone already saved the misread text, so the garbage characters are now literally in the file, and no encoding choice will display them correctly.

To tell them apart, open the file in an editor that lets you switch encodings. In VS Code, click the encoding shown in the status bar and choose "Reopen with Encoding" — try UTF-8 and Windows 1252. In Notepad++, use the Encoding menu. If one of them shows "café" correctly, the bytes are fine and you just need to read (or convert) the file properly. If "café" appears no matter which encoding you pick, the damage is baked in and needs repairing.

Fix 1: Re-Export From the Source

The cleanest fix, when you still have access to the system that produced the file, is to re-save with the right encoding. In Excel, use "Save As" and choose "CSV UTF-8 (Comma delimited) (.csv)" rather than plain CSV — they sit next to each other in the format list and the difference matters. In Google Sheets, downloading as CSV always produces UTF-8. Most databases and SaaS platforms have an explicit UTF-8 export option; turn it on and the whole problem disappears at the source.

Fix 2: Reopen or Convert With the Right Encoding

If the bytes are fine (your Step 1 test found an encoding that displays correctly), you have two easy options. To view the data in Excel without damage, don't double-click the file — use Data > Get Data > From Text/CSV and set the File Origin dropdown to the correct encoding (65001: Unicode (UTF-8) for a UTF-8 file). To permanently convert the file, open it in VS Code or Notepad++ with the correct encoding, then save it back as UTF-8 ("Save with Encoding" in VS Code, Encoding > Convert to UTF-8 in Notepad++). From then on every standards-respecting tool reads it correctly.

Fix 3: Repair Baked-In Mojibake With Code

When the garbled characters are already stored in the file, you can often reverse the damage by re-running the mistake backwards: encode the broken text into the single-byte encoding it was misread as, then decode those bytes as the UTF-8 they originally were.

# Recover mojibake text in Python
fixed = garbled.encode("latin-1").decode("utf-8")
# "café" -> "café"

# When reading a file that may have a BOM, use utf-8-sig
import csv
with open("file.csv", encoding="utf-8-sig") as f:
    rows = list(csv.reader(f))

This round-trip works when the whole file was garbled the same way exactly once. If the text went through the wringer twice (you'll see monsters like "café"), apply the same round-trip repeatedly until it stabilizes, or use the Python library ftfy, which exists precisely to untangle layered mojibake automatically.

Fix 4: Find & Replace the Common Sequences

No code handy? Because mojibake is deterministic — the same source character always produces the same garbage sequence — a plain find and replace fixes it too. Work through the sequences that actually appear in your file:

Find      Replace with
é         é
è         è
â         â
ï         ï
ñ         ñ
ü         ü
’        '   (apostrophe)
“        "   (opening quote)
”        "   (closing quote)
–        -   (dash)

This works in Excel, in a text editor, or as a set of find & replace steps in PipeSheets — where the advantage is that the replacements are saved as a pipeline, so a recurring export that always arrives with the same dozen garbled sequences gets repaired in one click each time, with a preview to confirm before you download.

Edge Cases Worth Knowing

The Faster Way: Clean Once, Save the Recipe

PipeSheets reads UTF-8 CSVs (with or without a BOM) and falls back to Windows-1252 for legacy files, and everything it exports is clean UTF-8 — so a cp1252 file from Excel comes out the other side as a UTF-8 file that imports anywhere. For garbage that's already baked into the text, add find & replace steps for the sequences in your file, preview the repaired result, and download. Save the steps as a pipeline and the same fix runs on every future export from the same messy source.