Fixing Garbled CSV: Delimiters, Encodings and Strange Characters
You open a CSV file and see everything crammed into one column, or names like José and Müller where accents should be. Nothing is wrong with the data. The file is simply being read with the wrong assumptions about how its columns are separated or how its characters are encoded. Once you know the two questions to ask, these problems are quick to diagnose. This guide shows how, and how our tools handle each case.
Problem one: everything in one column (the delimiter)
A CSV file does not have to use commas. Many programs in countries where the comma is the decimal separator, such as much of continental Europe, write files separated by semicolons. Other exports use tabs (often called TSV) or the pipe character. If the program reading the file expects commas but the file uses semicolons, it sees one long column.
The quickest diagnosis is to open the file in a plain text editor and look at the first line. Whichever character repeats between the headings is the delimiter. A second symptom to watch for is numbers such as 3,14 within a semicolon file: the comma there is a decimal mark, not a separator, and it should not be changed.
Fixing delimiter trouble with our tools
- CSV & TSV Converter reads your file and rewrites it with comma, tab or semicolon separators, so you can produce exactly what the receiving program expects.
- CSV to JSON has a delimiter setting that defaults to automatic detection and can be set to comma, semicolon, tab or pipe if detection guesses wrong.
- CSV to Excel turns the file into a proper workbook, so the columns are separated for good.
Cells that contain the delimiter must be wrapped in double quotes, such as "Smith, John". A broken or missing quote can make columns appear shifted from that row downwards. If one row looks wrong, inspect it in a text editor.
Problem two: strange characters (the encoding)
Computers store text as bytes, and an encoding says which bytes mean which characters. Two encodings cause most of the confusion:
- UTF-8 is the modern standard. It can represent every language, and it uses more than one byte for accented and non-Latin characters.
- Windows-1252 is an older single-byte encoding used by many Windows programs for Western European text. It has the same characters for plain English letters but represents accents differently.
If UTF-8 text is read as Windows-1252, a character like é turns into é. If Windows-1252 text is read as UTF-8, accents may turn into question marks or a diamond with a question mark, because those bytes are not valid UTF-8. The recognisable "Ã" and "â€" sequences are the signature of UTF-8 being misread as Windows-1252.
How our tools read text files
To be exact about our own behaviour: when you open a text or CSV file in our data tools, the file is read as UTF-8 first. If the bytes turn out not to be valid UTF-8, the tool falls back to Windows-1252. Files that begin with a UTF-16 byte-order mark are read as UTF-16. This means common Western files, either UTF-8 or Windows-1252, usually open correctly without any setting. There is no manual encoding picker, so files in other encodings, such as Shift-JIS, may still show wrong characters. In that case, open the file in a text editor that lets you choose the encoding and save it as UTF-8, then use the tool.
The reverse problem happens when you take a clean UTF-8 file into older spreadsheet software. Some versions of Excel assume a legacy encoding when a CSV has no byte-order mark, and show accents wrongly. Instead of double-clicking the CSV, import it through the program's data import feature and select UTF-8 as the file origin, or convert the file to an Excel workbook, which stores text unambiguously, with CSV to Excel.
Step by step: a repair routine
- Open the file in a text editor and look at the first two lines. Identify the delimiter and check whether accented letters look right.
- If the delimiter is wrong for your target program, run the file through CSV & TSV Converter and pick the separator you need.
- If characters are wrong, find which encoding the original export used. Re-export from the source with UTF-8 if the option exists. Otherwise open in an editor with an encoding choice and save as UTF-8.
- Tidy the content with CSV Clean: it trims spaces, drops empty rows and removes duplicates.
- Convert to the final format, such as an Excel workbook or JSON, and spot-check rows with accents, long numbers and quoted commas.
Other things that look like encoding problems
Dates and numbers that changed
This is a separate issue: spreadsheet programs guess types. A code like 0012 becomes 12, and a long ID becomes 1.23E+15. The CSV itself is fine; the damage happens when it is opened and saved again. Open the original as text, or import columns as text.
Invisible characters at the start
A byte-order mark at the start of a UTF-8 file can make the first column heading look odd in some programs. Re-saving the file without it, or reading it as UTF-8 with BOM handling, fixes that.
Line endings
Windows uses carriage return plus line feed; other systems use only line feed. Most modern programs handle both, but a very old tool might show everything on a single line.
Huge files
If the file is too large for your spreadsheet program, CSV Split cuts it into files of a chosen number of rows, and CSV Merge stacks files with the same columns back together.
Prevent it next time
When you create CSV files for others, use UTF-8, use a comma unless you know the recipient expects another delimiter, quote any cell containing a delimiter or line break, use ISO dates, and write one sentence describing the columns. When you receive files, ask which delimiter and encoding the sender used. If you are choosing a format from scratch, our guide on CSV versus Excel versus JSON helps you decide whether CSV is the right container at all. If a file seems damaged rather than misread, compare its checksum with the sender's value; see how checksums verify downloads.
Written by SmarTech Apps. General information only, not legal or professional advice. Questions or corrections? Contact us.