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

91–100 of 355 posts

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

#91

Post by tbrownaw »

LeonB wrote: post #27
dossy wrote: post #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 ne…

> 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. This is a really unfair appraisal in a bunch of different ways. Removing the ability to embed record delimiters, for example, means you can process the records in parallel. That’s a massive improvement all by itself. Stating that t…

> Removing the ability to embed record delimiters, for example, means you can process the records in parallel. That’s a massive improvement all by itself.

I can process records from a csv in parallel just fine. Lexing isn't slow.

.csv stream -> tokens -> records (-> aggregate into ~100ms blocks) -> work queue with N workers

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

#92

Post by nerdponx »

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.

Pipes are usually pretty safe too.

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

#93

Post by anigbrowl »

Pxtl wrote: post #48
briHass wrote: post #9

If 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…

Mostly the problem comes from how excel is apocalyptically shitty at inferring datatypes, incorrectly assuming non-dates are dates and ISO8601 dates are god knows what, when a sane format would default to text if it didn't know better.

That kind of frustration was what drove me away from spreadsheets in the end. Someone sent me a native excel file for the first time in ages the other day and I just opened it in Pandas without thinking about it.

Pandas also gets things wrong (eg give it a column of unix timestamps with one missing value and there's a good chance it will treat them as float) and it took me a while to adapt to its vector math approach. But once I got comfortable with it working with a new pile of messy data began to feel pleasant rather than annoying.

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

#94

Post by LeonB »

donatj wrote: post #78

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

One major limitation with quoted values that can this contain record delimiters (as opposed to escaping the delimiters) is that it stops systems from being able to load records in parallel.

Some systems ban embedded record delimiters, for this reason.

Btw, I’ve (previously) included at least one of your essays in “awesome csv” list at GitHub. https://github.com/secretGeek/AwesomeCSV#essays

There’s a few specs mentioned there too — is one of those the spec you worked on?

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

#95

Post by tony-allan »

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…

This is a very cool idea but why not just add the closing tags and have a minimal format that is also valid HTML and can easily view the data by pointing your browser at the file?

It's already a universal format and as dtech mentions below, you get Excel compatibility.

I would also add headers and an optional type attribute...

  
  Age
If the file is too big then you probably need compress it or use a more appropriate format.

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

#96

Post by noxvilleza »

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…

Unsure if it was added after your comment, but there is a brief comment on parquet (in that it's binary - so it can be annoying to view/edit).

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

#97

Post by LeonB »

ubermonkey wrote: post #87

I mean, in business contexts at least, Excel is what we end up using nearly all the time because it's ubiquitous. This doesn't mean there aren't issues with it; obviously it's nonFree, and it's notorious for making assumptions about data (see recent coverage of scientists literally renaming a gene (I think?) because Excel kept assuming it was a date or something). But Excel as a tool is on so many desktops that you c…

Yeh, I think you’re spot on. It’s bad enough that lot of people will implement direct alternatives, but it’s good enough that none of the alternatives will get any traction — unless they can be “just click the file” compatible with Excel (or something at least as possible and useful as excel… a browser maybe? )

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

#98

Post by indymike »

quesera wrote: post #36
AdamH12113 wrote: post #34

I never understood why the ASCII separator characters aren't used more. It seems like we're one simple text editor feature away from having easy display and modification. Is there some historical reason for not doing that?

ASCII separators (mnemonics FS, GS, RS, US) are difficult for most users to type, and have no obvious/standardized visual representation.

Early on they were used in some serial and modem protocols, which was problematic when you used them in actual file content. I remember trying to use them in a file, and then when I tried to transfer them, the transfer would stop at RS.

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

#99

Post by dataflow »

LeonB wrote: post #94
donatj wrote: post #78

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

One major limitation with quoted values that can this contain record delimiters (as opposed to escaping the delimiters) is that it stops systems from being able to load records in parallel. Some systems ban embedded record delimiters, for this reason. Btw, I’ve (previously) included at least one of your essays in “awesome csv” list at GitHub. https://github.com/secretGeek/AwesomeCSV#essays There’s a few specs mention…

I think you still can load in parallel, but it just introduces potential for backtracking/correcting the data if you speculate incorrectly.

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

#100

Post by 1vuio0pswjnm7 »

donatj wrote: post #78

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

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

I have found these "unusual" separators are useful, e.g., I use ASCII file separator (FS). I use tr and sed to add/remove/change separators. If I had to edit a table interactively I would change the separator to something visible before editing. Thats said, in nvi(1) or less(1), US is displayed as highlighted ^_ and RS as highlighted ^^. It is not difficult to work with if the data is just text, as it is for me. One could also use a hex editor like bvi.

On large tables, I prefer to make edits non-interactively. I use ed(1) scripts.

Unfortunately, the UNIX sort command -t option will only support a limited range characters as separators. US, RS and FS are not among them. If I want to use UNIX sort I have to change the separator to one that sort accepts as a separator before sorting.

The complaints about CSV I read on HN and elsewhere seem to be complaints about what people put into them, i.e., lack of enforced rules about what is acceptable, not about the format itself.

Post reply on HN