7 ms·

CSV isn't a single format but many incompatible formats that require additional metadata to decode, such as: - whether quotes are ""doubled like SQL92 or backs

by geocar 6y ago

CSV isn't a single format but many incompatible formats that require additional metadata to decode, such as: - whether quotes are ""doubled like SQL92 or backslash-\"escaped like C - whether newlines are quoted or \n like C - whether there are column headings - what the character set of the file is (Excel insists it's the local machine's codepage) - whether that's a BOM or part of a column heading - whether lines terminate with \n or \r\n or \r or \r\n - what to do about ragged rows and so on. Except for the simplest of datasets, CSV is almost certainly going to lose information. The only advantages over SQLite is streaming support and maybe compression, but even then there are better formats.

mulmen 6y ago
There's a current debate on my team if we should accept semicolon delimited files as CSVs. You can't even rely on commas in CSVs. I would say any format that is compatible with Excel is not suitable for archival. Ever open a file with numeric values with more than say, 20 (I forget the actual cut off) digits? Excel converts to scientific notation and loses precision. When you change the format there is data loss. The length data is preserved but not the full precision.
- arethuza 6y ago
  I saw a problem recently where a file had a .csv extension but was actually colon delimited. The data contained fields with large quantities of text so a CSV parser happily parsed the file splitting lines on commas in the text and didn't complain so the problem wasn't noticed until much later in the processing of the data.
  - dspillett 6y ago
    One of our clients internally uses an odd text file format with ~ as the field delimiter and a fixed-width based header & footer containing metadata. And still sometimes send such files with names ending .csv. ~ is a better choice for field separator though, it is far less commonly used in most datasets and natural languages. | can also make sense for the same reason, and visually looks like a column divider as an extra bonus.
  - Pamar 6y ago
    This is a common problem when you receive csv from someone working in a different country: comma is a decimal separator in some cases (e.g.: Germany) so Excel will automatically switch to semicolon for separators. So now your colleague in the UK office (or even someone who simply prefers using Windows in with US locale, or, as in my case, got a UK laptop because the German keyboards has some layout differences to what they consider "normal"...) double clicks on the "German" .csv... Excel will show (most of the time) every line as a single FIELD. So you have to close it and use Data Import. We just moved to the latest version of Office 365 at the job and it still works like this (even if the import action can usually figure out that semicolon is the separator without me having to manually specify it...).
- yoz-y 6y ago
  As long as you make the Filene extension .scsv Jokes aside, we had that debate in our team way back and decided to follow the rfc and only use commas. The format is often abused, better not create more tools that do so.
  - mulmen 6y ago
    Totally understand and respect that decision. In our case these files come from third parties. We ingest that data, grind it up, sprinkle on our own blend of 11 herbs and spices and serve it to customers. Rejecting that data because it doesn't strictly conform to the RFC makes our product less appetizing. Or we can hold our noses and accept any consistent delimiter while working with the data providers to follow the standards.
    - im3w1l 6y ago
      Do not sniff the format. Instead override the field separator based on source.
- arp242 6y ago
  In hindsight, semicolons are the better choice, given that commas are common in many languages as decimal separators. I don't know where there's never been some effort to create a "CSV format specification"; for example the first line could indicate quoting, delimiter, etc. style used in the file. Guess it's never been big enough of a problem for people to take action. "Good enough" (or rather, "not bad enough").
  - mulmen 6y ago
    > In hindsight, semicolons are the better choice, given that commas are common in many languages as decimal separators. Or a pipe. Or the unprintable “field separator” character. > I don't know where there's never been some effort to create a "CSV format specification"; for example the first line could indicate quoting, delimiter, etc. style used in the file. Fundamentally CSV or any delimited format requires agreement between parties for interchange. At that point standards don’t really help because the parties can just agree to anything that works. > Guess it's never been big enough of a problem for people to take action. "Good enough" (or rather, "not bad enough"). Exactly.
    - mavhc 6y ago
      It's very annoying that ascii includes file and record separator characters and csv still exists
      - dspillett 6y ago
        The problem there is those code-points are usually non-printable, making the resulting file less human-readable which is often a stated desire for a data transfer file format. Some text editors will show those code points, but others will completely omit them (or worse, reject them as invalid).
        mavhc 6y ago
        If they were something people used then editors would support them
