Why isn’t there a decent file format for tabular data?

If we are talking about machine transfer, there are tons of formats that work, and many of them are binary. xls, ods, SYLK, and dta are examples (not sure about UTF8 wit SYLK and dta). Then there are the "just write it in code" formats like SQL dumpfiles (some xml fits here, too) - which work well and carry really nice, readable type declarations.

If we are talking about human readable, text editor safe formats, we have lots of challenges. ASCII delimiter characters don't display and aren't on the keyboard (and will sometimes have side effects with old wire protocols). Nearly any character that is on a keyboard (or international keyboards) eventually becomes a problem because you will have to escape them when they are not used as a field (, is the classic example) or record delimiter (For example, can be valid inside of a longer text field). Also, many of the first 31 ASCII characters (the control characters) are used in different and surprising ways (the classic being null) by hardware and software.

BTW what makes CSV bad is that there is no escape character for , or ". Likewise, TSV has the same problem with tabs. JSON, YAML, and XML seem to be much better, but all have edge cases.

Text is text. It's stored as bits, typically 8 bits per character. It allows arbitrary precision but is very space (and memory throughput) inefficient. Fine, compression works but there are many more bits flying around and you're spending CPU cycles on decompression.

What could you do instead? Use appropriate integers, fixed point, or floating point representations. Everybody here knows these things. Nobody is pretending anything.

To the point of portability: IEEE-754 is the same everywhere.

HDF5 has some limitations that make it suboptimal for cloud based storage systems.

Zarr overcomes these limitations for array data and Parquet overcomes these limitations for tabular data.

I live in France and let me tell you: CSV is not good enough.

What about France you say? The decimal point is a coma, and to avoid confusion, the list separator is a semicolon. So "1.5, 1.6" becomes "1,5; 1,6" when localized. And if you think it is terrible for CSVs, well, it is worse than that.

If we all used RFC4180 consistently, it would work, but of course, that would be too simple. Some software insist on localization. So unless you need to know the software and locale that goes with that CSV. In the most simple case: you can treat both comas and semicolons as separators and it will work for reading, for writing, you have to pick one that the target software hopefully understands.

Hell starts when you have decimal numbers, because sometimes, you get exported CSVs where the coma is used both as a decimal point and as a separator. It makes the number ambiguous and your file becomes useless. It can result in data loss (personal experience). For example "1,2,3" may be [1.2, 3] or [1, 2.3]. And even without that, you still have to guess if you are using points and comas or comas and semicolons.

And of course, there the quoting/escaping issues every country has. Good thing the RFC has clear rules, I heard you can even find software where they are properly implemented. Of course, in France we also have the added bonus of questioning whether or not comas should be quoted since they are not the localized separator.

And character encoding? Of course it is a mess, as with every text file, but CVS makes no effort to help, is it UTF-8? Latin1? I have seen both, and I have seen files mangled because they were opened with the wrong encoding. Also, line endings should be CRLF, for those who care.

CSV as defined by the RFC is good enough, the problem is how it is used in practice.

> But it is binary, so can’t be viewed or edited with standard tools, which is a pain.

I've heard this sentiment expressed multiple times before, and a minor quibble I have with it is that the fact that it's binary has nothing to do with whether or not it's a pain. It's a pain because the tools aren't ubiquitous, so you can't count on them always being installed everywhere. But I'd argue that sqlite _is_ ubiquitous at this point and, as others have mentioned, it's a _great_ format for storing tabular data.

JSON is also a fine choice, if you want it to be human readable, and I'm not sure why this is claiming it's "highly sub-optimal" (which I read as dev-speak for 'absolute trash'). JSON is extremely flexible, compresses very well, has great support for viewing in lots of editors, and even has a decent schema specification. Oh, and line-delimited JSON is used in lots of places, and allows readers to begin at arbitrary points in the file.