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

51–60 of 355 posts

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

#51

Post by habitue »

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 need to look at tabular data outside of a text editor anyway[0], I don't see a ton of benefit to making it a plaintext format. Especially not with the author's suggestion of un-typeable ascii delimiters. If I can't type it on my keyboard, I may as well be looking at a hex dump of a binary file because I can't really edit it.

[0] Who among us hasn't experienced the joy of a pull request updating a checked in csv file? A mess of ,,,,,,,"Birmingham",,,AL, etc.

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

#52

Post by macintux »

habitue wrote: post #46
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.

Do people really edit csvs in a text editor? It's horrific, the columns don't line up at all, empty cells are represented by a bunch of commas in a row (which, are you supposed to count all the commas?) And in terms of versioning, I have seen people commit diffs of csvs before, and they're equally unreadable. CSV is a plain text format, but that basically buys you nothing. As long as you're going to be loading it int…

I frequently use the table syntax in org-mode. No, I wouldn’t edit CSVs because they are miserable, but a friendly syntax with some basic text editor support, absolutely.

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

#53

Post by aimor »

I've done exactly what the author suggests for the exact same reasons. CSV headaches got me searching for other delimiters and I saw there were already unit and record separators, "WTF!?".

As long as you're dealing with text, and you're writing and parsing the data yourself, and you never need to use tools other than things that can be modified to set the item and line delimiters, then it's great. I haven't used the approach since then.

Mostly I haven't used it because half my use cases need binary numerical data (floats) and the others can be worked with entirely in Excel or as CSVs. But I like the idea, even if the support just isn't there.

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

#54

Post by dtech »

Pxtl wrote: post #26

> Why can’t we have a format where Does Excel support it? No? Then that's the end of that. Excel is tabular data to all non developers. The formats supported by Excel are the whole thing. And if we're inventing a CSV-like format that uses a more convenient character than quotes and commas, maybe jumping to a non-displayable non-typeable character isn't the best? Honestly, if I were inventing a table format, I'd use a…

Excel actually opens HTML tables

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

#55

Post by hedora »

barrkel wrote: post #23
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.

Doesn't have a great int64 number story, no native dates / date-times. If you want named tuples, then the names need to go everywhere, otherwise it's a serialization mechanism on top of JSON.

It also has opinions about floating point numbers.

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

#56

Post by bitmedley »

> Tab separated is a bit better than CSV. But can’t store tabs and still has issues with line endings, encodings etc.

There are three main variations of tab-separated-values files: 1) Those that don't allow tab and line endings. 2) Those that replace tab and newline characters with escaped values (\n for newline, \t for tab, \r for carriage return, \\ for backslash). 3) Those that follow the CSV convention of quoting fields as defined in RFC4180

The third option is by far the best and is what Microsoft Excel uses. Microsoft Excel has a save file type called "Unicode Text (.txt)" which saves the data as a tab-separated file using RFC4180 quoting/escaping with the UTF-16LE character encoding. In older versions of Excel, "Unicode Text (.txt)" was the only way to export any values containing Unicode characters since the "CSV (Comma delimited) (.csv)" export format uses the ANSI encoding(Windows-1252 on Western/US computers) corrupting any characters not contained in the ANSI character set. In late 2016, Microsoft finally added the "CSV UTF-8 (Comma delimited) (.csv)" option for exporting a CSV file containing Unicode characters.

https://en.wikipedia.org/wiki/Tab-separated_values

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

#57

Post by hermitcrab »

quesera wrote: post #40
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…

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.

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

#58

Post by prepend »

quesera wrote: post #40
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…

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.

I prefer commas because I can see them over tabs. I spend zero time escaping commas because the libraries and read and write with (usually pandas but pretty much everything) escape for me. So unless I’m manually building csvs myself it’s a non-issue and certainly not histrionics.

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

#59

Post by hermitcrab »

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…

Editors such as Notepad++ make it fairly easy to insert US and RS ASCII codes. But not quite as simple as typing a comma or return, obviously.

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

#60

Post by Pxtl »

prepend wrote: post #47
Pxtl wrote: post #33

Earlier quoted context omitted.

Your data must be very sparse or include a lot of escape chars for that. I've seen a lot of CSV where everything is quoted, meaning that the cell separator is effectively "," which is only one character less than and still beats the pants off of JSON or XML. imho, it would be a good compromise, in that there's already partial tooling and GUI support.

But usually it will be 1 character vs 4. So that adds a lot of space that doesn’t add much value. It’s also harder to read.

I would say invisible control characters would be even harder to read.
Post reply on HN