24 ms·

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

• nbevans 4y ago
  SQLite is the answer.
• Justsignedup 4y ago
  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.
  • orasis 4y ago
    A JSON array doesn't allow easily appending a row. JSONLines is a bit better.
    • LeonB 4y ago
      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.
  • nescioquid 4y ago
    From TFA: >> XML and Javascript are tree structures and not suitable for efficiently storing tabular data (plus other issues).
    • javajosh 4y ago
      What does the acronym TFA mean here?
      • tsimionescu 4y ago
        "The Fine Article" (though more traditionally, and/or depending on tone, "The Fucking Article").
        • LeonB 4y ago
          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.”
    • hermitcrab 4y ago
      "Javascript" was meant to be "JSON". (now fixed)
  • barrkel 4y ago
    Doesn't have a great int64 number story, no native dates / date-times. If you want named tuples, then the names need to go everywhere, otherwise it's a serialization mechanism on top of JSON.
    • the_gipsy 4y ago
      Number size is out of JSON scope, int64 is a problem of JavaScript no matter what format you use.
    • hedora 4y ago
      It also has opinions about floating point numbers.
  • tshaddox 4y ago
    I think the problem is that there is no clear standard or convention for how to do this. Indeed there are many ways to represent tabular data. Most large software projects probably have at least one bespoke file format that they use internally. The trouble is the lack of such a format that everyone agrees on.
  • prepend 4y ago
    Doesn’t open in Excel. And since it doesn’t require one record per line it can be a hassle to read without having to parse it. It’s really nice to be able to do “head -n 5”
    • saulpw 4y ago
      jsonlines (jsonlines.org) is one record per line.
  • bumper_crop 4y ago
    This table has one column, one row, and thus one cell. What is its value? [{"col1":"val1", "col1":"val2"}]
    • noitpmeder 4y ago
      If you're really trying to replace csv with json, it would probably look like ["header1", "header2"] ["val1", "val2"] ... The entire file isn't valid JSON, but if you load each line independent of the next (jsonl), then you're fine.
  • delusional 4y ago
    Not being able to do more is exactly the point. By restricting the space of the file format, we can free the mind to think about how to fit the data to the format. If you can do anything, it becomes hard to do the right thing. If you can only do the right thing, it becomes trivial to decide.
  • Pxtl 4y ago
    The repetition of the keys seems like the sore point here. I could see a specialized form of JSON using jagged arrays {"Header": ["Name", "Street Address", "City"] "Body": [ ["Alice", "123 Fake St", "Faketon"], ["Bob", "987 Any Pl", "Anytown"], ]} in that way the keys aren't repeated. It wouldn't be coherent useful JS objects when deserialized, but it would be trivial to convert js table object into a true array of Javascript objects.
    • noitpmeder 4y ago
      Not great because you can't just append records without modifying the entire "body". Better to use JSONL!
  • xxs 4y ago
    That's inferior to even csv in any all its forms. It cannot be appended w/o removing the trailing square bracket. It requires column names per each row, you can't even know how many rows are there w/o parsing. It requires quotes (technical csv allows multiline rows but that's rarely used in practice) json is absolutely horrid for this type of hack-in jobs. Tabular data just not well structure data, easy to cut and paste.
• deleted 4y ago
  [deleted]
• benmmurphy 4y ago
  if you are ok with a binary format there is apache parquet or apache feather or 'jay' (https://datatable.readthedocs.io/en/latest/api/frame/to_jay.html https://datatable.readthedocs.io/en/latest/api/frame/to_jay....).
  • hermitcrab 4y ago
    No doubt binary formats like Parquet are the way to go for high performance with multi-GB datasets. Seems like total overkill if you have a few hundred or thousand rows of data though. Being able to create/edit/view stuff in a text editor and easily version it is very useful.
    • habitue 4y ago
      Do people really edit csvs in a text editor? It's horrific, the columns don't line up at all, empty cells are represented by a bunch of commas in a row (which, are you supposed to count all the commas?) And in terms of versioning, I have seen people commit diffs of csvs before, and they're equally unreadable. CSV is a plain text format, but that basically buys you nothing. As long as you're going to be loading it into excel or whatever anyway, might as well just pick a good binary format like parquet.
      • macintux 4y ago
        I frequently use the table syntax in org-mode. No, I wouldn’t edit CSVs because they are miserable, but a friendly syntax with some basic text editor support, absolutely.
      • hermitcrab 4y ago
        I create and edit CSVs by hand daily. To create/modify simple examples to provide technical support for a data transformation tool.
      • gnulinux 4y ago
        I frequently edit CSVs by hand. As long as you understand your data, and stakes are low (e.g. it's a one off analysis not prod code) it's pretty easy and convenient.
• nottorp 4y ago
  > No escaping. If you want to put \u001F or \u001E in your data – tough you > can’t. Use a different format. > It would be reasonably compact, efficient to parse and easy to manually edit > (Notepad++ shows the unit separator as a ‘US’ symbol). Is it me or it won't be human readable because of the lack of new lines?
  • shadowofneptune 4y ago
    I rarely want to see tabular data in a human-readable format. It is always the most tedious way to approach it. My go-to is Excel/LibreOffice Calc. This approach is at least tolerable to edit in a text editor, while something like the OpenDocument Spreadsheet format or the Excel format is impenetrable.
    • prepend 4y ago
      I rarely do it, but it’s nice to be able to Human read when I need to. Also being able to use all the command line text tools is super convenient. I think it’s a think where having the option for the .1% of times when you need it keeps me using it.
      • nottorp 4y ago
        > I rarely do it, but it’s nice to be able to Human read when I need to. Also being able to use all the command line text tools is super convenient. Sometimes it helps a lot to eyeball what you have before doing one off scripts to filter/massage your data. Had a recent case where the path of least resistance was database to csv to one off python to statistic tools with a gui and tabular display. Could have probably done some enterprise looking export infrastructure but it was a one off and not worth it.
  • hermitcrab 4y ago
    Yes, that is an issue with the suggested approach. Unless we can persuade all the editor developers to break lines as 'RS' characters (which is not going to happen).
  • 4y ago
• Pxtl 4y ago
  > 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 strict subset of HTML5 tables. Strip it down as hard as possible to the bare minimal elements - no unneeded close tags. The only real flaw is that their encoding of whitespace and escaped chars are verbose and ugly. <!DOCTYPE html> <html> <head> <title>Example workbook</title> <meta charset="utf-8"> <meta name="subtype" content="tabular data"> </head> <body> <table><caption>Persons</caption> <tr><th>Name<th>Address <tr><td>Alice<td>123 Fake St, Faketon FA 12345 <tr><td>Bob<td>789 Any Pl, Anytown AY 54321 </table> <table><caption>Cars</caption> <tr><th>Make<th>Model <tr><td>Buick<td>LeSabre <tr><td>Pontiac<td>Vibe </table>
  • adgjlsfhk1 4y ago
    it will also be at least 2x bigger on disk for no reason.
    • Pxtl 4y ago
      Your data must be very sparse or include a lot of escape chars for that. I've seen a lot of CSV where everything is quoted, meaning that the cell separator is effectively "," which is only one character less than <td> and still beats the pants off of JSON or XML. imho, it would be a good compromise, in that there's already partial tooling and GUI support.
      • prepend 4y ago
        But usually it will be 1 character vs 4. So that adds a lot of space that doesn’t add much value. It’s also harder to read.
        • Pxtl 4y ago
• AdamH12113 4y ago
  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?
  • quesera 4y ago
    ASCII separators (mnemonics FS, GS, RS, US) are difficult for most users to type, and have no obvious/standardized visual representation.
    • hermitcrab 4y ago
      Notepad++ show RS and US as little black boxes with 'RS' and 'US' in. It works well enough.
    • jonnycomputer 4y ago
      My editor, and lots of editors now, put spaces instead of tabs when I press the tab key. No reason something similar couldn't happen in CSV modes/apps.
    • Gigachad 4y ago
      Who is typing out CSVs anyway. You’d either use a spreadsheet tool or do it in code.
      • qwerty456127 4y ago
        From my point of view, CSV is mostly operated in text editors because they are much faster and don't risk to do any unintended modifications.
    • indymike 4y ago
      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.
  • 4y ago
• human_person 4y ago
  Have you see tiledb? https://tiledb.com/data-types/dataframes https://tiledb.com/data-types/dataframes My team is currently transitioning from HDF5 to tiledb for genomics data.
  • elmolino89 4y ago
    TileDB-VCF does work very well. Which types of data stored as HDF5s you are ingesting into TileDB?
  • stavrospap 4y ago
    Hi folks, Stavros from TileDB here. Here are my two cents on tabular data. TileDB (Embedded) is a very serious competitor to Parquet, the only other sane choice IMO when it comes to storing large volumes of tabular data (especially when combined with Arrow). Admittedly, we haven’t been advertising TileDB’s tabular capabilities, but that’s only because we were busy with much more challenging applications, such as genomics (population and single-cell), LiDAR, imaging and other very convoluted (from a data format perspective) domains. Similar to Parquet: * TileDB is columnar and comes with a lot of compressors, checksum and encryption filters. * TileDB is built in C++ with multi-threading and vectorization in mind * TileDB integrates with Arrow, using zero-copy techniques * TileDB has numerous optimized APIs (C, C++, C#, Python, R, Java, Go) * TileDB pushes compute down to storage, similar to what Arrow does Better than Parquet: * TileDB is multi-dimensional, allowing rapid multi-column conditions * TileDB builds versioning and time-traveling into the format (no need for Delta Lake, Iceberg, etc) * TileDB allows for lock-free parallel writes / parallel reads with ACID properties (no need for Delta Lake, Iceberg, etc) * TileDB can handle more than tables, for example n-dimensional dense arrays (e.g., for imaging, video, etc) Useful links: * Github repo (https://github.com/TileDB-Inc/TileDB https://github.com/TileDB-Inc/TileDB) * TileDB Embedded overview (https://tiledb.com/products/tiledb-embedded/ https://tiledb.com/products/tiledb-embedded/) * Docs (https://docs.tiledb.com/ https://docs.tiledb.com/) * Webinar on why arrays as a universal data model (https://tiledb.com/blog/why-arrays-as-a-universal-data-model https://tiledb.com/blog/why-arrays-as-a-universal-data-model) Happy to hear everyone’s thoughts.
• prepend 4y ago
  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 clean production and consumption programs than to spend time on a really structured table format. Although I would love a simple, tabular format so I’d never have to use csv again.
  • quesera 4y ago
    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.
    • hermitcrab 4y ago
      Not being able to include Tabs and Carriage Returns in your data can be a problem though.
      • deleted 4y ago
        [deleted]
      • mjevans 4y ago
        Depending on the library, (double) quoted fields _may_ be able to contain carriage returns (0x13) and/or line feeds (0x10). Sometimes it works, sometimes it doesn't, can be a real pain in the butt.
      • justin_oaks 4y ago
        The ideal would be a format that avoids characters that are common in data, but still allows a way to encode rare characters that are part of data. I think that was the motivation for multi-character delimiters like <% %> or {{ }} in templating languages.
      • 4y ago
• habitue 4y ago
  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 need to look at tabular data outside of a text editor anyway[0], I don't see a ton of benefit to making it a plaintext format. Especially not with the author's suggestion of un-typeable ascii delimiters. If I can't type it on my keyboard, I may as well be looking at a hex dump of a binary file because I can't really edit it. [0] Who among us hasn't experienced the joy of a pull request updating a checked in csv file? A mess of ,,,,,,,"Birmingham",,,AL, etc.
  • hermitcrab 4y ago
    Editors such as Notepad++ make it fairly easy to insert US and RS ASCII codes. But not quite as simple as typing a comma or return, obviously.
  • emef 4y ago
    parquet is great but it's not particularly easy to read or write. the libraries that do exist to work with it are few and far between, and those that do either have a hundred dependencies or depend on native code (e.g. libarrow). certainly an important dimension in an ideal file format should be the ease of parsing/writing it, and parquet gets an extremely low score on that front imo
    • thayne 4y ago
      Parquet is also column-major which is great for many use cases, but bad for others, where row-major is more useful. For example, if you want to get just the first x rows.
      • adammarples 4y ago
        Then you want avro
    • 4y ago
• aimor 4y ago
  I've done exactly what the author suggests for the exact same reasons. CSV headaches got me searching for other delimiters and I saw there were already unit and record separators, "WTF!?". As long as you're dealing with text, and you're writing and parsing the data yourself, and you never need to use tools other than things that can be modified to set the item and line delimiters, then it's great. I haven't used the approach since then. Mostly I haven't used it because half my use cases need binary numerical data (floats) and the others can be worked with entirely in Excel or as CSVs. But I like the idea, even if the support just isn't there.
• bitmedley 4y ago
  > Tab separated is a bit better than CSV. But can’t store tabs and still has issues with line endings, encodings etc. There are three main variations of tab-separated-values files: 1) Those that don't allow tab and line endings. 2) Those that replace tab and newline characters with escaped values (\n for newline, \t for tab, \r for carriage return, \\ for backslash). 3) Those that follow the CSV convention of quoting fields as defined in RFC4180 The third option is by far the best and is what Microsoft Excel uses. Microsoft Excel has a save file type called "Unicode Text (.txt)" which saves the data as a tab-separated file using RFC4180 quoting/escaping with the UTF-16LE character encoding. In older versions of Excel, "Unicode Text (.txt)" was the only way to export any values containing Unicode characters since the "CSV (Comma delimited) (.csv)" export format uses the ANSI encoding(Windows-1252 on Western/US computers) corrupting any characters not contained in the ANSI character set. In late 2016, Microsoft finally added the "CSV UTF-8 (Comma delimited) (.csv)" option for exporting a CSV file containing Unicode characters. https://en.wikipedia.org/wiki/Tab-separated_values https://en.wikipedia.org/wiki/Tab-separated_values
• simonw 4y ago
  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/fdd000461.shtml#local 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 https://www.sqlite.org/locrsf.html I think it's a fantastic format for archiving and distributing data.
  • rileytg 4y ago
    sqlite also has roots in the us military (navy iirc). this could explain the US govs willingness to adopt.
    • tptacek 4y ago
      The LOC page explains why they recommend SQLite. https://www.loc.gov/preservation/digital/formats/fdd/fdd000461.shtml https://www.loc.gov/preservation/digital/formats/fdd/fdd0004...
  • Banana699 4y ago
    I honestly love SQLlite, so many things about this database are refreshingly amazing. The lightweight embedded nature, the extreme ubiquity[1], the single-source-file-to-compile idea[2], the gigantic test suite[3], their commitment to stability and backward compatability[4], etc ... Simply a modern marvel of engineering. A thing to put on the next Voyager and send to aliens as a representative of what human minds and hands can do. [1] https://www.sqlite.org/mostdeployed.html https://www.sqlite.org/mostdeployed.html [2]https://www.sqlite.org/amalgamation.html https://www.sqlite.org/amalgamation.html [3] https://www.sqlite.org/testing.html https://www.sqlite.org/testing.html [4]https://www.sqlite.org/lts.html https://www.sqlite.org/lts.html
  • euroderf 4y ago
    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).
• donatj 4y ago
  > 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. I'm known for these two posts https://donatstudios.com/Falsehoods-Programmers-Believe-About-CSVs https://donatstudios.com/Falsehoods-Programmers-Believe-Abou... https://donatstudios.com/CSV-An-Encoding-Nightmare https://donatstudios.com/CSV-An-Encoding-Nightmare Some friends and I actually started an RFC about 11 years ago for a CSV enhancement with an HTTP inspired header section with metadata including encoding. UTF-8 wasn't as clear of a winner back then. Never went anywhere.
  • LeonB 4y ago
    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 https://github.com/secretGeek/AwesomeCSV#essays There’s a few specs mentioned there too — is one of those the spec you worked on?
    • dataflow 4y ago
      I think you still can load in parallel, but it just introduces potential for backtracking/correcting the data if you speculate incorrectly.
    • wahern 4y ago
      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.)
      • 4y ago
• togaen 4y ago
  “No escaping. If you want to put \u001F or \u001E in your data – tough you can’t. Use a different format.” I fully support this format just for this.
• tbrownaw 4y ago
  I thought in-band signalling was generally considered harmful these days? +++
  • avmich 4y ago
    You mean, everybody prefers FTP to HTTP?
• ubermonkey 4y ago
  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 can assume access, and so it gets used. I have a strong sense that this may be the biggest reason why no other format has taken over. It's "good enough" that it steals oxygen from any competing idea.
  • LeonB 4y ago
    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? )
  • deleted 4y ago
    [deleted]
