What is a CSV file, and why does yours open wrong?

A CSV is a plain text file holding a table. One line per row, a comma between each column, and that is genuinely the whole idea. You can open one in Notepad and read it. Its entire reason for existing is that every database, spreadsheet and programming language on earth can read and write it without agreeing on anything else.

The trouble starts with the comma, because data contains commas.

A correct file, read wrongly

This is a valid CSV with three rows. An address contains a comma, and one note contains a line break, both of which the format allows by wrapping the value in quotes:

name,address,note
Ada Lovelace,"12 Pall Mall, London",fine
Grace Hopper,"Line one
Line two",embedded newline

Run on 2026-09-12, a proper parser returns three rows of three columns. Now the two shortcuts almost everyone writes first:

• Split the file on newlines and you get four records, not three, because Grace Hopper's note is cut in half.
• Split a row on commas and Ada's row gives four columns, not three, because the address breaks in two and the quotes are left dangling on the pieces.

Nothing errors. You get a table that is the wrong shape and looks plausible, which is worse than a crash, because it can be saved and sent onward.

The comma is not always a comma

In much of Europe the decimal separator is a comma, so 1,5 means one and a half. A comma cannot then also separate columns, and Excel in those locales writes semicolons instead. The file is still called .csv.

So a CSV exported in Germany and opened in the UK arrives as a single column with every row jammed into one cell. Tab separated files (.tsv) and pipe separated ones exist for the same reason. A converter worth using works out the separator from the file instead of assuming, which is what ours does: it counts each candidate in the header line, ignoring anything inside quotes, and takes the most common. The header specifically, not the whole file, because one free-text column full of commas would outvote the semicolons on every other column of a European export.

What Excel does to your data on the way in

A CSV has no types. Every value is text, and the program opening it guesses what each one is meant to be. Excel guesses aggressively and does not tell you:

• 007123 becomes 7123. Leading zeros are not a number, so product codes, zip codes and account numbers lose them.
• A 16 digit reference becomes a rounded approximation, because it is past the largest whole number a spreadsheet holds exactly.
• 1-2 and 3/4 become dates. Gene names and part numbers have been losing this fight for twenty years.
• A value starting =, + or - is treated as a formula. A phone number written +44 7700 900123 is an expression, not a string, and this is also a real security issue: a CSV from an untrusted source can carry a formula that runs when somebody opens it.

None of that is the CSV being broken. The file holds exactly the characters that were written into it. It is the spreadsheet deciding what they mean, which is why converting to .xlsx with the types stated explicitly survives where handing Excel a .csv does not.

Is there a standard?

There is one, RFC 4180, and it was written in 2005, decades after the format was already everywhere. It is a description of common practice rather than a rule anybody had to follow, so plenty of real files disagree with it about line endings, quoting and whether a header row exists at all. Assume the file you have is close to it and not exactly it.

filetity is built by Adarsh Mishra. The parser output above is from running that file on 2026-09-12; paste your own into the viewer and it will tell you how many columns it really found. Corrections welcome: support@filetity.com.