CSV Pitfalls: Delimiters, Quoting and Encoding

The classic symptom is a file that opens with everything crammed into one column. That means the program guessed the wrong delimiter — it split on commas when the file used semicolons, or vice versa. European locales are a frequent cause: because many countries write decimals as 3,14, their spreadsheets export with semicolons to avoid ambiguity. Tabs and pipes appear too. The fix is simply to tell the tool which delimiter to use, or to rely on auto-detection that inspects the file and picks the separator giving the most consistent column count.

Two details make the mismatch worse than it needs to be. Excel on Windows writes and reads CSV using the list separator from the regional settings, so the same spreadsheet saved in Germany and in the United States produces two different files from identical data. Excel also honours a line reading sep=; at the very top of a file, which sets the delimiter for Excel but belongs to no CSV convention — every other parser reads it as a one-column first row. If you find such a line, drop it once the delimiter is set correctly.

The second trap is quoting. A value that contains the delimiter, a quote character or a line break must be wrapped in double quotes, and inner quotes must be doubled. A naive parser that splits each physical line on the delimiter will mangle any such value — a product description with a comma becomes two columns, and a multi-line address is torn across rows. This is why DataForge uses a proper CSV parser that understands quotes and embedded newlines, so a value like "123 Main St, Apt 4" stays in one cell exactly as intended.

Two failure modes are worth recognising on sight. An unbalanced quote — an opening quote with no closing partner — makes a parser swallow everything after it as one enormous value, so the symptom is a file that collapses into a single row somewhere in the middle. The other is an exporter that escapes with a backslash instead of doubling: MySQL writes a backslash before an inner quote by default where RFC 4180 expects two quote characters. A parser built for doubled quotes misreads those rows, and the right answer is to re-export with the expected quoting rather than patch the text by hand.

Every record should hold exactly as many fields as the header. When one does not, you have a ragged row, and each tool reacts differently: pandas raises a ParserError naming the offending line, a spreadsheet silently spills the extra value into a new column, and a hand-written script shifts every later value one place to the left so that dates land in the price column. The field count is the symptom; something else is the cause.

The causes are a short list: an unescaped delimiter inside a value, a line break in a field that was never quoted, a trailing delimiter at the end of the line (which adds one empty field to that row alone), and the decorations exports like to add — a title line above the real header, a blank spacer row, a totals row at the bottom, or a header repeated every fifty lines by a paginated report. Find the line number first and open that single line; the cause is almost always visible at a glance.

Opening a CSV by double-clicking it in Excel is not a read-only act — the values change. Leading zeros disappear, so 00123 becomes 123 and the postal code 02134 becomes 2134. Numbers longer than fifteen digits lose precision and are then displayed in scientific notation, which quietly ruins long order numbers and account ids. Text that resembles a date is converted outright: 1-2 becomes the second of January. Genetics knows this problem well — in 2020 the HGNC renamed several human gene symbols because spreadsheets kept turning them into dates.

The way round it is to import rather than open: use Data, then Get Data from Text/CSV, and set the type of the fragile columns to Text before loading. Saving matters too. In Excel the option named CSV UTF-8 writes UTF-8 with a BOM, while the plain CSV option writes the system code page — windows-1251 on a Russian Windows, windows-1252 on a Western European one. A file saved that way looks fine on the machine that made it and arrives as mojibake anywhere else, which is where a great many encoding complaints begin.

The third culprit is encoding. If Cyrillic, accented letters or emoji turn into question marks or strange symbols, the file was saved in one encoding and read in another. The modern default is UTF-8, which represents every character correctly, and reading files as UTF-8 solves most cases. A related nuisance is the byte-order mark (BOM) — a few invisible bytes some programs add to the start of a file, which can attach themselves to your first column name. When a header looks subtly wrong, an invisible BOM is often the reason.

When something looks off, load the file and read the preview and delimiter chip. If the columns line up and your text is legible, the parse succeeded; if not, switch the delimiter and reload before doing anything else.