Why isn’t there a decent file format for tabular data?
successfulsoftware.net
Why isn’t there a decent file format for tabular data?
1–10 of 355 posts
Re: Why isn’t there a decent file format for tabular data?
#2HDF5 is often used in scientific computing for this.
Re: Why isn’t there a decent file format for tabular data?
#3I don't think anyone wants tabular data. They want to ingest it into a system so they can query it, or join it, or aggregate it. They want to get rid of tabular data as quickly as possible
Data at the boundary must be validated, a file format can't do that for your. Semantics is harder than syntax and you can't push every problem to that level.
Re: Why isn’t there a decent file format for tabular data?
#4> Most tabular data currently gets exchanged as: CSV, Tab separated, XML, JSON or Excel. And they are all highly sub-optimal for the job.
> CSV is a mess. One quote in the wrong place and the file is invalid.
That breaks the other formats too, why pick on CSV? I can imagine a format designed to be friendly to syntax errors, but contra Postel's Law I'm not sure it would be an improvement over a strict, fail-fast syntax.
That's CSV/TSV's real shortcoming: about the only generic validation they allow is to make sure the column count is the same for all rows.
Re: Why isn’t there a decent file format for tabular data?
#5I don't think anyone wants tabular data. They want to ingest it into a system so they can query it, or join it, or aggregate it. They want to get rid of tabular data as quickly as possible Data at the boundary must be validated, a file format can't do that for your. Semantics is harder than syntax and you can't push every problem to that level.
The finance world runs on tables. They want to ingest it, query it, join it, aggregate it ... and look at the result in tables. Tables are everywhere, and a generic tabular format is constantly useful for a la carte one off spontaneous data processing. I love strict schema validation as much as the next guy, but sometimes you just want to cut and paste a table.
Re: Why isn’t there a decent file format for tabular data?
#6HDF5 is often used in scientific computing for this. https://en.wikipedia.org/wiki/Hierarchical_Data_Format
> This results in a truly hierarchical, filesystem-like data format. In fact, resources in an HDF5 file can be accessed using the POSIX-like syntax /path/to/resource.
That seems a whole higher level of complexity compared to CSV or the other options listed in TFA (perhaps comparable to Excel).
Re: Why isn’t there a decent file format for tabular data?
#7> Most tabular data currently gets exchanged as: CSV, Tab separated, XML, JSON or Excel. And they are all highly sub-optimal for the job. > CSV is a mess. One quote in the wrong place and the file is invalid. That breaks the other formats too, why pick on CSV? I can imagine a format designed to be friendly to syntax errors, but contra Postel's Law I'm not sure it would be an improvement over a strict, fail-fast synta…
>> CSV is a mess. One quote in the wrong place and the file is invalid.
> That breaks the other formats too, why pick on CSV?
I think it's perhaps badly worded, but the implied (and more important) criticism seems to me to be that CSV makes this kind of error much more likely, with its handling of quotes. Having worked with CSV files that had commas in the data (and sometimes quotes too), I quickly learned that I should `set readonly` on my editor and only interact with the file through programmatic tools, and give up any notion of it being a plaintext hand-editable data format.
Re: Why isn’t there a decent file format for tabular data?
#8> Most tabular data currently gets exchanged as: CSV, Tab separated, XML, JSON or Excel. And they are all highly sub-optimal for the job. > CSV is a mess. One quote in the wrong place and the file is invalid. That breaks the other formats too, why pick on CSV? I can imagine a format designed to be friendly to syntax errors, but contra Postel's Law I'm not sure it would be an improvement over a strict, fail-fast synta…
We can blame CSV, or we can blame the way people use CSV. Either way CSV is so unreliable that I try to “fail-fast” as soon as possible in automated pipeline.
At work, we explicitly define data structuring process, converting CSV to Parquet with strict schema and technical/structural validation. We assign interns and new grad engineers for this, which is nicely within their capabilities too with minimal training.
Re: Why isn’t there a decent file format for tabular data?
#9If it's tabular, I want schema for the columns. Is this column a 'number' or a string? Even better, is there a max length or precision known? Can the cell be null and how is that represented? How are dates formatted? Are they UTC/specific TZ, etc.
Most of my complaints about CSV relate to trying to determine the types used to parse or import as, not how commas are escaped. Excel, for example, actually warns you about this if you try to save a native Excel file as CSV: you are effectively throwing away information.
Re: Why isn’t there a decent file format for tabular data?
#10> Columns are separated by \u001F (ASCII unit separator)
> Rows are separated by \u001E (ASCII record separator)
Or, how about columns separated by \u002C, and rows separated by \u000A. And, for bonus points, we can even define unambiguous ways of escaping those two characters so that they CAN appear within column values, if we wanted to, and not tell people that our encoding format is totally stupid and that they need to use a different format.
OP's proposal is equally "highly sub-optimal for the job" for exactly the same imaginary reasons they dislike the currently available encoding formats, but they don't seem to realize it.