Common CSV Problems: Encoding Mojibake, Delimiters, and Excel Traps

CSV is probably the oldest and most widely used data interchange format in the world. It looks trivially simple — just text separated by commas, right? But anyone who has handled CSV files from different systems or colleagues in different countries has stepped on the landmines: mojibake when opening the file, every column crammed into one cell, carefully maintained IDs mangled into scientific notation. All of these problems trace back to a single root cause: CSV has never had a truly universal standard.

Why CSV has no real standard

CSV predates the internet. Every system implemented its own flavor long before anyone wrote anything down. RFC 4180 arrived in 2005, but it merely describes common practice after the fact, and plenty of implementations ignore it. In the wild you’ll encounter:

• Delimiters that may be commas, semicolons, tabs, or even pipes
• Inconsistent quote-escaping rules (some use "", others \")
• Line endings in \n (Unix), \r\n (Windows), or \r (classic Mac) flavors
• Encodings ranging across UTF-8, GBK, Latin-1, and more

So the first rule of handling an unfamiliar CSV is: assume nothing, inspect first. Drop the file into a CSV viewer — it auto-detects comma, semicolon, and tab delimiters and renders the data as a table, so you can see the file’s real structure at a glance instead of counting commas in a text editor.

UTF-8 BOM and garbled text in Excel

A classic problem for anyone working with non-ASCII data: your program exports a perfectly valid UTF-8 CSV, someone double-clicks it in Excel, and all the Chinese (or accented, or Cyrillic) characters turn into mojibake. The reason: on Windows, Excel interprets unmarked CSV files using the system’s legacy locale encoding — GBK on a Simplified Chinese system, for example — and decodes the UTF-8 bytes incorrectly.

The fix is to prepend a UTF-8 BOM (the three bytes EF BB BF) to the file. When Excel sees that marker, it decodes the file as UTF-8 correctly. In Python, this is a one-word change:

df.to_csv('output.csv', encoding='utf-8-sig')

Conversely, if you receive a garbled CSV from someone else, don’t guess encodings at random. Confirm the actual encoding first — VS Code shows it in the status bar, and the file command detects it on the command line — then either transcode the file or open it with that encoding explicitly.

Comma, semicolon, tab: regional differences

In most English-speaking countries, CSV means comma-separated. But many European countries (Germany, France, Italy, and others) use a decimal comma — the number 3.14 is written 3,14. If those files also used commas between columns, every number would shatter. So Excel installations in those locales export CSV with semicolons by default.

This means “the file my colleague sent opens with all columns mashed into the first cell” is almost always a delimiter mismatch: the parser splits on commas while the file uses semicolons. How to cope:

• Detect the delimiter before parsing, or use a tool that auto-detects
• When exchanging data between systems, agree on the delimiter explicitly and document it
• If you must hand-edit, tab-separated (TSV) is the flavor least likely to collide with actual content

Embedded newlines and quote escaping

RFC 4180 says: if a field contains the delimiter, a double quote, or a newline, the whole field must be wrapped in double quotes, and any double quote inside the field is written as two double quotes. A legal record might look like this:

1001,"He said ""let's discuss the price"" and hung up","Note:
follow up next week",2026-08-06

This record contains a newline, yet it is still one record, not three. Naive parsers that “read line by line, then split on commas” fall apart immediately on such files — row counts go wrong, column counts go wrong, data bleeds across columns. If your program parses CSV, use the mature CSV library that ships with your language (Python’s csv module, csv-parse for Node, and so on). They all handle quoting and embedded newlines correctly. Never hand-roll split(',').

Excel’s “helpfulness”: scientific notation and dates

When Excel opens a CSV, it guesses a type for every field, and this “intelligence” has destroyed untold amounts of data:

• The ID 0012345 becomes the number 12345; the leading zeros are gone
• Long numbers — national IDs, tracking numbers — become scientific notation like 6.21E+17, with permanent precision loss
• Values like 3-4 or MAR1 are interpreted as dates and become 4-Mar or 1-Mar
• The genetics community famously renamed the gene MARCH1 to MARCHF1 because too many datasets had it auto-converted into a date

The critical insight: the damage happens the moment the file is opened, and once you hit save, it’s irreversible. Defenses:

1. Don’t double-click a CSV just to inspect raw data — use a text editor or a CSV viewer first
2. If you must work in Excel, use Data → From Text/CSV and explicitly set ID columns to “Text” in the import wizard
3. Programs exporting CSV should quote ID-like fields, or better, design the pipeline so Excel never touches the raw file

Strategies for very large files

CSVs of several hundred megabytes or more are common — log exports, transaction histories. Three rules for these: don’t open them in Excel (it has a hard limit of 1,048,576 rows and will hang long before that), don’t read them fully into memory, and don’t import them anywhere before confirming the structure.

Pick the tool based on what you actually need:

• Just want the shape and first rows: head -n 20 file.csv is fastest; for a proper table view, load the file (or a slice of it) into the CSV viewer, which detects the delimiter, reports row and column counts, and lets you sort by column
• Need filtering and aggregation: command-line tools like xsv or csvkit query large files directly without loading them whole
• Feeding an analysis pipeline: in Python, use pandas.read_csv(..., chunksize=100000) for chunked reading, or convert to a columnar format like Parquet first

One more note on data flow: data often travels between systems as JSON and lands on disk as CSV, and the conversion is where type information quietly dies — numbers become strings, nulls become empty cells. If you need to do that conversion, the JSON to CSV tool handles standard flat structures; check first whether your JSON is nested, because nested structures must be flattened beforehand or columns will silently lose data.

Wrapping up

Every CSV problem reduces to one sentence: it looks like text, but it behaves like a protocol. Confirm the encoding, detect the delimiter, leave quoting rules to mature libraries, defend against Excel’s type guessing, and use streaming tools for big files. Turn these into a fixed checklist for any unfamiliar CSV, and you’ll sidestep every classic trap this format has sprung on people for decades.