Hacker News Reader

Comment by mulmen | Hacker News Reader

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.

View on HN

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.

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.

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...).

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").

> 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.

It's very annoying that ascii includes file and record separator characters and csv still exists

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).

If they were something people used then editors would support them

How do you type those in modern text editors?

You used to be able to ctrl + letter to input control keys on traditional terminals — was it (ord(char) - 0x20), but outside Emacs, vi and similar editors, I am sure you can't do that anymore.

If you are questioning existence of human-readable/writeable (typeable?) data formats, you might want to check out SGML/XML, JSON, YAML... Are you surprised that being able to type it out holds true for all of these? Esp on standard keyboards?

It's more interesting how SGML-derived languages have kept <> symbols so accessible on keyboards regular people use.

> I don't know where there's never been some effort to create a "CSV format specification"

RFC 4180 (https://tools.ietf.org/html/rfc4180) "Common Format and MIME Type for Comma-Separated Values (CSV) Files"

That's not exactly what I mean; that's just one of many different CSV variants (and just because it's an RFC doesn't make it "official" either). What I mean is something like:

  $ cat file.csv
  CSV,quote=always,quotechar=",header=yes,delim=,
  Col1,Col2
  "val","val2"
  [..]

Then you always know what you're dealing with, unambiguously.

There are other cases where embedding some "metadata" in CSV files is useful; for example the exports I create in my app are versioned by prefixing the first header with the version number. It works, but it's not exactly great.

Of course, the downside is legacy software not being able to process it.

There are CSV specs - see https://specs.frictionlessdata.io/csv-dialect/ and https://github.com/csvspecs , for example. Note: Both initiatives are ongoing and alive and kicking. The trouble is almost nobody cares - as your ignorance shows - just complaining and playing the know-it-all is so much easier and more fun, isn't?

You seem like a nice person. Want to be friends?

Or a tab. It's what tabs are for, after all.

Or ASCII 0x1C, 0x1D, 0x1E, and 0x1F. Because those are in the spec exactly for records based files.

I've never understood why these characters aren't used more frequently.

Let's call it ASV - ASCII Separated Values.

FYI: Find some CSV format specifications (and tests) @ https://github.com/csvspecs

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.

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.

Do not sniff the format. Instead override the field separator based on source.

Reply on news.ycombinator.com