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

341–350 of 355 posts

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

#341

Post by webmaven »

hermitcrab wrote: post #35
webmaven wrote: post #14

The proposed format is reasonably same, but you really want to prevent people from writing them by hand, and adding a bit of metadata to describe the column data types at a minimum, and ideally more information such as allowed values, semantics, etc. To that end, I suggest that putting the tabular data file, along with a metadata descriptor file, inside an archive format (zip, tarball, etc.); that would put just the…

Being able to create small tabular datasets by hand is incredibly useful to me (doing support for data wrangling software). Having an optional associated meta data file would be useful though.

Are you really typing the data directly into a csv file, or are you exporting csv data from something like a spreadsheet?

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

#342

Post by dossy »

LordDragonfang wrote: post #80
dossy wrote: post #11

Earlier quoted context omitted.

> That's CSV/TSV's real shortcoming: about the only generic validation they allow is to make sure the column count is the same for all rows. Once upon a time, when I was doing a lot of data interchange between a wide variety of systems (OS'es, applications, etc.) I considered proposing an "enhanced CSV" (ECSV) where the values did not start on the second row in the file, but instead the second row would be regular ex…

HN actually does support markdown code blocks, you just have to preface lines with four (nope, two)[1] spaces instead of the (newer) triple-backtick codefences. ID,NAME,DATE "/^\d+$/","//","/^\d{4}-\d{2}-\d{2}$/" 867,Alice,1984-01-09 5309,Bob,1981-11-16 [1] apparently it's actually two spaces instead of the normal markdown standard of four, making HN even more non-standard than usual https://news.ycombinator.com/form…

Yup, I noticed that and my Level of Care was insufficient to go and edit my comment a second time to fix it. But, thanks for confirming that it could have worked!

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

#343

Post by dossy »

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…

> Stating that their reasons are “imaginary” is just a needless insult, apart from being wrong. Why be like that?

It's not an insult, let alone a needless one: it is a statement of fact.

The reasons OP cites for criticizing CSV are creations of OP's imagination, their subjective beliefs, and not based in objective truth.

For example:

> "CSV is a mess."

Actually, CSV is quite orderly and predictable. OP only imagines it's a mess because of some skewed experience they've personally had.

> "One quote in the wrong place and the file is invalid."

Any file format that is encoded improperly renders it invalid. Imagining that this is strictly limited to CSV, is just that: a creation of OP's imagination.

> "It is difficult to parse efficiently using multiple cores, due to the quoting (you can’t start parsing from part way through a file)."

This is absolutely untrue. Anyone who's written a high-volume streaming CSV parser that is multi-process and multi-threaded should be giving OP the side-eye here.

Yes, you can absolutely start parsing from any arbitrary point in the file, and yes, in the degenerate worst-case scenario, you may need to rewind back to the beginning of the file if the data exists in such a way that your arbitrary starting point requires it, but that is true of ANY file format: it is possible to craft a worst-case scenario and specific starting position within that data that would require this. CSV is no exception.

Hope this helps you better understand what I had written.

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

#344

Post by account42 »

cestith wrote: post #330
Brometheus wrote: post #255

Earlier quoted context omitted.

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

It makes more sense to me in these days of ubiquitous encoding of in-band data that it would be easier to edit if we just used tabs and newlines. If you need a tab in your data, the standard could support either \t (requiring \\t if you need a literal backslash followed by a literal 't') or %09 or &09; or \x09 or something.

If you require escaping then you may as well stick with csv - better to have the character that needs to be escaped to be common enough that people will run into it during testing.

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

#345

Post by jayd16 »

AdrenalinMd wrote: post #269
jayd16 wrote: post #145

Earlier quoted context omitted.

None of them seem all that conducive to source control or merging. Any good format for that?

There is a git diff driver for CSV that makes the CSV diffing and merging a lot better. https://paulfitz.github.io/2014/07/09/diff-merge-csv.html Works like a charm!

Looks good, although I don't think I would want the diffs to be in cell. I might just end up writing my own merge tool that passes conflicting cells to a fallback tool.

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

#346

Post by cestith »

account42 wrote: post #344
cestith wrote: post #330

Earlier quoted context omitted.

It makes more sense to me in these days of ubiquitous encoding of in-band data that it would be easier to edit if we just used tabs and newlines. If you need a tab in your data, the standard could support either \t (requiring \\t if you need a literal backslash followed by a literal 't') or %09 or &09; or \x09 or something.

If you require escaping then you may as well stick with csv - better to have the character that needs to be escaped to be common enough that people will run into it during testing.

My actual preference is LTSV. Then all that needs escaping or encoding is a quote since a tab or newline inside the quotes is different from one outside the quotes. And I prefer encoding to escaping generally, since it's easier for someone else to come along and build a fairly naive parser.

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

#347

Post by aldonius »

euroderf wrote: post #314
simonw wrote: post #70

SQLite is one of the few file formats that's recommended by the US Library of Congress for archival storage: https://www.loc.gov/preservation/digital/formats/fdd/fdd0004... See also this page on the SQLite website (they're understandably very proud of this): https://www.sqlite.org/locrsf.html I think it's a fantastic format for archiving and distributing data.

