QUESTION :
While trying to find out how to get CSVs to open correctly in Excel on a Spanish PC (belonging to a customer) I found many answers recommending using sep=, at the top of the file:
- Easiest way to open CSV with commas in Excel
- How to control CSV import into Excel 2010
- How to open semicolon delimited CSV-files in US-version of Excel
- Space or tabs as separator in CSV
- How to get Excel to interpret the comma as a default delimiter in CSV files?
On the face of it, this approach works but none of those answers give any further information on where this metadata option comes from. I have tried to search for what it means (and hence found all of those answers) but have been unable to get any further information especially given that punctuation is notoriously hard to search for.
My primary concern is whether or not this is an Excel-specific feature. I suspect it is but have found nothing definitive to confirm it.
Some related questions are:
- What characters can be used in this setting?
- What other settings are available (eg. line termination character, quote character, etc).
- Are there any other tools that officially support this feature?
I’m hoping there is a piece of documentation somewhere that someone can point me to that will answer all these questions and more; I just haven’t been able to find it.
Some clarification:
The sep= is not a parameter to a parser. It is meant to be placed inside the CSV. Example:
sep=|
"LETTER"|"ANIMAL"
"a"|"aardvark"
"b"|"bear"
"c"|"cow"
ANSWER :
RFC 4180 is commonly recognized as the standard for the CSV format, and does not mention any such feature.
W3C Model for Tabular Data and Metadata on the Web mentions it as a feature not within their scope:
Many “CSV” files embed metadata, for example in lines before the header row
of the CSV document. This specification does not define any formats for
embedding metadata within CSV files, aside from the names of columns in the
header row.
So while I did not find any direct proof that this feature is Excel-specific, this apparently still is the case as there seems to be no standardization document covering that feature.
To contribute towards an answer to one of your questions:
“Are there any other tools that officially support this feature?”
This is not supported by Apple Numbers (I’ve tested using Numbers version 3.6.2).
It’s also not supported by csv2json (https://www.npmjs.com/package/csv2json) nor csvtojson (https://www.npmjs.com/package/csvtojson).
I tested this as well on Google Sheet and Google does not support it for the moment. But Google Sheet does not process CSV files correctly if a “;” is used as separator.
I personal think it is much easier for tool vendors to analyse the CSV file and identify the separator automatically. I did not find a tool yet which do this 🙁
The instruction sep= is used in all the countries where comma is used as decimal delimiter, as Italy. Since Excel export CSV using semicolons instead of commas in those countries, if you want to be sure that the file can be read in USA or other countries, you NEED
sep=;
on top of file. This is Excel-specific and it might be ignored by other applications.