Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

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

successfulsoftware.net

Post reply on HN
Print view View original

71–80 of 355 posts

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

#71

Post by hermitcrab »

twobitshifter wrote: post #62
hermitcrab wrote: post #31

Earlier quoted context omitted.

No doubt binary formats like Parquet are the way to go for high performance with multi-GB datasets. Seems like total overkill if you have a few hundred or thousand rows of data though. Being able to create/edit/view stuff in a text editor and easily version it is very useful.

It’s strange that we pretend text is not binary. The truth is our many tools are set up to handle binary text data and these tools are not set up for alternate encodings. If you grab a text file from a Windows machine and bring it to a mac, you’ll see that txt is far from perfect. This is a long way of saying that if we develop both the format and the tooling then the distinction of text vs “binary” tabular data goes…

Fair point. But the vast majority of programming tools do already handle UFT8 text (although perhaps don't do a great job with US/RS characters).

also you could write an efficient parser for US/RS separated data in 5 minutes. For parquet you would have to integrate with a library (with all it's dependencies and design choices) or spend days writing your own parser.

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

#72

Post by javajosh »

nescioquid wrote: post #21
Justsignedup wrote: post #15

I literally don't get why JSON is bad: [{row1}, {row2}, {row3}] The fact that it can do more is in no way a negative. Can even make a limited JSON parser with reduced capabilities. And with JSON can do more definitions like header names vs column names vs just arrays of arrays.

From TFA: >> XML and Javascript are tree structures and not suitable for efficiently storing tabular data (plus other issues).

What does the acronym TFA mean here?

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

#73

Post by kall »

prepend wrote: post #38

I think it’s because csv is good enough. All the standards I’ve seen haven’t been worth the effort to implement. So since csv, with all its flaws, is good enough it crowds out other open standards. People complain about it, but it’s not really much of a challenge to use csv. I’d also prefer it over the crap (rdf, xml, even schemad json) proposed by people who value more structure. It’s easier for me to just make clea…

Yeah I agree. Just the combination of opening in excel on double click and being dead simple to spit out from any software makes it a winner. It helps that it's usable in plain text too.

If it would have used ANYTHING other than half the world's decimal separator as a value separator, it would be a no brainer. If it had been .psv (pipe separated values) the edge cases would be so few that we could just ignore them.

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

#74

Post by emef »

habitue wrote: post #51

There is a decent file format for tabular data, and the author dismisses it: parquet. It's compact, encodes all the common data types well, does int/float distinction (thanks for teaching us about how important that is json), stores null records with a mask instead of a special value, row major order, has compression, speedy random access... it has it all. And it isn't bogged down with legacy cruft (yet). Since you n…

parquet is great but it's not particularly easy to read or write. the libraries that do exist to work with it are few and far between, and those that do either have a hundred dependencies or depend on native code (e.g. libarrow). certainly an important dimension in an ideal file format should be the ease of parsing/writing it, and parquet gets an extremely low score on that front imo

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

#75

Post by rileytg »

simonw wrote: post #70

SQLite is one of the few file formats that's recommended by the US Library of Congress for archival storage: https://www.loc.gov/preservation/digital/formats/fdd/fdd0004... See also this page on the SQLite website (they're understandably very proud of this): https://www.sqlite.org/locrsf.html I think it's a fantastic format for archiving and distributing data.

sqlite also has roots in the us military (navy iirc). this could explain the US govs willingness to adopt.

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

#76

Post by tsimionescu »

javajosh wrote: post #72
nescioquid wrote: post #21

Earlier quoted context omitted.

From TFA: >> XML and Javascript are tree structures and not suitable for efficiently storing tabular data (plus other issues).

What does the acronym TFA mean here?

"The Fine Article" (though more traditionally, and/or depending on tone, "The Fucking Article").

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

#77

Post by mjevans »

hermitcrab wrote: post #57
quesera wrote: post #40

Earlier quoted context omitted.

I think CSV is crappy because commas are so common in real data. For almost all scenarios I've had to work with, I'd have been perfectly happy with TSV where literal Tab was a disallowed character. No escaping histrionics required.

Not being able to include Tabs and Carriage Returns in your data can be a problem though.

Depending on the library, (double) quoted fields _may_ be able to contain carriage returns (0x13) and/or line feeds (0x10).

Sometimes it works, sometimes it doesn't, can be a real pain in the butt.

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

#78

Post by donatj »

> Columns are separated by \u001F (ASCII unit separator) > Rows are separated by \u001E (ASCII record separator)

That's a nightmare to try to edit yourself in a text editor?

I'd rather just have basically TSV, but with every value always quoted, always UTF-8. Quotes escaped with backslashes, backslashes escaped with backslashes, and that's it. Any binary allowed between the quotes.

I deal with CSVs all day every day. I'm known for these two posts

https://donatstudios.com/Falsehoods-Programmers-Believe-Abou...

https://donatstudios.com/CSV-An-Encoding-Nightmare

Some friends and I actually started an RFC about 11 years ago for a CSV enhancement with an HTTP inspired header section with metadata including encoding. UTF-8 wasn't as clear of a winner back then. Never went anywhere.

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

#80

Post by LordDragonfang »

dossy wrote: post #11
hirundo wrote: post #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 synta…

> 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. Once upon a time, when I was doing a lot of data interchange between a wide variety of systems (OS'es, applications, etc.) I considered proposing an "enhanced CSV" (ECSV) where the values did not start on the second row in the file, but instead the second row would be regular ex…

HN actually does support markdown code blocks, you just have to preface lines with four (nope, two)[1] spaces instead of the (newer) triple-backtick codefences.

  ID,NAME,DATE
  "/^\d+$/","//","/^\d{4}-\d{2}-\d{2}$/"
  867,Alice,1984-01-09
  5309,Bob,1981-11-16
[1] apparently it's actually two spaces instead of the normal markdown standard of four, making HN even more non-standard than usual

https://news.ycombinator.com/formatdoc

Post reply on HN