laumars 6y ago
I used to think this too before I started researching the file format and it’s associated RFCs. Then I discovered a lot of that is actually explicitly defined, such as C-style escaping not being accepted and new lines following the MS-DOS format (\r\n). Of course, there might be programs that write invalid CSV or parsers that accept invalid CSV, but that doesn’t mean CSV isn’t a standardised format. https://tools.ietf.org/html/rfc4180 https://tools.ietf.org/html/rfc4180
- mulmen 6y ago
  > Of course, there might be programs that write invalid CSV Like Excel? RFC 4180 exists but that doesn't mean any file with .csv at the end of the name or a text/csv MIME type can be parsed according to it. Also, you still have to track the metadata, such as the charset and if the first line is a header. Both are optional in the RFC. e: RFC 4180 is not a standard.
  - laumars 6y ago
    The header should be optional. That’s a schema specific detail like the number of columns. The character set is also forgivable given the age of the format; it comes from an era when there wasn’t an agreed single character encoding to rule them all. Sure, RFC 4180 probably should have specified UTF-8 but by that point there was already three decades of CSV usage in different encodings on mainframes which are likely still in use even now to make specifying any character encoding rather pointless. Don’t get me wrong, I’m not a massive fan of CSV either. The way quoted new lines are encoded, for example, is horrible and I’ve never liked the “” format for escaping quotation marks. But a lot of CSVs biggest problems are ironically a result of its success: as a format it’s so easy to use that a great many developers can hand crank their own parsers without looking at the spec. So I find it hard being critical about the lack of standardisation in CSV when the real problem is the number of developers who don’t follow the standardisation.
    - mulmen 6y ago
      Even if RFC 4180 was a standard, which isn’t, and everyone in the world strictly adhered to it, it would still be a poor choice for archival purposes.
im3w1l 6y ago
Quotes are doubled Newlines are quoted Both having headings and not having headings is valid. Document/data specific. Use utf8 Do not use bom at all. But if for whatever reason you must use it in column heading, quote it. Idk which line ending but you should probably handle both Do not emit ragged rows.
Raqbit 6y ago
Last time I checked, Outlook (The desktop application) exported contacts in localized CSV (semi-colon separated in my case) but Outlook (online) only accepted contacts separated by commas. As I was trying to migrate contacts on someone else's computer, I had to use notepad to change it.
MaxBarraclough 6y ago
Isn't XML the obvious choice for robustly future-proof long-term data storage? Why mess about with an anemic scheme like CSV or a DBMS-specific scheme like SQLite?

by geocar 6y ago

mulmen 6y ago

There's a current debate on my team if we should accept semicolon delimited files as CSVs. You can't even rely on commas in CSVs. I would say any format that is compatible with Excel is not suitable for archival. Ever open a file with numeric values with more than say, 20 (I forget the actual cut off) digits? Excel converts to scientific notation and loses precision. When you change the format there is data loss. The length data is preserved but not the full precision.

arethuza 6y ago
I saw a problem recently where a file had a .csv extension but was actually colon delimited. The data contained fields with large quantities of text so a CSV parser happily parsed the file splitting lines on commas in the text and didn't complain so the problem wasn't noticed until much later in the processing of the data.
- dspillett 6y ago
  One of our clients internally uses an odd text file format with ~ as the field delimiter and a fixed-width based header & footer containing metadata. And still sometimes send such files with names ending .csv. ~ is a better choice for field separator though, it is far less commonly used in most datasets and natural languages. | can also make sense for the same reason, and visually looks like a column divider as an extra bonus.
- Pamar 6y ago
  This is a common problem when you receive csv from someone working in a different country: comma is a decimal separator in some cases (e.g.: Germany) so Excel will automatically switch to semicolon for separators. So now your colleague in the UK office (or even someone who simply prefers using Windows in with US locale, or, as in my case, got a UK laptop because the German keyboards has some layout differences to what they consider "normal"...) double clicks on the "German" .csv... Excel will show (most of the time) every line as a single FIELD. So you have to close it and use Data Import. We just moved to the latest version of Office 365 at the job and it still works like this (even if the import action can usually figure out that semicolon is the separator without me having to manually specify it...).
