Established onRFC 4180, informational, 2005Excel 365: regional list separator, sep= line honouredGoogle Sheets, web: comma assumed
There is a document describing comma-separated files, published in 2005. It is explicitly informational: it records what implementations commonly do rather than requiring anything, and it says in its own text that practice varies.
So a file with this extension is not a file in a format. It is a file in whichever of several conventions its producer happened to use, and the reader has to guess which.
What varies
The separator. Comma, semicolon, tab and vertical bar are all in common use, and the file does not say which.
Quoting. Whether fields are quoted always, only when necessary, or never. What counts as necessary. How a quote inside a quoted field is escaped, by doubling it or by preceding it with a backslash.
Line endings. The description above specifies one combination; a great deal of software emits another.
A header row. Optional, and undeclared.
The encoding. Not stated anywhere in the file, which the entry on encodings in this part is about.
Types. There are none. Every field is characters, and what they mean is decided by whoever reads them.
The variation that catches organisations
In places where the comma is the decimal separator, Excel writes and expects semicolons in files it calls comma-separated, because it uses the operating system's list separator rather than a comma.
The consequence is that a file exported from Excel by a colleague in one country opens as a single column of text for a colleague in another. Nothing is broken, no setting is wrong, and both installations are behaving as documented.
The non-standard escape hatch
Excel honours a first line consisting of the word sep, an equals sign and the separator character, and uses that instead of the regional setting. It is not in the description above, it is not understood by most other software, and it is the only way to make a file open correctly in Excel regardless of where it is opened.
Which is a fair summary of the whole subject: the practical answer is a vendor-specific line that the specification does not mention.
What to do when producing a file
State the convention out of band, in the file name, in the documentation, or in an accompanying note: which separator, which encoding, whether there is a header, and how quotes are escaped. Four facts, and the file cannot carry any of them.
Prefer tab as a separator where the choice is yours. Tabs appear in data far less often than commas or semicolons, which reduces the amount of quoting and therefore the number of ways a reader can go wrong.
What to do when receiving one
Open it in a text editor first and look at the first three lines. That answers the separator, the quoting and the header question in about five seconds, and it is the single most useful habit in this part of the site.
Then import rather than open, which the entry on leading zeros covers, so that the types are declared rather than guessed.
The alternative formats, briefly
A tab-separated file has the same absence of types and fewer quoting problems. A file with one record per line in a structured notation carries types and nesting and is not readable by a spreadsheet without tooling. A columnar analytical format carries types and compresses well and is not readable by a spreadsheet at all without an add-in.
The reason the untyped convention persists despite all of this is that everything can read it, which is the same argument that has decided several of the older decisions in computing and is not a bad one.
What we cannot verify
The description document is public and can be read, including its own statement about variation. The regional separator behaviour and the first-line override are documented by the vendor and were confirmed on a current build. The prevalence of each separator in the wild is not something we have measured and no figure is offered.
Why a spreadsheet is a bad place to keep one
Opening a delimited file in a spreadsheet and saving it back is a lossy round trip. The types the reader guessed are written back out as whatever they became: the identifier that lost its leading zero is now genuinely without one in the file, and the date that parsed in the wrong order is now written in the wrong order.
Which means the file that arrived and the file that leaves are different data, and nothing in the workflow marks the moment. Where a delimited file is a transfer between two systems, the spreadsheet should be a viewer and not a stop on the route.
In short
- The document describing these files is informational and says practice varies.
- Separator, quoting, line endings, header and encoding are all undeclared.
- There are no types, so every field is characters until a reader decides otherwise.
- Excel uses the regional list separator, so a file from another country opens as one column.
- A first line declaring the separator works in Excel and is not in the specification.
- Open it in a text editor and read three lines before anything else.