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

101–110 of 355 posts

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

#101

Post by chubot »

Related thread: CSVs: The Good, the Bad, and the Ugly (2020)

https://news.ycombinator.com/item?id=25014721

FWIW I have designed an upgrade to TSV as part of https://www.oilshell.org. Oil actually emits it now from 'pp proc' to pretty print "functions" and their docstrings as a table. It will be used in many parts of Oil, most of which aren't implemented yet.

It's called QTT -- Quoted, Typed Tables (formerly QTSV)

It's built on top of QSN, which is just Rust string literal notation -- https://www.oilshell.org/release/latest/doc/qsn.html

There is a stub doc which links to a wiki proposal -- http://www.oilshell.org/release/latest/doc/qtt.html

------

I think the only way that such a format becomes popular is if Oil itself becomes popular, so I haven't really emphasized it as a spec. (Similar to how JSON wouldn't be popular if the browser / JS weren't popular)

But it is a pretty easy spec -- just take TSV, add types to the column header, and specify that any cell that begins with a single quote is QSN.

A nice property is that every almost TSV file is a valid QTT file -- the exception being TSV files where a cell just a single quote, or some malformed QSN.

Note that TSV officially can't represent fields with tabs, but QTT can with '\t'.

As always feel free to contact me if you want to work on any of this

----

The ASCII field separators don't make sense -- they break tools and take you off the narrow waist of Unix. https://www.oilshell.org/blog/2022/02/diagrams.html

A key design point of QTT is that it's a special case of the "lines of text" narrow waist. CSV does not have that property, because fields can contain embedded newlines. And unlike TSV, QTT cells can contain arbitrary values.

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

#102

Post by wahern »

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…

What's the difference between quoted and escaped delimiters? (Keeping in mind that escaping sequences can themselves be escaped, ad infinitum. You can't simply seek to an escape sequence and depend algorithmically on a small, fixed lookbehind.)

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

#103

Post by [deleted] »

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

[deleted]

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

#104

Post by dataflow »

wahern wrote: post #102
LeonB wrote: post #94

Earlier quoted context omitted.

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…

What's the difference between quoted and escaped delimiters? (Keeping in mind that escaping sequences can themselves be escaped, ad infinitum. You can't simply seek to an escape sequence and depend algorithmically on a small, fixed lookbehind.)

I think the parent that if newlines were encoded as "\n" (with a backslash) then you could always split on (actual) newlines and process them in parallel without having to tokenize the quote first.

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

#105

Post by LeonB »

orasis wrote: post #20
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.

A JSON array doesn't allow easily appending a row. JSONLines is a bit better.

I remember my annoyance at XML that you couldn’t simply append a row, due to the closing tag.

I think there was some off-spec dialog called something like “appendable xml” (only snappier) that said the closing tag of the root element was optional, but it was clearly a hack.

I wanted there to be no root element at all, and the filename (or, the last part of the url) would act as the root element. Kind of weird idea in hindsight, but kind of useful too.

One downside is that there’d be no obvious way to specify attributes on the root element.

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

#106

Post by Diggsey »

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…

CSV has more problems than just quoting: one problem is that excel is so commonly used to open CSV files that various weirdnesses in excel have essentially become part of the CSV format.

For example, if a value looks like a formula, then the only way to get excel to treat it as text is to put a single quote in front of it, "'=not a formula". This in turn means that its common for values in a CSV to begin with a single quote, but for that single quote to not be intended as part of the value.

I think a decent format could be like CSV, but tab separated, where the header row is mandatory, and with explicit support for data types. Tabs and newlines (along with any other unicode character) can be included via backslash escaping.

eg. (imagine the spaces are tabs)

    int:Id  str:Name      json:Attributes    dyn:Other
    1       John\tSmith   {"age": 30}        int:42
    2       Bill          {"age": 40}        str:Hello
Applications can use their own type specifiers (eg. "formula:") to avoid needing to introduce special rules. The value of a field (before interpretation) is the exact string written in the file. The type specifier indicates how that string should be interpreted as a typed value by the application. For example, the `str` type specifier says that backslash escape sequences should be converted to the corresponding characters when interpreting the value.

This has the following properties:

- Machine and human readable and writable.

- Does not place artificial restrictions on values.

- Can be extended as needed with new type specifiers.

- Can be processed losslessly even if an application does not understand all type specifiers in use.

- Is not ambiguous about whether data is part of a header or not.

- Can store common data patterns (eg. all values in a column have the same type) efficiently without being restrictive (values can still have different types even in the same column).

- Can easily jump to a line or field (newlines and tabs not allowed except as separators - they must be escaped when in a value).

- Easy to remember.

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

#107

Post by UncleEntity »

tbrownaw wrote: post #91
LeonB wrote: post #27

Earlier quoted context omitted.

> 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

You lex the whole file, separate it into records and then process them?

Doesn’t sound like you’re processing the file in parallel to me.

TFA’s way would allow you to start at any arbitrary file position, just advance until you find a record separator then start parsing from there without having to worry about starting in the middle of a quoted string.

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

#108

Post by LeonB »

tsimionescu wrote: post #76
javajosh wrote: post #72

Earlier quoted context omitted.

What does the acronym TFA mean here?

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

I thought it was “the featured article” — in any case, it means in this context — “the linked url that this hacker news thread is discussing.”

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

#109

Post by tony-allan »

tony-allan wrote: post #95
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 for…

This also allows CSS selectors such as:

  a[data-type="int"] {
    color: green;
  }
Post reply on HN