yoz-y 6y ago
As long as you make the Filene extension .scsv Jokes aside, we had that debate in our team way back and decided to follow the rfc and only use commas. The format is often abused, better not create more tools that do so.
- mulmen 6y ago
  Totally understand and respect that decision. In our case these files come from third parties. We ingest that data, grind it up, sprinkle on our own blend of 11 herbs and spices and serve it to customers. Rejecting that data because it doesn't strictly conform to the RFC makes our product less appetizing. Or we can hold our noses and accept any consistent delimiter while working with the data providers to follow the standards.
  - im3w1l 6y ago
    Do not sniff the format. Instead override the field separator based on source.
arp242 6y ago
In hindsight, semicolons are the better choice, given that commas are common in many languages as decimal separators. I don't know where there's never been some effort to create a "CSV format specification"; for example the first line could indicate quoting, delimiter, etc. style used in the file. Guess it's never been big enough of a problem for people to take action. "Good enough" (or rather, "not bad enough").
- mulmen 6y ago
  > In hindsight, semicolons are the better choice, given that commas are common in many languages as decimal separators. Or a pipe. Or the unprintable “field separator” character. > I don't know where there's never been some effort to create a "CSV format specification"; for example the first line could indicate quoting, delimiter, etc. style used in the file. Fundamentally CSV or any delimited format requires agreement between parties for interchange. At that point standards don’t really help because the parties can just agree to anything that works. > Guess it's never been big enough of a problem for people to take action. "Good enough" (or rather, "not bad enough"). Exactly.
  - mavhc 6y ago
    It's very annoying that ascii includes file and record separator characters and csv still exists
    - dspillett 6y ago
      The problem there is those code-points are usually non-printable, making the resulting file less human-readable which is often a stated desire for a data transfer file format. Some text editors will show those code points, but others will completely omit them (or worse, reject them as invalid).
      - mavhc 6y ago
        If they were something people used then editors would support them

laumars 6y ago

I used to think this too before I started researching the file format and it’s associated RFCs. Then I discovered a lot of that is actually explicitly defined, such as C-style escaping not being accepted and new lines following the MS-DOS format (\r\n). Of course, there might be programs that write invalid CSV or parsers that accept invalid CSV, but that doesn’t mean CSV isn’t a standardised format. https://tools.ietf.org/html/rfc4180 https://tools.ietf.org/html/rfc4180

mulmen 6y ago
> Of course, there might be programs that write invalid CSV Like Excel? RFC 4180 exists but that doesn't mean any file with .csv at the end of the name or a text/csv MIME type can be parsed according to it. Also, you still have to track the metadata, such as the charset and if the first line is a header. Both are optional in the RFC. e: RFC 4180 is not a standard.
- laumars 6y ago
  The header should be optional. That’s a schema specific detail like the number of columns. The character set is also forgivable given the age of the format; it comes from an era when there wasn’t an agreed single character encoding to rule them all. Sure, RFC 4180 probably should have specified UTF-8 but by that point there was already three decades of CSV usage in different encodings on mainframes which are likely still in use even now to make specifying any character encoding rather pointless. Don’t get me wrong, I’m not a massive fan of CSV either. The way quoted new lines are encoded, for example, is horrible and I’ve never liked the “” format for escaping quotation marks. But a lot of CSVs biggest problems are ironically a result of its success: as a format it’s so easy to use that a great many developers can hand crank their own parsers without looking at the spec. So I find it hard being critical about the lack of standardisation in CSV when the real problem is the number of developers who don’t follow the standardisation.
  - mulmen 6y ago
    Even if RFC 4180 was a standard, which isn’t, and everyone in the world strictly adhered to it, it would still be a poor choice for archival purposes.

im3w1l 6y ago

Quotes are doubled Newlines are quoted Both having headings and not having headings is valid. Document/data specific. Use utf8 Do not use bom at all. But if for whatever reason you must use it in column heading, quote it. Idk which line ending but you should probably handle both Do not emit ragged rows.

Raqbit 6y ago

Last time I checked, Outlook (The desktop application) exported contacts in localized CSV (semi-colon separated in my case) but Outlook (online) only accepted contacts separated by commas. As I was trying to migrate contacts on someone else's computer, I had to use notepad to change it.

MaxBarraclough 6y ago

Isn't XML the obvious choice for robustly future-proof long-term data storage? Why mess about with an anemic scheme like CSV or a DBMS-specific scheme like SQLite?