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

281–290 of 355 posts

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

#281

Post by mavhc »

leeoniya wrote: post #153
briHass wrote: post #150

Earlier quoted context omitted.

You are limited to the basic types (int, floating point, string, and blob), however. I can somewhat get behind the opinionated argument for not needing more specific types like most common language types, but not the lack of a date type.

> but not the lack of a date type. i've also found this to be truly bizarre. even more bizarre than not actually respecting (via coercing or error) to the specified type...why even have types, then?

Got to pick a date format first, what date it is depends on what calendar you use, and where you are in the world. That's before you add time.

So you could base it on UT1/TAI, but then that ignores clocks in space.

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

#282

Post by kbouck »

Brometheus wrote: post #255
lordnacho wrote: post #233

Earlier quoted context omitted.

Gotta wonder why the format isn't just a column separator char, a row separator char, and then all the data guaranteed not to have those two chars. Then you could save the thing by finding any two chars that aren't used in the data. I guess this is why we have a zillion formats.

ASCII Code 29: Group Separator ASCII Code 30: Record Separator ASCII Code 31: Unit Separator https://theasciicode.com.ar/ascii-control-characters/record-...

this doesn't directly address the ease-of-editing concern, but if you have csv (or tsv) and want ascii (0x1f, 0x1e) or unicode(U+241F, U+241E) separated (or vice versa), 'miller' [0] is a command line tool that supports converting among these formats with these one-liners:

    mlr --icsv --oasv cat input.csv  # csv to asv
    mlr --iasv --ocsv cat input.asv  # asv to csv
[0] https://miller.readthedocs.io/en/latest/

[1] https://miller.readthedocs.io/en/latest/file-formats/#csvtsv...

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

#283

Post by zoomablemind »

pmontra wrote: post #230
zoomablemind wrote: post #180

Earlier quoted context omitted.

What's so special about having a named type for datetime? User will still need to call functions to manipulate the dates. If only for the default display and import?

I've seen a sqlite database with datetimes in three different formats in the same field, because different parts of the application I inherited had different ideas of how to write a datetime and sqlite accepts everything. It's only a string after all. That's a mistake that the same bad developer couldn't have done with a PostgreSQL or a MySQL.

I understand that SQLite can store datetime in REAL type no problems, just on import one needs to convert the date strings properly. Of course, stuffing strings verbatim may work too, but that's about the robustness of the load process or the ETL pipeline.

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

#284

Post by alpaca128 »

m_eiman wrote: post #241
lordnacho wrote: post #233

Earlier quoted context omitted.

Gotta wonder why the format isn't just a column separator char, a row separator char, and then all the data guaranteed not to have those two chars. Then you could save the thing by finding any two chars that aren't used in the data. I guess this is why we have a zillion formats.

Then you can't edit or view it in a normal text editor, which is part of the appeal of CSV.

That and the fact that the comma (or semicolon, as I prefer it due to the rare usage) is on every keyboard.

A text editor can be made to handle those separators, but editing or processing the file can be more cumbersome than csv and tsv.

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

#285

Post by petepete »

zo1 wrote: post #234
bryanrasmussen wrote: post #231

Earlier quoted context omitted.

>xml -- Useful if you are programming in early 2000s I guess nobody knows about document formats anymore.

They are re learning those lessons slowly. I.e. OpenAPI and json schema are pretty much poor re implementations of SOAP and XSD but for json. I don't want to be that get off my lawn guy but it's laughable how equivalent they are for 99% of daily use cases.

Every time I hear someone talking about validating JSON I just think about how, despite its flaws, XSD is actually pretty decent despite being 20 years old.

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

#286

Post by Diggsey »

masklinn wrote: post #272
Diggsey wrote: post #106

Earlier quoted context omitted.

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

> - Can be extended as needed with new type specifiers. That's often not a good thing, at least if that allowance is given to third parties, as it wildly opens up the format to effective incompatibilities (fragmenting the ecosystem) and is a never ending source of security issues.

This assumes that if you don't include such capability, that applications won't find a way to extend it on their own regardless of the spec, and that's empirically false.

There's a reason the vast majority of file formats do have some capacity for extension. By providing this capability you avoid much worse hacks (the devil you know and all that...) and you can ensure certain invariants are upheld (such as applications being able to process records losslessly even if they don't understand the format specifier)

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

#287

Post by dukoid »

asperous wrote: post #126

Seems like the problem here is there is several high quality and well-developed formats, but the author and the commenters here dismiss them because of the different trade-offs they make. csv -- Simple for simple use cases, text-based, however many edge cases, feature lacking etc xlsx -- Works in excel, ubiquitous format with a standard, however complicated and missing scientific features sqlite -- Designed for relat…

Surprised that nobody has mentioned ARFF: https://www.cs.waikato.ac.nz/ml/weka/arff.html

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

#289

Post by joshka »

Amazon ION perhaps?

https://amzn.github.io/ion-docs/guides/cookbook.html#convert...

As for why, the rationale is that tabular data often needs more than the things that the article decries (e.g. efficient parsing / processing of partial records, sparse values, richly typed values, flexibility of schema evolution (i.e. not having to assume specific columns upfront)...

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

#290

Post by hermitcrab »

hoseja wrote: post #264
eru wrote: post #249

Earlier quoted context omitted.

What you suggested works for fixed width fields only, doesn't it?

Yeah, true. Still, one or more bytes of overhead per cell and row/column seems wasteful.

Fixed width formats are only efficient when everything in a column is a similar length. You only need a single 1000 char data value in a column to mean either the whole column is 1000 chars or the data is truncated.
Post reply on HN