CSV encoding problems when combining files: UTF-8 vs ANSI

Reading the symptoms

A text file stores characters as bytes, and the encoding is the rule that maps one to the other. English letters are identical in UTF-8 and Windows-1252, which is why problems only show up on accents, currency symbols and names. The pattern of the garbage tells you what went wrong.

The replacement character � is the worst case: a program that substituted it while saving has thrown the original byte away. Fix encodings on copies of the original exports, not on files that have already been re-saved.

Why merging makes it worse

A command-line merge (copy *.csv on Windows, cat on a Mac) joins bytes without converting anything. If file one is UTF-8 and file two is Windows-1252, the result is a mix that no single encoding reads correctly: open it as UTF-8 and file two's accents break; open it as ANSI and file one's break.

Byte order marks cause the second problem. Excel's CSV UTF-8 save adds the three bytes EF BB BF at the start of the file. Concatenate three such files and the second and third BOMs land in the middle of the data, glued to the first header of each, so the repeated header row no longer matches and filters to remove it miss.

Find each file's encoding

• Notepad (Windows 10/11): open the file; the status bar shows UTF-8, UTF-8 with BOM or ANSI.
• VS Code or Notepad++: the encoding appears in the status bar or Encoding menu, and both can reopen or convert it.
• Mac or Linux terminal: file -I name.csv (Mac) or file -i name.csv (Linux) prints a charset guess.

Detection is a guess. A file with no accented characters looks like plain ASCII and is valid as both, which is fine: it will merge cleanly either way.

Convert everything to UTF-8, then merge

Pick one encoding, UTF-8, and convert the files that differ. In Excel, import the file with Data > From Text/CSV, set File Origin to the right encoding (1252: Western European for ANSI, 65001 for UTF-8) until the preview looks right, load it, then File > Save As > CSV UTF-8 (Comma delimited). Note that this save adds a BOM.

For many files, convert in bulk without opening them in Excel:

# Mac/Linux: convert ANSI files to UTF-8 copies
mkdir -p utf8
for f in exports/*.csv; do
  iconv -f WINDOWS-1252 -t UTF-8 "$f" > "utf8/$(basename "$f")"
done

import glob
import pandas as pd

frames = []
for f in sorted(glob.glob("exports/*.csv")):
    try:
        frames.append(pd.read_csv(f, encoding="utf-8-sig", dtype=str))  # also strips a BOM
    except UnicodeDecodeError:
        frames.append(pd.read_csv(f, encoding="cp1252", dtype=str))
pd.concat(frames, ignore_index=True).to_csv("merged.csv", index=False, encoding="utf-8")

Only run iconv on files you know are ANSI: converting a file that is already UTF-8 double-encodes it and produces exactly the é garbage you are trying to remove. The Python version tries UTF-8 first and falls back, which is safer for a mixed folder. Power Query's From Folder applies one File Origin to every file, so it suits folders that share an encoding; the Windows merge guide and combining CSV files in Excel cover those routes.

Checks before you trust the merge

• Search the merged file for Ã, Â,  and �. Any hit means a file was read with the wrong encoding.
• Count distinct values of a name or city column: duplicates like Zürich and Zürich reveal mixed encodings.
• Confirm the first column's header matches exactly in a filter or lookup.

Analistable reads files in your browser and expects UTF-8, so converting stray ANSI exports first gives clean text there as well. The merge CSV files guide lists the other merge methods.

A worked example of a mixed folder

Here is a typical case: three regional exports, each produced by a different tool, combined into one customer list.

Merged with copy, the output holds 3,735 data rows, but south.csv's names with accents (Renée, Müller) appear as Ren�e when the file is opened as UTF-8. File order matters too: north.csv is the one with a BOM, so while it comes first the BOM sits harmlessly at the start of the output. Put it second and its BOM lands mid-file, so its header row slips past your header filter. After converting south.csv and reading every file with BOM handling, the merge gives the same 3,735 rows with every name intact.

A plain Excel "CSV (Comma delimited)" save on Windows writes the system code page rather than UTF-8, so characters outside that code page, such as Polish, Greek or Chinese letters, are replaced with question marks at save time. That damage cannot be undone from the file. If your data contains non-Western names, always use the CSV UTF-8 option when saving from Excel, and ask the systems you export from for UTF-8 output.

On a Mac, Numbers and TextEdit default to UTF-8, so the problem usually appears when files from a Windows colleague arrive in the same folder as your own.

Frequently asked questions

What is ANSI encoding in a CSV?

On Windows, "ANSI" usually means the system code page, Windows-1252 for Western European languages. It stores one byte per character and cannot hold most non-Latin scripts.

Should I save CSVs with or without a BOM?

Excel on Windows reads UTF-8 most reliably with a BOM, while many scripts and databases prefer none. When merging, keep at most one, at the very start.

Can I repair Caf� back to Café?

Not from that file. The replacement character discarded the original byte. Re-export or convert from the source file.

Why does the first column name not match in my lookup?

It probably starts with an invisible BOM. Re-read the file as UTF-8 with BOM handling, or retype the header.

Related guides

• How to merge CSV files on Windows
• How to combine CSV files in Excel
• Leading zeros lost when merging CSV files: how to keep IDs intact
• How to join a big CSV to a small lookup table
• How to join two CSV files by a common column