A CSV that opens as a single column is almost always a separator disagreement. The file uses commas and the spreadsheet expects semicolons, or the reverse — and both are correct, because the format has no standard that says which.
The document usually cited, RFC 4180, describes common practice rather than mandating it. It arrived decades after the format was already everywhere, and it says so itself.
Why would anyone use a semicolon?
Because in much of Europe the comma is the decimal separator. A file containing 1.234,56 cannot also use commas between fields without ambiguity, so the regional convention became the semicolon.
Spreadsheet software follows the system locale when deciding which to expect, which is why the same file opens correctly on one machine and as one column on another. Nothing about the file changed.
Tab-separated data avoids the argument, since a tab appears in almost no real field. It is the safest of the three and the least used, because the format is named after the comma.
How does quoting work?
A field containing the separator, a quote or a line break must be wrapped in double quotes, and a quote inside a quoted field is written twice.
| Field value | Written in CSV |
|---|---|
| Smith, John | "Smith, John" |
| 6" pipe | "6"" pipe" |
| two lines |
"two⏎lines" |
The third row is the one that breaks naive parsers. A quoted field may contain a real line break, so a CSV file cannot be processed by splitting on newlines — the row continues until the quotes close, which means correct parsing requires reading character by character.
It is also why a file that looks fine in a text editor loses rows on import. The rows are there; the reader stopped at the wrong newline.
What does a spreadsheet destroy on open?
Leading zeros and anything shaped like a date. A postcode, a product code or a phone number beginning with zero is read as a number, the zero is dropped, and the file is saved back without it.
Date coercion is the more famous failure. Text that resembles a date is converted on import, and the conversion is not reversible from the result — the original string is gone. It has been a large enough problem in genetics that gene naming conventions were formally revised in 2020 to stop identifiers being eaten by spreadsheets.
The safe path is to import rather than open, declaring the troublesome columns as text. Converting the file elsewhere avoids the question altogether, which is the argument for doing the transformation before the spreadsheet sees it.
Why does the first header look wrong?
Because CSV carries no encoding declaration. A file is bytes, and the reader guesses — so a file written as UTF-8 and read as something else shows mangled accented characters throughout.
The workaround the industry settled on is a byte order mark at the start of the file, which some readers use as an encoding hint and others include in the first header name. That is the extra invisible character at the front of the first column, and it is why a lookup on the first header fails while every other column matches.
Neither behaviour is wrong. The format simply has nowhere to record what it should have recorded.
Column names travel badly too. A header containing a space, a hyphen or an accent is legal in CSV and awkward everywhere downstream, so a conversion step that normalises headers to plain identifiers saves the quoting argument later.
What survives the trip to SQL?
Less than you would like, because CSV has no types. Every field is text until something interprets it, so generating INSERT statements means deciding what each column is — and deciding what an empty field means.
An empty field is either an empty string or a missing value, and the file does not distinguish them. Writing them all as empty strings loses the distinction; writing them all as NULL invents one. Only the person who exported the data knows which is right.
Escaping is the other half. An apostrophe inside a value closes the string early unless it is doubled, and a value that was never checked is the classic injection shape — which is why generating statements with the escaping already handled is safer than assembling them by hand.
Questions people ask
Should I trim whitespace around fields? The convention says space after a separator is part of the field. Most readers trim it anyway, so do not rely on either behaviour.
Does the header row have to exist? No, and nothing in the file says whether it does. A first row of column names is a convention the reader has to be told about.
What line ending should I use? Carriage return plus line feed is the most compatible for spreadsheets. Line feed alone works everywhere else.
Can a CSV have blank lines? Yes, and readers disagree about whether they are empty rows or nothing. Strip them before sharing the file.
Why is Markdown different? A Markdown table has no quoting rules at all, so a pipe inside a cell must be escaped and a line break cannot be represented.
Convert it where the rules are explicit. CSV to JSON and JSON to CSV handle the quoting in both directions, text to CSV builds a file from pasted columns, the CSV to SQL insert generator and SQL to CSV cross the type boundary, the SQL formatter and minifier make a long statement readable, and the Markdown table formatter aligns the version you paste into a document.