Fix Garbled Characters and Broken CSV Imports (UTF-8)

A CSV is the universal way to move spreadsheet data between systems, and also one of the most common reasons an import fails. The format looks trivial (values separated by commas) but it carries no information about its own text encoding, delimiter or column types. Every program that opens it has to guess. This guide explains the guesses that go wrong and how to take control of them.

What a CSV file actually is

A CSV is plain text: one record per line, fields separated by a delimiter, usually a comma. RFC 4180, the closest thing to a specification, recommends that fields containing commas, double quotes or line breaks be enclosed in double quotes, and says that a double quote inside a quoted field is written as two double quotes. A header line with column names is optional. Everything else (encoding, how to write dates, which delimiter, what is a number) is left to the programs on each end.

id,name,address
001,"Smith, John","12 Oak Street
Apartment 4"
002,"Anna ""Ann"" Lee",Berlin

Note that 001 is just three characters. Nothing in the file says it should stay text, which is why Excel may turn it into the number 1.

Problem 1: é, ’ and other garbled characters

This is called mojibake. It happens when text written in one encoding is read as another. The usual case: a file saved as UTF-8 (where "é" takes two bytes) is read as Windows-1252, a single-byte encoding, so each byte is shown as its own character. The two bytes of "é" become é.

A real example: "José Martínez" arrives as José Martínez. The telltale sign is the pattern: a capital Ã or Â followed by an odd symbol, or â€ followed by a symbol, where an accented letter or punctuation mark should be.

The fix: re-open the original with the right encoding

1. Do not edit or save the damaged workbook. Go back to the original CSV file.
2. In Excel choose Data, From Text/CSV and select the file.
3. In the preview window set File Origin to 65001: Unicode (UTF-8). The preview updates immediately; if the characters look right, click Load.
4. If they still look wrong, try other origins in turn. A file from an old Windows system may genuinely be Windows-1252, and one from another region may be a different legacy encoding.

Double-clicking a CSV skips all of this. Excel then picks an encoding itself, and UTF-8 files without a byte-order mark (BOM) are commonly misread.

If only a damaged copy exists, the text can sometimes be repaired by reversing the mistake (encode as Windows-1252, decode as UTF-8). Free tools such as the open-source Python library ftfy do this automatically. If the characters were already replaced by ? or �, the information is gone and only the original will do.

Problem 2: everything lands in one column

In many European locales the comma is the decimal mark, so those versions of Excel use a semicolon as the list separator and expect it in CSV files (and write it when saving). A comma-separated file opened there shows each row as one cell, and a semicolon-separated file opened on a US machine does the same. The delimiter Excel assumes comes from the regional "list separator" setting of your operating system.

• Fix on open: use Data, From Text/CSV and choose the delimiter in the preview. Or select the column and use Data, Text to Columns, Delimited and tick the right separator.
• Fix at the source: agree one format with whoever supplies the file and write it in your import instructions.
• Danger sign: if decimals are written with commas (1250,50) in a comma-delimited file, the numbers must be quoted or the columns shift.

Problem 3: leading zeros, long numbers and surprise dates

When Excel loads a CSV it converts what looks like a number or a date. That is why 00123 becomes 123, 1234567890123456 becomes 1234567890123450 (Excel stores 15 significant digits) and 3-4 becomes a date. To stop it:

1. Open the file with Data, From Text/CSV.
2. In the preview set Data Type Detection to Do not detect data types. Everything arrives as text.
3. Click Transform Data only if you want to set specific columns to numbers or dates afterwards.
4. Load, and keep identifier columns (phone numbers, ZIP codes, account numbers, SKUs) as Text.

When you save a workbook as CSV, what you see is what is written: if a leading zero was already lost inside the workbook, the CSV carries the loss with it. And when the receiving program opens that CSV, it may strip the zeros again, so it is worth testing with a small file first.

Problem 4: line breaks and quotes inside cells

An address typed with Alt+Enter contains a line break. In a correct CSV that field is wrapped in double quotes, so one record spans two lines. A program that does not understand the quoting splits the record into two broken rows. A stray single double quote has a similar effect, because the parser treats everything after it as one long quoted field.

• Symptom: row counts that do not match, rows with too few or too many columns, or the last columns filled for some rows only.
• Check: after import, compare the count of rows with the source, and test that every row has a value in a column that should never be empty (such as an ID).
• Fix: replace line breaks inside cells before export: =SUBSTITUTE(SUBSTITUTE(A2,CHAR(10)," "),CHAR(13)," ").

Problem 5: things a CSV cannot hold

Problem 6: email lists

Email columns collect their own set of defects. After trimming spaces and lowercasing, look for:

• A missing @ or a missing dot in the domain.
• Two addresses in one cell, separated by a comma, semicolon or slash.
• Text around the address: mailto:anna@mail.com, Anna <anna@mail.com>.
• Typos in popular domains: gmial.com, gmai.com, gmail.con, hotmial.com, yaho.com. A short lookup of known-wrong and right domains fixes most.

Syntax checks only prove that an address is shaped correctly. Whether a mailbox exists can only be learned from the mail provider, so expect some bounces even from a clean list.

Import-ready checklist

1. One header row with unique, plain column names. No merged cells, no title rows, no totals.
2. UTF-8 encoding, and the delimiter the target expects.
3. Identifiers as text: phone numbers (E.164 if the target accepts it), ZIP codes, IDs, account numbers.
4. Dates in ISO form, yyyy-mm-dd (see the dates guide).
5. No leading or trailing spaces, consistent capitalization, one value per cell.
6. No duplicates on the key the target uses (see the duplicates guide).
7. Valid emails, typo domains fixed.
8. Row and size limits of the target system respected; check its documentation.
9. Test with ten rows first. Import a small sample, open the result, and compare it with the source before importing everything.