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

231–240 of 355 posts

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

#231

Post by bryanrasmussen »

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…

>xml -- Useful if you are programming in early 2000s

I guess nobody knows about document formats anymore.

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

#232

Post by fiddlerwoaroof »

SkyPuncher wrote: post #194
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…

Most csv utilities support an alternative delimiter. If I need to edit a file by hand, I'll typically pick an uncommon character for the delimiter (pipe "|" works well since it's uncommon). For me, that pretty much entirely eliminates any of the pain with CSV.

More tools should use the ASCII unit and field separator characters intended for this purpose: https://ronaldduncan.wordpress.com/2009/10/31/text-file-form...

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

#233

Post by lordnacho »

wodenokoto wrote: post #215
PeterisP wrote: post #203

Earlier quoted context omitted.

I've seen ubiquitous use of tab-separated value files instead of csv, as as simpler format without quoting support and a restriction that your data fields can't contain tabs or newlines, which (unlike commas) is okay for many scenarios.

But that’s sort of the problem with csv. You never really know which rules your csv files has. Many .csv files are indeed tab separated.

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.

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

#234

Post by zo1 »

bryanrasmussen wrote: post #231
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…

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

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

#235

Post by omegalulw »

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…

One thing of note is that there isn't a single format that's optimal for storing tabular data - what's optimal depends on your use case. And if performance doesn't matter much, just use CSV.

As a simple example, column based formats can significantly speed up queries that don't access the full set of columns. They can come in handy for analysis type SQL queries against a big lump of exported data - where different users query the subset they are interested it.

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

#236

Post by lelanthran »

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

>> 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?

It is. When I needed to edit a file with field separators (a little tool I made used field separators) I found that Vim was great, because I could copy an existing character into a specific register, and then never use that register for anything else.

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

#237

Post by lelanthran »

MobiusHorizons wrote: post #217
thayne wrote: post #202

Earlier quoted context omitted.

So vim with a plugin isn't a text editor?

I just mean, if you _require_ plugins in order to be able to edit the content, then the content can't easily be described as text. It is fine to use a specialized application to edit a file of a non-text format, I have nothing against that, but you have then left the realm of text editor. As an example of what I mean, if someone wrote a vim plugin that allowed a user to interact with a sqlite file and change the sche…

> I just mean, if you _require_ plugins in order to be able to edit the content,

Vim doesn't require plugins (as long as you are editing an existing file, and not trying to create a new one).

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

#238

Post by thayne »

MobiusHorizons wrote: post #217
thayne wrote: post #202

Earlier quoted context omitted.

So vim with a plugin isn't a text editor?

I just mean, if you _require_ plugins in order to be able to edit the content, then the content can't easily be described as text. It is fine to use a specialized application to edit a file of a non-text format, I have nothing against that, but you have then left the realm of text editor. As an example of what I mean, if someone wrote a vim plugin that allowed a user to interact with a sqlite file and change the sche…

The described format _is_ editable in vim without a plugin. It's just a little awkward, because everything will be on a single line, and you have to use more complicated key commands to enter the delimiters (for example `` for \x1e).

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

#239

Post by Someone »

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

>> 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 think it's perhaps badly worded, but the implied (and more important) criticism seems to me to be that CSV makes this kind of error much more likely, with its handling of quotes. Having worked with CSV files that had commas in the data (and sometimes quotes too), I quickly learned that I sho…

Indeed, it’s because people use an editor that isn’t designed for editing csv to edit csv.

Every csv files is a text file, but not every text file is a csv file, but people use text editors to edit them.

A csv editor would forbid you from entering that quote, automatically add a matching one, ask you for your intentions, or whatever, but it shouldn’t silently corrupt your file.

A spreadsheet-like UI, but without formulas, automatic single header row, etc. would be a better UI for csv files.

If you use zip to edit a .xlsx or .docx file, you easily get an invalid file, too.

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

#240

Post by omnicognate »

> Why can’t we have a format where:

>

Erm, we do. You just defined it and gave it a name. There's nothing stopping you using it in your software. Persuading others to use it will be trickier given the existing widely-supported options really aren't so bad, but you could give it a go.

Post reply on HN