All I'd want is a CLI tool that can dump an SQLite file so that it actually looks like a table (using "|", "-", "+", etc.), and maybe also accept grep-style filter specs (per specified column[s] of course).

you can get a long way with something like

$ echo "" | sqlite3 FILE

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

#348

Post by elcritch »

Pxtl wrote: post #305
elcritch wrote: post #257

Earlier quoted context omitted.

I've pondered exactly that. After a previous HN thread, I tried my hand at writing a specification that was minimal as possible but followed HTML5. For example many end tags are optional like `tr` and `td` end tags. Though I pulled in RDFa for richer data types. Here's the GitHub repo for what I like to call HSV5: https://github.com/elcritch/hsv5/blob/main/README.md ;) And an example of the format, pretty similar to…

Neat! edit: wait, the stuff after is implicitly the first cell? That's new to me.

As far as I can tell! Browsers seem fine with it too.

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

#349

Post by bariumbitmap »

This is a perennial topic. Related prior discussion on this forum alone is voluminous:

> I'm surprised never to have seen any support for CSV files using US/RS instead of TAB/(CR)LF or comma/(CR)LF. Or any support in Unix to use RS instead of LF or NUL to delineate records.

February 27, 2022, on: "The Great Curly Brace Trace Chase (2003)"

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

> If we're going to change the format, why not just use the record separator, and field separator characters.

August 19, 2021, on: "A straightforward way to extend CSV with metadata"

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

> Time to retire the CSV?

August 18, 2021

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

> I think we should just stop using commas and newlines and start using the ASCII unit separator and record separator

June 3, 2021, on "RFC 4180: Common Format and MIME Type for CSV Files (2005)"

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

> The record separator ship sailed long ago. The advantage of newline is that it’s easy to use with a bunch of standard tools like less/head/tail/your text editor.

May 25, 2021, on "Newline Delimited JSON"

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

> What both models lacked was a good way to handle optional/sparse fields.

February 26, 2021

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

> It's very annoying that ascii includes file and record separator characters and csv still exists

December 18, 2020 on: "What If OpenDocument Used SQLite? (2014)"

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

> ASCII 31 is a "unit separator" (or field separator as we'd call it today) and ASCII 30 is a record separator.

November 29, 2020

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

> Easy: CSVs are human readable and writeable and humans tend to have a comma sign on their keyboard, while they don't have the ASCII record seperator visible (teaching them to use eg Alt + 30 will not work).

May 15, 2020 on: "So you want to write your own CSV code (2014)"

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

> ASCII also includes control characters for delimiting text records. If people used these purposed-designed characters instead of comma or tab characters as delimiters, we could avoid many headaches quoting and escaping CSV data.

September 26, 2019, on: "Four Column ASCII (2017)"

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

> many of the pain points of CSV/TSV could be addressed by using US (unit separator) or RS (record separator) bytes instead.

April 11, 2018, on: "Problems with CSVs (2016)"

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

> ASCII actually contains four control characters for this purpose: file, group, record and unit separator. I'm sure a lot of problems would be solved if people just used these.

April 11, 2018, on: "Problems with CSVs (2016)"

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

> Ask HN: Why isn't ASCII codes 28 – 31 used more often to serialize tabular data?

December 15, 2017

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

> ASCII defines a unit separator, and a record separator. I wish we would just bite the bullet and start using this 50 year old solution.

December 27, 2016

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

> It really makes me sad that CSV even exists: ASCII defines field ('unit') & record separator characters (also group & file, but those are less-useful), as well as an escape character.

June 8, 2016 on: "ParaText: CSV parsing at 2.5 GB per second"

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

> ASCII has field record separator for instance. Free CSV.

July 2, 2015 on: "Stop the Vertical Tab Madness (2010)"

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

> Good old ASCII has characters specifically devoted to separating fields, keys, etc. that no-one uses for anything else. Why not use them instead of inventing a new character that does the same thing?

June 5, 2015 on: "Almost every Cassandra feature has some surprising behavior"

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

> ASCII Delimited Text – Not CSV or TAB delimited text

March 26, 2014

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

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

#350

Post by tech2 »

mbreese wrote: post #326
tech2 wrote: post #225

Earlier quoted context omitted.

Given your example looks very much like python you could use the CSV module which is built into the standard library and handles all of this for you in a standards compliant manner. You really do want to use a library to parse CSV since there are a number of corner cases. For example, your example code may not read a whole row since rows can have newlines in them.

> You really do want to use a library to parse CSV Right… you absolutely need to use a library. I’ve written the parsers for CSV which handle the edge cases (at least RFC edge cases). But I don’t want to include a library to be able to do it. Most of my scripts are small, and adding libraries makes them more difficult to move around. So, I shy away from CSV as a format as a result. I’m okay with not allowing new line…

I appreciate, as described, some of your needs may differ from the standard format (I'd probably still use them to make interoperability between other l languages/people/systems easier though and have my comments etc. in documentation). However, with all that in mind I may not have communicated well enough about CSV as a built in.

> Most of my scripts are small, and adding libraries makes them more difficult to move around. So, I shy away from CSV as a format as a result.

The CSV library I speak of is part[0] of the python standard library. Unless you're working with a restricted subset of the language or something it should have no impact on the size of your script or its portability between platforms/locations.

[0] https://docs.python.org/3/library/csv.html

Post reply on HN