• slaymaker1907 4y ago
  Why not sqlite? You can read/write to a sqlite file in pretty much any language using decent bindings aside from maybe browser JS. I've worked with third party sqlite files several times and it usually isn't that bad. You can't just use any text editor to work with them, but the sqlite CLI is very easy to install and use. If you want a GUI, DB Browser seems to work very well.
• chubot 4y ago
  Related thread: CSVs: The Good, the Bad, and the Ugly (2020) https://news.ycombinator.com/item?id=25014721 https://news.ycombinator.com/item?id=25014721 FWIW I have designed an upgrade to TSV as part of https://www.oilshell.org 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 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 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 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.
  • earthboundkid 4y ago
    You should make the connection of QTT and QSN to Rust more clear by coming up with a backronym for RSON.
    • chubot 4y ago
      Hm I wasn't aware of RSON. https://github.com/rson-rs/rson https://github.com/rson-rs/rson QSN isn't intended to be tied to Rust in any way (and isn't), while RSON says it uses the Serde data model. This gets at an issue I have been having a hard time explaining, mentioned here: http://www.oilshell.org/blog/2022/03/backlog-arch.html http://www.oilshell.org/blog/2022/03/backlog-arch.html That is, narrow waists are necessarily a COMPROMISE. JSON is a compromise, and Rust users will be equally unhappy as Lua or Erlang users. That is a feature and not a bug for something meant of interoperability. You are "stuck with" the lowest common denominator, but that's what enables interop. (The lowest common denominator of them all is Unix byte streams.) I contrast "monoglot" serialization formats like Python pickle an Go .gob with language-independent formats like JSON, TSV, and HTML. The wisdom of JSON is that Crockford specified it independently of JavaScript. But both are useful. It's not clear if RSON is meant to be monoglot or polyglot, but it's a huge difference and it seems more monoglot. QSN on the other hand is definitely a polyglot design like JSON, despite being derived from Rust.
• blagie 4y ago
  Simple format resolving most of the issues: http://www.tsvx.org/ http://www.tsvx.org/
• indymike 4y ago
  If we are talking about machine transfer, there are tons of formats that work, and many of them are binary. xls, ods, SYLK, and dta are examples (not sure about UTF8 wit SYLK and dta). Then there are the "just write it in code" formats like SQL dumpfiles (some xml fits here, too) - which work well and carry really nice, readable type declarations. If we are talking about human readable, text editor safe formats, we have lots of challenges. ASCII delimiter characters don't display and aren't on the keyboard (and will sometimes have side effects with old wire protocols). Nearly any character that is on a keyboard (or international keyboards) eventually becomes a problem because you will have to escape them when they are not used as a field (, is the classic example) or record delimiter (For example, <CR> can be valid inside of a longer text field). Also, many of the first 31 ASCII characters (the control characters) are used in different and surprising ways (the classic being null) by hardware and software. BTW what makes CSV bad is that there is no escape character for , or ". Likewise, TSV has the same problem with tabs. JSON, YAML, and XML seem to be much better, but all have edge cases.
• MrPowers 4y ago
  Parquet is a wonderful file format and is a dream to work with compared to CSV. Parquet embeds the schema in the footer metadata, so the query engines don't need to guess what the column names / data types are. Parquet used to be poorly supported, but now it's well supported by almost all languages. You can even view Parquet files in text editors now, but that's not something I've ever needed (https://blog.jetbrains.com/blog/2020/02/25/update-on-big-data-tools-plugin-spark-hdfs-parquet-and-more/ https://blog.jetbrains.com/blog/2020/02/25/update-on-big-dat...). Parquet column pruning & predicate pushdown filtering allow for great query performance improvements, see this blog post I wrote for benchmarks: https://coiled.io/blog/parquet-file-column-pruning-predicate-pushdown/ https://coiled.io/blog/parquet-file-column-pruning-predicate... Delta Lake (Parquet files + a transaction log) makes it quite pleasant to manage a large lake of data stored in Parquet. There are tons of other features Delta allows for like time travel, schema enforcement, schema evolution, etc.
  • losvedir 4y ago
    I've been pretty impressed with parquet lately. One thing I've missed is a way to group tables. Is there a standard for that? While parquet is generally column oriented it has support for metadata about tables of multiple columns. However, I'm not aware of any format that groups the tables, short of just zipping a bunch of files. For context, this would be for an application that passes sqlite files around. So naturally it has good support for the database level of storage. But parquet is so fast for some applications as well as so compressed.
    • 101011 4y ago
      Is Spark what you're looking for? You can do all sorts of joins, groupings, and aggregations with parquet(s) acting as your source(s)
    • BFLpL0QNek 4y ago
      You want to search for “DataFrame” libraries. Another commenter mentioned Spark, Panda’s is another popular one, not used it but think it’s lighter weight where Spark is more for large distributed computation even though can run locally. There’s a bunch of these tools which lets you treat parquet files as tables doing joins, aggregations etc.
• thayne 4y ago
  I've actually started working on something very similar: https://github.com/tmccombs/ssv https://github.com/tmccombs/ssv So far it's just a python library, but I'm planning on adding editor plugins at least for vim, vscode and maybe emacs, libraries for additional languages, and maybe some cli commands for it. One distinction from the OP is the delimiters also include a tab (for fields) or newline (for records) by default (but not in "compact" mode). That has the benefit that the files are at least readable with editors and pagers that aren't aware of the format.
• psfried 4y ago
  > But it is binary, so can’t be viewed or edited with standard tools, which is a pain. I've heard this sentiment expressed multiple times before, and a minor quibble I have with it is that the fact that it's binary has nothing to do with whether or not it's a pain. It's a pain because the tools aren't ubiquitous, so you can't count on them always being installed everywhere. But I'd argue that sqlite _is_ ubiquitous at this point and, as others have mentioned, it's a _great_ format for storing tabular data. JSON is also a fine choice, if you want it to be human readable, and I'm not sure why this is claiming it's "highly sub-optimal" (which I read as dev-speak for 'absolute trash'). JSON is extremely flexible, compresses very well, has great support for viewing in lots of editors, and even has a decent schema specification. Oh, and line-delimited JSON is used in lots of places, and allows readers to begin at arbitrary points in the file.
  • jltsiren 4y ago
    JSON is good for structured data, but I prefer TSV for simple human-readable tabular data. In situations where it's the right choice, a TSV file consists of data and whitespace and nothing else. You can view and edit it with any imaginable tool, and there is no overhead in the form of delimeters and encodings distracting you from the data.
    • cestith 4y ago
      I really like LTSV. (That stands for labeled tab-separated values.) LTSV is basically equivalent to a JSON object per line. You have columns consisting of a label, a colon, then a value. The columns are then separated by tabs. The value can be quoted. If you need a tab in the value, it goes inside the quotes. As the http://ltsv.org/ http://ltsv.org/ suggests, I use it for logging, too, so that a log line is easily parseable and a log file is basically a table. Notice there are parsers for many languages, and there are several tools supporting it including fluentd.
• asperous 4y ago
  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 relational data, somewhat ubiquitous, types defined but not enforced parquet / hdf5 / apache feather / etc -- Designed for scientific use cases, robust, efficient, less ubiquitous capn proto, prototype buffers, avro, thrift -- Has specific features for data communication between systems xml -- Useful if you are programming in early 2000s GDBM, Kyoto Cabinet, etc -- Useful if you are programming in late 1990s Pick your poison I guess. Engineering is about trade offs.
  • zzo38computer 4y ago
    The latest version of SQLite has a STRICT command to enforce the data types. This option is set per table, but even in a STRICT table you can specify the type of some columns as ANY if you want to allow any type of data in that column (this is not the same meaning of ANY in non-strict tables).
    • briHass 4y ago
      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.
      • leeoniya 4y ago
        > 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?
        • zoomablemind 4y ago
• dfdz 4y ago
  I think CSV is a decent file format for tabular data. The author claims that CSV files are > difficult to parse efficiently using multiple cores, due to the quoting (you can’t start parsing from part way through a file). But I do not see why this is the case. Step 1: loop over file (in parallel) to determine indices of quote characters Step 2: loop over indices outside quote regions (in parallel) to determine indices of comma and return characters Step 3: return two dimensional integer array with pointers to cells of table
  • deleted 4y ago
    [deleted]
  • rectang 4y ago
    I'm inclined to agree. CSVs which are well-formed (escapes within fields handled consistently) shouldn't be that hard to parse. I can't think of a reason your algo wouldn't be logically sound for good CSV files, although a little backtracking might be necessary to recognize escaping of delimiters in edge cases. The author writes "CSV is a mess. One quote in the wrong place and the file is invalid.", but what logical formats can tolerate arbitrary corruption? An unclosed tag is similarly problematic for xml. In both cases you wind up falling back to heuristics. It's true that CSVs often contain a mess of encodings inside fields, but that's not the problem of the CSV format per se. Validation of field encodings, or validation that the entire file is in a uniform encoding... those are separate requirements.
  • EdwardDiego 4y ago
    Indeed, every distributed query engine I've used can easily parallelise CSV in the same file (so long as it's splittable, friends don't let friends gzip their data), with the option to ignore bad rows, log them, or throw your hands up and die. Admittedly, all of them are Java based and use Hadoop libs for handling CSV, which makes sense, the Elephant ecosystem has spent years getting this stuff right.
• foxylad 4y ago
  I've used \u001F and \u001E for years. Don't need to check for and un/escape separator characters, very compact, and python's split() and join() makes implementation trivial. Hope this article makes them more popular.
• zzo38computer 4y ago
  I had also thought of the same format described there (but without Unicode), and others also have done. The SQLite command shell also implements this format as one of the possibilities.
• qwery 4y ago
  Because a file (or computer memory in general) is a linear (1D) storage medium and tabular (2D) data is not well suited to linear serialisation. You can't avoid adding complexity (i.e. making trade-offs) to the format and any code that deals with it.
• nvader 4y ago
  This tabular file format doesn't exist.com
• devit 4y ago
  The article's proposal seems worse than tab separated. Not having escaping is unacceptable of course, and once you add escaping you might as well use the standard tab and newline characters as delimiters (and the \t, \n, \\ escapes plus \N for SQL NULL), resulting in a file that is properly formatted by the terminal and text editors and just works with Unix tools and most spreadsheet/database importers unlike a file with the weird separators the article proposes.
  • hermitcrab 4y ago
    >Not having escaping is unacceptable of course How often do you use the ASCII US and RS characters in your data (apart from binary data blobs, which it wouldn't be a good fit for)?
• noobermin 4y ago
  Don't know why csv cant be done in parallel by splitting across lines.
  • hermitcrab 4y ago
    Because the line break might be escaped and inside a data value. You have to parse all the way from character 1 to be sure.
• galaxyLogic 4y ago
  I prefer tab-separated is to comma-separated. With an editor like Excel etc. you can then make it so that you exactly see where columns are, which column each datum belongs to.
• tpl 4y ago
  Parquet is the answer, human readable is not a valid requirement for tabular data anyway. If you absolute must be human readable CSV is good enough.
  • mountainriver 4y ago
    You can also easily read it in and view it in a human readable format, I’ve never had a problem with that
• mc4ndr3 4y ago
  SQL INSERT's
• _8j50 4y ago
  I just came up with a new format guys, BCSV! <pre> def parse(fileData): rows=[] for line in fileData.splitlines(): for cell in line.split(","): rows.append(b64decode(cell) return rows </pre> Tada! Edit: I give up, can't figure out hn's code format syntax.
• kragen 4y ago
  The proposal is TSV but using ^_ instead of ^I (tab) to separate columns and ^^ instead of ^M (CR) or ^J (LF) to separate rows. So instead of being unable to put ^I and ^J in your table cells you are now unable to put ^_ and ^^ in your table cells. That sounds exactly as good, or bad, as TSV. So, okay? Sure, do that if you like, that sounds fine. Emacs TAGS files, Info files, and BABYL mailboxes https://quimby.gnus.org/notes/BABYL https://quimby.gnus.org/notes/BABYL similarly use weird control characters as delimiters.
  • hermitcrab 4y ago
    US and RS characters are much less likely to appear in textual data than Tab or LF. Also I believe this was the original purpose for US and RS in ASCII. So I'm not sure it is 'weird' to use them for that (although it does seem to be uncommon).
    • kragen 4y ago
      Yeah, it kind of was; also FS and GS. GNU Info files and Babyl files use US that way. Really though you can define whatever weird format you want if you're writing the code for it.
• nahtasjain 4y ago
  [dead]
• sargun 4y ago
  > There doesn’t seem to be anything that is reasonably space efficient, simple and quick to parse and text based (not binary) so you can view and edit it with a standard editor. > XML and Javascript are tree structures and not suitable for efficiently storing tabular data (plus other issues). You can certainly be efficient with json(net). See: Notice how they are separate objects: {'name': 'foo', 'age': 2} {'name': 'cat, 'age': 6} You can do it very efficiently: https://github.com/simdjson/simdjson https://github.com/simdjson/simdjson Compress it if you need compact. There's also UBF, but it never saw much traction: https://ubf.github.io/ubf/ubf-user-guide.en.html#specifications https://ubf.github.io/ubf/ubf-user-guide.en.html#specificati...
  • benibela 4y ago
    XML can be more efficient, since you do not have to surround the key with quotes. It saves two characters at the foo/cat each: <r name="foo" age="2"/> <r name="cat" age="6"/>
• bullen 4y ago
  What about a HTML/XML table?
• d--b 4y ago
  1. Either you want to edit the file in a text editor and you need to have a separator that has a key on your keyboard. Comma is indeed not the greatest choice, but tab or vertical pipe are usually fine. Most parsers will allow you to define the separator. 2. Or you don’t and you’re much better off with any binary format. Why aren’t unicode separators keys on your keyboard? Maybe that’s suboptimal, but good luck changing that.
• omnicognate 4y ago
  > Why can’t we have a format where: > <A bunch of rules> 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.
• jarofgreen 4y ago
  Fundamentally, why are people so concerned about about hand editing tabular data? The options for that are pretty terrible. Only editing an existing value in one cell is ok. Everything else - adding new rows, new columns and making sure the file format and column structure is correct - is all terrible. This gets doubly so if you stick it in git and start getting messy pull requests. I would carefully question what use case you have that this is so important. If you truly do have such a use case, I'd politely suggest tabular data maybe isn't what you need. Once you remove that requirement, binary solutions for tabular data are possible again and several good options have already been pointed out in other comments.
  • larsrc 4y ago
    I regularly need to hand-edit tabular data I have generated from other data when it turns out there are a few weird cases I didn't handle right. I do a lot of one-off data processing in several stages, so it's not just a question of fixing the generator. If I want to edit the structure, sure, I'll take it into Sheets, but for data that's slightly weird, a text editor or shell pipe is often faster.
• hoseja 4y ago
  Why are all these "separated"? Sounds like null-terminated C strings. Wouldn't it be better to put the dimension in a header and then just dump the contents unseparated? Why the need to view them in simple text editors?
  • eru 4y ago
    What you suggested works for fixed width fields only, doesn't it?
    • hoseja 4y ago
      Yeah, true. Still, one or more bytes of overhead per cell and row/column seems wasteful.
      • hermitcrab 4y ago
        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.
        • eru 4y ago
          Space efficient, that is. Fixed width formats are pretty efficient for quick random access. Btw, you can have a mostly fixed width format, but with an escape that puts larger value somewhere else. That's more or less what some relational databases are doing, I think.
• rusk 4y ago
  Plain ASCII has everything you need, but nobody uses these features because they’re non-printing chars. On the one hand you want a special format, but on the other you want to edit it like plain text?
• baggiponte 4y ago
  Perhaps we “just” need Excel and Numbers to support reading .parquet files, and come up with a CLI tool to view them like head/tail/awk? Yes, they still cannot be read as plain text so functionality is quite restricted with respect to CLI pipelines
• jph 4y ago
  > it could be called something like “unicode separated value” There is. USV uses Unicode Record Separator and Unicode Field Separator. And data handling tools such as Miller offer USV: https://miller.readthedocs.io/en/latest/file-formats/#csvtsvasvusvetc https://miller.readthedocs.io/en/latest/file-formats/#csvtsv...
• fattybob 4y ago
  Take a look at LAS std - commonly used in oil Field electric log data, it has its positives re indexing
• joshka 4y ago
  Amazon ION perhaps? https://amzn.github.io/ion-docs/guides/cookbook.html#converting-non-hierarchical-data-to-ion 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)...
• Traster 4y ago
  In my view you can't have it both ways - if you want efficiency it's not reasonable to expect to be able to hand edit the data in a text editor. Especially since at the scale that efficiency matters your text editor is going to struggle. If you don't care that much about efficiency then CSV, JSON, XML all provide perfectly good solutions and they're actually more easily editable in a text editor than what is being suggested. It's also much more sensible if you are hand-editing tabular data to just slurp it in to a jupyter notebook and do the edits there and shoot it back out because it means you've got a straight forward record of what changes you've had to make and can re-run those changes at a click of a button.
• carapace 4y ago
  https://git.sr.ht/~sforman/sv https://git.sr.ht/~sforman/sv > This is a simple file format for storing tabular content. It's meant to work like CSV files. So far only the low-level encoding is specified, handling e.g. type conversion, etc., is left to a higher-level abstraction layer (as yet unwritten.) > An *.sv file consists of one or more documents separated by ASCII Group Separator character (0x1D), sv files are UTF-8 encoded. > Each document can have an optional header indicated by the presence of ASCII Start of Heading character (0x01) followed by zero or more records of metadata. If present, the first record should be field names, the second record should be type names, any further records are user-defined. The first two records (names and types) must be the same length as the data records. The header is concluded by an ASCII Start of Text character (0x02). > Records are separated by ASCII Record Separator characters (0x1E), and data units (fields) of each record are separated by ASCII Unit Separator characters (0x1F). All record values must be strings. > ...a grammar: sv ::= doc [GS sv] doc ::= [header] records header ::= SOH records STX records ::= record [RS records] record ::= field [US record] field ::= <sequence of zero or more non-sv Unicode characters> A table of the sv ASCII characters: 1 01 00000001 SOH
  Start of Heading 2 02 00000010 STX  Start of Text 29 1D 00011101 GS  Group Separator 30 1E 00011110 RS  Record Separator 31 1F 00011111 US  Unit Separator
• sindoc 4y ago
  I agree with the author. There’s merit in an open format to be developed with a simple spec as pretty much defined here. Of course more formalism should he attached to it i.e. BNF (am I too old?) Anyway, one of the giants making billions out of data should maybe commission the publication of such a standard and the implementations in every programming language will follow, I’m sure. The Unicode bit alone and an enforced mine type makes the case in my opinion and it should have streaming capabilities as well. Maybe chunking of content so that it can be used for caching and compression, ideally still human readable but not marked up like XML so you can’t see the end of it.
• sleightofmind 4y ago
  I don't know why. That said, I've enjoyed using Carlo Strozzi's NoSQL program in the past. It's not NoSQL in the current sense of the word. It's a plain text database that stores data in delimited text fields -- e.g. tsv or csv. Create those fields however you want. I just used rlwrap/readline in a simple shell program for input. It does the basic relational database things, but all with plain text. A text editor will work just fine for data entry, and for viewing it later. It will be around as long as Linux commandline utilities are around. Your data can be viewed in a line-oriented or list-oriented fashion -- the program does the conversion for you in commandline fashion. And you can view everything in a text editor. I think it's just a bit of code tying together a bunch of standard Linux commandline utilities. It's not in a lot of repositories these days. But back when, I found it quite handy for working with line-oriented tabular data. It was the guts of a bailing wire and bubblegum program I wrote to create reinforcing steel barlists back when I only thought I knew what I was doing. My ignorance persists but now I'm aware of and at peace with it.
• cestith 4y ago
  Why not http://ltsv.org/ http://ltsv.org/ ???
• bariumbitmap 4y ago
  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 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 https://news.ycombinator.com/item?id=28236339 > Time to retire the CSV? August 18, 2021 https://news.ycombinator.com/item?id=28221654 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 https://news.ycombinator.com/item?id=7474600
• synack 4y ago
  HDF5 is often used in scientific computing for this. https://en.wikipedia.org/wiki/Hierarchical_Data_Format https://en.wikipedia.org/wiki/Hierarchical_Data_Format
  • out_of_protocol 4y ago
    You can just use sqlite then. Very compact, highly popular (in different role though). Seen it used for large datasets - map tiles (millions of jpeg files). Much smaller size than zip or tar archive, indexed, fast. P.S. sqlite> .mode csv sqlite> .import city.csv cities
    • d3mcfadden 4y ago
      Cool, I didn't know about .mode and .import. Super handy tip.
      • mastax 4y ago
        You can also use .mode csv .headers on .output file.csv Then run a query and it gets output to file.csv.
  • MrPowers 4y ago
    HDF5 has some limitations that make it suboptimal for cloud based storage systems. Zarr overcomes these limitations for array data and Parquet overcomes these limitations for tabular data.
    • noobermin 4y ago
      The OP wants a text based format, he doesn't care about what is optimal.
  • acomjean 4y ago
    Single Cell RNA Seq data is often stored in Loom which is a HDF5 format. https://linnarssonlab.org/loompy/ https://linnarssonlab.org/loompy/ Its a little weird at first but its a great format and has libraries in a lot of major languages. It stores a sparse matrix which cuts the size down a lot. https://linnarssonlab.org/loompy/format/index.html https://linnarssonlab.org/loompy/format/index.html
• nh23423fefe 4y ago
  I don't think anyone wants tabular data. They want to ingest it into a system so they can query it, or join it, or aggregate it. They want to get rid of tabular data as quickly as possible Data at the boundary must be validated, a file format can't do that for your. Semantics is harder than syntax and you can't push every problem to that level.
  • ACow_Adonis 4y ago
    yeah, um, my life is in tables (stats, finance, etc). while csv is less than optimal, it's always worked out in practice for me (because we still have to validate anything of significant size and nothing finance or city/nation sized should generally be edited by hand anyway, and errors are everywhere independent of format). honestly, my bigger nightmare has been people trying to put things into something like xml... edit: not that there aren't huge problems with the csv/excel/database software and interaction: generally I just end up having to assume everything is text and defining type information my end afterwards on successful parsing
  • barrkel 4y ago
    We've had XML for decades and the default interop is still tabular data - probably in large part because unless you go into non-standard extensions, tabular data is the easiest thing to get from a database query. Spreadsheets haven't exactly gone away either.
  • Sharlin 4y ago
    And the data in the system is… almost always tabular? Either Excel (which runs half the world) or RDB/SQL (which runs the other half).
  • hermitcrab 4y ago
    It's tables all the way down...
  • hirundo 4y ago
    The finance world runs on tables. They want to ingest it, query it, join it, aggregate it ... and look at the result in tables. Tables are everywhere, and a generic tabular format is constantly useful for a la carte one off spontaneous data processing. I love strict schema validation as much as the next guy, but sometimes you just want to cut and paste a table.
• hirundo 4y ago
  > 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 syntax. 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.
  • hermitcrab 4y ago
    if you add an extra comma in a CSV (outside of quoting) then the rest of the cells in that row are off by 1. Which is not good obviously. But if you add an extra quote, then the entire rest of the file is garbage.
  • sundarurfriend 4y ago
    >> 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 should `set readonly` on my editor and only interact with the file through programmatic tools, and give up any notion of it being a plaintext hand-editable data format.
    • Someone 4y ago
      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.
• briHass 4y ago
  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 this if you try to save a native Excel file as CSV: you are effectively throwing away information.
  • Pxtl 4y ago
    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.
    • anigbrowl 4y ago
      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.
• dossy 4y ago
  > 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 need to use a different format. 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.
  • LeonB 4y ago
    > 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 their reasons are “imaginary” is just a needless insult, apart from being wrong. Why be like that?
    • tbrownaw 4y ago
      > 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
      • UncleEntity 4y ago
        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.
• johnny22 4y ago
  I like one of the json lines formats, although pretty sure you'd wanna treat floats as strings there, so they don't get mangled. Each line is separate object there.
• webmaven 4y ago
  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 right size speed-bump to encourage accessing the data through tools and libraries (though if someone is just a bit determined, reading and editing the contained data directly isn't actually impossible or forbidden). All that said, if you want a better tabular data interchange format badly enough that you're considering devising one, you should probably look at using something even more featureful, like SQLite: https://www.sqlite.org/appfileformat.html https://www.sqlite.org/appfileformat.html
  • LeonB 4y ago
    I put some work into creating a standard, csvz, for putting csv files and their metadata, into a zip file. https://github.com/secretGeek/csvz https://github.com/secretGeek/csvz It’s a pretty powerful concept. SimonW’s preferred technique of using sqlite as the means of exchange is also very powerful. Particularly when combined with all of the utils he maintains.
  • hermitcrab 4y ago
    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.
    • webmaven 4y ago
      Are you really typing the data directly into a csv file, or are you exporting csv data from something like a spreadsheet?