40 ms·

Time to retire the CSV?

• newbamboo 5y ago
  “ I’m not going to advocate for any one successor file format here, but there are certainly a lot of contenders. The big data ecosystem gave us formats like Avro, Parquet, and Arrow that are widely used as an intermediate representation when transferring data between systems. HDF51 is widely used in the scientific computing community.” Xkcd: https://m.xkcd.com/927/ https://m.xkcd.com/927/
  • xtracto 5y ago
    USB-C (alt text)
  • croo 5y ago
    Yeah okay funny, but widely used? I've never heard any of these formats. Can someone give a short and biased opinion which one is the best?
• izietto 5y ago
  "In favour of what?", that is the matter. CSV is a format more for humans and less for machines, but that is the use case: a format that is good enough to be compiled by humans and read by machines. At the moment there aren't many alternatives.
  • habitue 5y ago
    Objectively, CSV is terrible for humans despite being a plaintext format. No one reads CSVs: they're incomprehensible since the columns are not aligned with the headings. (You might be drawing an analogy with JSON, which is often human readable because it puts the keys right there next to the values). The best that can be said for its simplicity is that it's easy to write code that can dump data out in CSV format (and to a lesser extent, it's easy to parse it, though watch out for those variants). This is not a really strong argument, most everyone is going to use a library for serialization, there's no reason to write your own unless it's for learning.
    • fighterpilot 5y ago
      I'm a human and I love CSVs. What other format can I open in a spreadsheet, access through cat/grep/awk, and easily load into any programming language? Any other format has to sacrifice one of these three things, and that's bad. It's this trifecta that makes it so versatile and human friendly.
      • madaxe_again 5y ago
        Developer friendly. Not human friendly. I used to receive CSVs of product data from clients, which they often handcrafted or manipulated by hand, and inevitably, and I mean inevitably, broke. Of course, excel will make just as much of a mess - when the client hands back their sheet with all of their UPCs expressed as exponents and æ€ ligatures jammed next to every apostrophe, there’s no guessing as to what happened. In theory, it’s a great and simple format. In practice, it causes much pain.
        • asdff 5y ago
          Honestly though, if people are doing that with CSVs there is no system where they won't also go off the reservation and do something insane with an excel file or sqlite or whatever. At least with CSVs there are common lines of code all around the internet to wrangle all sorts of issues.
        • bobthepanda 5y ago
          Anything written by hand will be screwed up. Chances are if they're writing it by hand and it's badly formatted, they're not using something that has input or output validation anyways, and they're probably not going to budge from whatever workflow they're using.
      • xtracto 5y ago
        > access through cat/grep/awk, and easily load into any programming language Until the CSV fields contain commas themselves. Even if fields are surrounded by "".
  • grenoire 5y ago
    I think this issue can be bypassed if a better format such as Arrow or Parquet can be used in Excel.
    • masklinn 5y ago
      Ideally excel would just speak sqlite. Not import/export or connect via odbc, straight be an sqlite client.
    • cryptonym 5y ago
      That doesn't qualify as "good enough to be compiled by humans". We need something dumb and easy you can author from scratch with any text editor, and read/understand with just `cat`.
    • pdonis 5y ago
      CSV isn't supported in Excel because Microsoft prefers it. MS prefers its own Excel format. Excel supports CSV because a large enough majority of Excel users want it, which means MS includes support for it. For MS to remove CSV support in favor of Arrow or Parquet support would not benefit MS at all, and would make many of its customers push back, so I don't see it happening.
      • mr_toad 5y ago
        Even if they did support Parquet they’d use the same embrace and extend mechanisms they do with CSV files.
    • xtracto 5y ago
      Interesting that the two main "alternatives" mentioned here are columnar data storages. The problem I see with both Arrow and Parquet are that they are binary-based files. Sure, binary files can be VERY efficient. But the "magic" behind files like CSV, JSON or YAML is that they are both "sufficiently" both human and machine readable. Maybe a CSV killer would be a human readable columnar based file format. Nevertheless, the article basically discusses the issues encountered with the manual "editability" of CSV files, not so much with its performance. It also mentions parquet or arrow and concedes that they require specialized format to read/write. If we are looking to that, then there are a lot of options such as sqlite format, BerkleDB (used by some cryptocurrency projects) among plenty of others.
  • masklinn 5y ago
    And CSV can be consumed and produced by spreadsheet software, which is useful to critical in many contexts where CSV is part of the pipeline. There the alternative would be to use xlsx files which… isn't necessarily any better. Until someone gets excel to ingest and produce something in a better format, we're pretty much stuck.
    • Sanzig 5y ago
      As I mentioned down-thread, I can generate a CSV with a couple of fprintf statements and a loop. I definitely can't do that with .xlsx. There is almost zero friction to bolting CSV export capability to an existing system, which is part of why it's so popular.
      • masklinn 5y ago
        > As I mentioned down-thread, I can generate a CSV with a couple of fprintf statements and a loop. And usually generate garbage for anything but the most trivial case, which really nobody gives a shit about. That's the main reason why CSV absolutely sucks too, you have to waste month diagnosing the broken shit you're given to implement the workarounds necessary to deal with it. > I definitely can't do that with .xlsx. You probably can though. An xlsx file is just a bunch of XML files in a zip.
        • pdonis 5y ago
          > An xlsx file is just a bunch of XML files in a zip. A bunch of XML files with opaque formats that MS constantly makes changes to to make its competitors have to keep chasing the format.
          • masklinn 5y ago
            Even if it were true it wouldn't matter a whit to the production side of the format, which is what "produce CSVs using fprintf" is: excel can consume them all.
  • acwan93 5y ago
    Also, "will the other side adopt it?" After working with so many retailers and online sales channels, things that are considered "legacy" or "outdated" by the HN crowd doesn't seem like it will go away unless both sides make a change. There are numerous articles posted on HN about how "FTP is dead" or no one uses it anymore, when it's far from the case. Even Amazon's marketplace and vendor files are still using SFTP and EDI files. They've recently made changes, but it's been slow and hasn't had widespread adoption. There's also the universality and "simplicity" CSV provides to the non-computer literate, and convincing them to make a change to a new standard provides itself some non-technical challenges. CSV is a bad standard, but it's the best one given what it does and its flexibility.
  • danellis 5y ago
    What are all these hypothetical humans doing with CSV files? I've worked with them a lot, and the only time I read one manually is to find out why it isn't parsing properly. I'm not writing them by hand. I'm not extracting data from them by hand. What are other people doing?
    • crazygringo 5y ago
      Mainly Ctrl+F and diffs, in my case. Sometimes you need to look up a single code in a 50 MB file. And sometimes you need a quick check to see if one line or a million lines changes. It's "exception not the rule" type stuff... but it sure comes in handy to be able to check this stuff quick with basic text tools than have to run it through some binary parser. Same as JSON. But unlike protobufs for example.
      • danellis 5y ago
        Interesting. I would reach for `grep` and `diff` in those cases, so it's not much of a leap to imagine similarly using a tool for a binary format. After all, I only need the textual representation of the thing I'm searching for, not the entire dataset.
        • crazygringo 5y ago
          Sure, but those specific tools need to be written for each specific binary format. And honestly they usually simply don't exist. In the vast majority of cases, in my experience.
• habitue 5y ago
  It's probably not news to anyone who works with data that CSVs are inferior to other file formats like parquet. I think the valuable insight here is that there needs to be a meme / movement that CSV is bad or deprecated. That's what's actually going to put the nails in its coffin, not private griping from developers when they get CSVs. I'm all for it. Down with CSV :)
  • drewmate 5y ago
    I'm with you! But what does a hot new startup do when a rich old legacy corp sends them a data dump that isn't a custom sqlite database? Slowly but surely, the startup begrudgingly uses csv "just this once" until they grow into the legacy corp that sends out files in csv because "we've done it this way forever." And the cycle continues.
  • Sanzig 5y ago
    The thing is, I need a hefty library to write parquet. I can write a CSV with a couple fprintf statements and a loop. A parser isn't much harder if we can make assumptions about CSV dialect ahead of time. If I'm trying to make an exportable format for a data logger with an SD card running on an ARM microcontroller, it doesn't get much easier than CSV. Sure, I could save space by rolling my own binary format, but then I have to provide a PC application to read it (and realistically, the user is probably just going to want that application to dump to a CSV anyway!). I agree that for many use cases there are much better alternatives, but one of the reasons CSV is so popular is because it's so simple. It shouldn't be used for multi-gigabyte datasets, but for many simple use cases it works great.
    • dingosity 5y ago
      no. you can't write CSV with a couple of fprintf statements in a loop. If your data has a comma or a double-quote in it, the field needs to be surrounded by double quotes (and the double quote needs to be doubled). Ditto if you have leading or trailing spaces. that is... if you're following the RFC 4180 guidance for CSV. microsoft has another internal standard that occasionally gets referenced.
• TrackerFF 5y ago
  I agree with many of the problems - but luckily many providers (of data) use the first lines to provide a header of metadata.
• coding123 5y ago
  As long as it's replacement opens in Numbers and Excel.
  • MeteorMarc 5y ago
    CSV does not open in Excel, unless you play the usual tricks first...
    • nolok 5y ago
      If Jane from accounting and Bobby from marketing know those trick (and they do) then Csv does open in excel, and its replacement has to do at least as good.
    • deleted 5y ago
      [deleted]
    • oytis 5y ago
      Just curious - what tricks are required? localc opens csv without an issue.
    • NBJack 5y ago
      Not sure what your basing that claim on. It is a natively supported format, even amidst its inconsistencies, and has been for decades. Are you referring to a particular format variant?
      • rutthenut 5y ago
        Excel definitely opens CSV files, and Windows file associations tend to set that up by default. However, Excel then goes ahead with changing the content to often wrongly-assumed formats. My favourite hate is how it will convert mobile numbers from a string of digits to an unusable exponential-format number. Hey, it doesn't even set column widths to make the content visible!
        • nojito 5y ago
          You should use power query to load csvs into excel that way you can control the parsing of columns.
• MonadIsPronad 5y ago
  Surely the successor should be SQLite. Tabular data like CSV, easy to view on different mediums, free and open source, single file databases. It has to be this, right?
  • JohnHaugeland 5y ago
    yeah, why not replace a dense human readable easily generated format with a third party format that requires installation of compiled binaries and an over the wire protocol
    • tzs 5y ago
      An SQLite dump is human readable and the data in the dump actually is in a CSV format, just surrounded with information on types and relationships. Here's the first few lines for example of a dump of an SQLite DB that I have of some temperature sensor data: PRAGMA foreign_keys=OFF; BEGIN TRANSACTION; CREATE TABLE temperature ( ts integer, Tc real, Tf real, src_id integer ); INSERT INTO temperature VALUES(1615165342,5.5999999999999996447,42.099999999999999644,1); INSERT INTO temperature VALUES(1615165350,0.6,32.999999999999998223,3); INSERT INTO temperature VALUES(1615165404,5.5,41.899999999999995026,1); INSERT INTO temperature VALUES(1615165410,-17.199999999999999289,1.0,2); INSERT INTO temperature VALUES(1615165435,5.5,41.899999999999995026,1); SQLite dumps would actually be a pretty good data exchange format. As mentioned there is a CSV inside there. It's not hard to extract that if you need an unadorned CSV--some grep and sed, or grep and cut, or a few lines of scripting. Or if you have SQLite installed, it is an easy to have SQLite itself read the dump and export it in CSV, letting it deal with things like quoting that can be a pain with grep and sed/cut. This way also makes it easy to rearrange or omit columns and to do some filtering so that data you don't care about doesn't get into the CSV.
  • puppet-master 5y ago
    Replacing an almost trivial open format with one that requires a specific 85 kLOC codebase to access does not seem like an improvement. SQLite is wonderful, but it's definitely not a replacement for CSV
• javajosh 5y ago
  More correctly titled, "I Don't Like CSV".
  • fridif 5y ago
    Hahahah. Allow me to submit mine as well: "I Cannot Fathom A Use Case For CSV So I Would Like To Ban Everyone From Using It For Any Reason"
  • cogman10 5y ago
    There are probably valid uses for CSV, but more often than not, it's the wrong choice. As soon as data has any form of structure to it (and most data does). CSV complicates everything. Even for unstructured data, the problem of escape characters often shows it's ugly head. The moment your data contains a comma, tab, or space, you run into a nasty mess that, in the best case makes your system fail, and in the worst case silently adds corrupt data into the system. Neither JSON nor XML suffer from that problem and both can easily be used in any scenario you'd use CSV. The only argument against either format is they are a bit more bulky than CSV.
    • fxleach 5y ago
      Just an FYI, JSON and XML both have characters that need to be escaped properly.
      • cogman10 5y ago
        Correct, and those methods of escaping are clearly defined in both specs. In CSV, there is no universal escaping system. It's all over the board. Any variable length text data format is going to run into issues when special characters are used in the data. CSV has no definition of what should happen when that occurs.
        • fxleach 5y ago
          The characters to escape are clearly defined in both specs, but that doesn't mean there's a universal escaping system. Just google "how to escape JSON" and you will see the wealth of users struggling to understand what and how to accomplish this. There will always be people struggling with escape characters, I don't see it being any harder or easier in any format.
• marcelotournier 5y ago
  https://xkcd.com/927/ https://xkcd.com/927/
• yuy910616 5y ago
  CSV is bad - but convenience is hard to beat. It's the same thing with excel. People abuse it, but you just can't beat the fact that your programmer, CEO, analyst, and secretary can all contribute to the same file.
  • fuhrysteve 5y ago
    Yeah, this is pretty much it. The author complains about CSVs being "notoriously inconsistent" as though switching to some other format would magically change that. They're only inconsistent because sometimes lazy programmers do ",".join(mylist) instead of using an RFC4180 compliant CSV writer. Lazy programmers will just use non-compliant methods of creating whatever magic format OP is dreaming about. Case in point: trailing commas in JSON objects, and other ridiculous things that people have come up with such as encoding a date in JSON like this: "\/Date(628318530718)\/" https://docs.microsoft.com/en-us/previous-versions/dotnet/articles/bb299886(v=msdn.10)?redirectedfrom=MSDN#intro_to_json_topic2 https://docs.microsoft.com/en-us/previous-versions/dotnet/ar... CSVs also are great because you can parse them one row at a time. This makes for a very scale-able and memory-efficient way of processing very large files containing millions of rows. Let there be no mistake: Everyone reading this today will retire long before CSVs retire. And that's just fine by me.
    • ivanbakel 5y ago
      >CSVs also are great because you can parse them one row at a time. This makes for a very scale-able and memory-efficient way of processing very large files containing millions of rows. Even RFC4180-compliant CSVs can be incredibly memory-inefficient to parse. If you encounter a quoted field, you must continue to the next unescaped quote to discover how large the field is, since all newlines you encounter are part of the field contents. Field sizes (and therefore row sizes) are unbounded, and much harder to determine than simply looking for newlines - if you were to naively treat CSV as a "memory-efficient" format to parse, you would create a parser that would be easy to blow up with a trivial large file.
    • newbie2020 5y ago
      Hah, I use the join function when I write them. Can you elaborate upon why that’s bad?
• mdellavo 5y ago
  please don't - CSV is a perfectly fine textual file format for many things that can be used by the technical and non with a wide range of support in applications and programming languages.
• podiki 5y ago
  As long as it is text-based. At least you can actually look at a CSV and see what is going (wrong), as well as use all the text tools we have. Not that there aren't problems as the article points out. But some binary file based on the whims of a proprietary program...no thanks.
  • nolok 5y ago
    Xlsx (office format for almost a decade now) are zip and XML all the way. Not fun to look at, but totally readable by a human.
    • dullgiulio 5y ago
      Readable but not comprehensible. CSV is hard to beat in that sense, being somewhat "natural" like a table written on paper.
    • daxuak 5y ago
      Human can't efficiently write or parse XML or json, though. In some scenarios CSV hits the right spot to be accessible to human and computer, and the table can be laid out so that one can sort/grep/awk to quickly gain some insight.
      • cogman10 5y ago
        As someone that works with maven and npm.... what? This is valid JSON [["bob", "jones", 1, 22], ["frank", "was", 32, 45]] That's unreadable and unparsable by a human? Not only is JSON often more parsable, because it's structured it also becomes a lot easier to query. I grep and awk xml and json stuff all the time. I also have the added bonus of being able to use `jq` for json content.
        • shapefrog 5y ago
          Thats just a CSV with extra steps.
• zepolen 5y ago
  CSVs are plain text, and that's huge, plus they have a few other advantages: 1. You can read them without any software 2. Streamable row by row 3. Compress well To be honest most of the points in this article could be addressed by standardizing a method of defining a schema for the CSVs. It could even be backward compatible by appending the definition as metadata on the header column or as a separate file. One thing that would be good to have is a standardized method of indexing CSVs so that random access is possible too, though that would be more involved.
  • jslaby 5y ago
    I think an ideal solution would be csv with json metadata elements as the header. If a program supports the header, then you get the metadata. If not, no big deal, you just get an ugly header.
    • zepolen 5y ago
      Perhaps, though maybe something simpler and readable would be easier to get people to use name::string,number of legs::int,height in meters::float,date of birth::date(MM/DD/YYYY),email adress::email,website::url joe,2,1.76,12/12/1999,joe@joe.com,https://www.joe.com bob,1,1.84,12/12/1944,bob@vietnam.com,null
• deleted 5y ago
  [deleted]
• Sunspark 5y ago
  I don't agree with giving up csvs until the following conditions are met: 1) A truly open format is available and accessible. Csvs are textfiles. There is no system around that cannot open a textfile. If the format is binary or requires patents or whatever, then it's a non-starter. 2) Applications have a speed increase from using csvs. To wit, I loved csvs because often they finish preparing much faster than a "formatted" output in excel, etc. and sometimes I just want to see the numbers, not have everything colored or weird features like merged cells in excel, etc. 3) The new format should not be grossly larger than the one it is replacing. Extracts in Excel format are orders of a magnitude larger than csv in filesize. This affects run-time to prepare the extract, open it (memory constraints, etc.) Is there truly a problem? The author is not forced to use csvs.
  • Twisell 5y ago
    OP and you gave me an idea : "The only true successor of CSV should be forward/backward compatible with any existing CSV variant" If we manage to write a spec that meet this criteria we'll have a powerful standard with easy adoption.
    • masklinn 5y ago
      Given the infinity of CSV variants existing is the very reason why CSV blows goats, creating a spec compatible with that is 1. a fool's errand, CSV "variants" are not compatible with one another and regularly contradict one another (one needs not look any further than Excel's localised CSVs) 2. resulting in getting CSV anyway, which is a lot of efforts to do nothing
    • ivanbakel 5y ago
      That's ignoring two big points in the OP: that CSV variants are ill-defined and conflicting; and that existing CSV data is too poorly-specified. There is no reason to try to be "backwards-compatible" with existing CSV files - we don't have a single definition of correctness to use to check that the compatibility is correct. Every attempt to be parse existing data would result in unexpected results or even data loss for some CSVs in the wild, because there is no way to reconcile all the different expectations and specifications that people have for their own CSV data.
• JohnHaugeland 5y ago
  LOL, no The only appropriate replacement for CSV would be a better defined CSV that gave hard requirements for things like field quoting
• dataminded 5y ago
  I've been moving my organization to Sqlite and Parquet depending on the use case.
• radmuzom 5y ago
  Every few years an article like this pops up. I find it tiring - because they are primarily from a software engineer's viewpoint who is probably trying to write a parser and needs to handle the edge cases. As a data scientist, I receive and process around 75GB of CSV every day - of course I don't process it manually. Our processes have been running a few years now and millions of dollars of revenue rides on it. I don't see any need to retire anything, and am pretty sure our processes will be working and running 10 years from now. The only thing which will change are some statistical models which make use of this data.
  • deleted 5y ago
    [deleted]
  • turtlebits 5y ago
    It'll work until someone upstream upgrades their CSV writing library and your process breaks. But it's still writing CSV.
    • nightski 5y ago
      So you can constrain what type of CSV you will allow and if this happens it will bail. It's that simple. There is nothing wrong with having additional constraints on top of just saying it must be "CSV" especially in these scenarios. I'm in a similar situation, we've been using CSV for over a decade to move billions of dollars worth of product each year. It just works.
      • turtlebits 5y ago
        I'm pretty sure most devs are going to use whatever CSV library that comes with their language. When that breaks, it's generally not a simple fix.
        • kcartlidge 5y ago
          > I'm pretty sure most devs are going to use whatever CSV library that comes with their language. When that breaks, it's generally not a simple fix. Call me a yak-shaver, but in every language I've worked with I've written my own csv parsing library when I needed one. It's such a trivial thing for the majority cases (varying of delimiters, line-endings, ascii/uft8, quoting, escaping, and embedding of delimiters/line-endings) that it takes barely no time at all after you've done it once in another language. Of course there are edge cases and special cases depending upon specific workloads, but if your team has it's own parser (which is a small amount of obvious code) then it does indeed usually become a simple fix. Sounds good using someone else's library, but below a certain complexity it's rarely worth it in the medium to long term except for when doing proof of concept or demo code, or if the domain space is complex.
• kaiken1987 5y ago
  The whole point of CSV is that it's simple. You don't need and special libraries. If you can write hello world to a file you can make a CSV and more often than not it'll just work. Yes somethings, like DNA, shouldn't be CSV files. But don't blame the tool for bad craftsmanship. There are more things that it works for than doesn't. As a side note dates should be ISO 8601
  • deleted 5y ago
    [deleted]
• rexreed 5y ago
  CSV will never go away. But surely we can build better systems to handle all the complexities and variability in CSV? If we can handle image recognition with all its complexities, we can handle the more constrained variety in CSVs. There are only so many variations to CSV files, and perhaps a better preprocessor is the answer here versus asking for the near-impossible task of "retiring" CSV.
  • deleted 5y ago
    [deleted]
• johnrob 5y ago
  I find JSON “array of arrays” to be a better solution while also remaining human-readable. Especially when formatted as one array per line, analogous to CSV.
  • xtracto 5y ago
    Right on. And with the inclusion of a JSON-schema header, it makes data parsing a breeze: { "$schema": "http://json-schema.org/draft-07/schema#", "type": "array", "items": {"type":"array","items": [ { "type": "number" }, { "type": "string" }, { "enum": ["Street", "Avenue", "Boulevard"] },{ "enum": ["NW", "NE", "SW", "SE"] } ]} } [[3,"some", "Street", "NE"], [4,"other", "Avenue", "SE"], [5,"some", "Boulevard", "SW"]]
    • Existenceblinks 5y ago
      With draft 2020-12, you can even have module-like based on [Compound Document spec](https://json-schema.org/draft/2020-12/json-schema-core.html#rfc.section.9.3 https://json-schema.org/draft/2020-12/json-schema-core.html#...) Now you have tables and relation. Only few people understand the usefulness of Compound Document, currently.
  • lufte 5y ago
    Parsing a CSV can be done in a line-per-line basis, while a an array of arrays in JSON is not valid until you reach the end. How would any existing JSON parser handle 75GB of data in a single array of arrays?
    • WorldMaker 5y ago
      Many JSON Parsers already have an incremental forward read mode (think SAX-style parser if you are familiar with XML parser styles) where you can ask for each array inside that array of arrays one at a time as it reads them. If something unexpected happens at the end of the large outer array such as syntax error you can decide at that point if you rollback what you've already read/operated on or not. JSON.Parse() in the browser isn't that sort of parser obviously, but there options in many languages such as both major .NET JSON libraries (Newtonsoft and System.Text.Json) have ways to parse that way. Similar examples exist in other languages too.
• deleted 5y ago
  [deleted]
• AdmiralAsshat 5y ago
  CSV was a thing long before I was born, so I'm not privy to how it came about. But at least in day-to-day work, the single biggest drawback of CSV in my experience is the fact that the comma and most of the other common delimiters occur regularly in real data, forcing all of the cumbersome escape sequences. To say nothing of someone misplacing a quote somewhere and throwing off the cell count. So, question to the greybeards: when this format was coming about, why didn't we use one of the dedicated Data Structure separator control codes (e.g. File/Group/Record/Unit separator) that were part of the ASCII standard? https://en.wikipedia.org/wiki/C0_and_C1_control_codes#Basic_ASCII_control_codes https://en.wikipedia.org/wiki/C0_and_C1_control_codes#Basic_... It seems like it would've saved us all several decades of headache.
  • rutthenut 5y ago
    Agree that the commma delimiter is a pain, as it is a frequently used character in the content. Then adding escape-type chars or quotes around fields just makes things more and more messy. Consider also that IBM EBDCIC and other non-ASCII character sets were (and are) in common use, so C0/C1 may not have made sense 'back in the day'
  • NyxWulf 5y ago
    Csv is only one form of delimited files. Tsv or Tab Separated Values is yet another. If you have either field delimiters or row delimiters (new line) in your data, change your delimiters and process appropriately.
  • wanderingstan 5y ago
    Some people did. But commas can be seen, edited, and typed with ease in any text editor. As per comment above: your CEO, SWE, or secretary can all use or contribute to a csv file. And using an easily recognizable and typeable separator has proven to be worth the downsides.
  • _moof 5y ago
    > why didn't we use one of the dedicated Data Structure separator control codes (e.g. File/Group/Record/Unit separator) that were part of the ASCII standard? Because you can't see them. CSV is, at its core, a text format. Using FS/GS/RS/US would effectively make it a binary format. Also, what happens if one of those bytes appears in data? The simple fact is you will never be able to come up with a text-based format that can handle all possible values without escape sequences, quoting, or length-encoding. And really, that's not that big a deal. It just means you have to write a simple state machine instead of using regex or your language's equivalent of split().
• PaulHoule 5y ago
  XSLX covers a lot of ground in this area, works with the market leading spreadsheet program, but there are also great libraries to encode and decode them in Python, Java and other languages.
  • andylynch 5y ago
    I agree, xlsx is pretty easy to deal outside Excel (at least until you hit 1M rows!).
• deleted 5y ago
  [deleted]
• poorman 5y ago
  I would agree, csv is probably not the way to go. But try to go implement a Parquet reader and then see how nice processing a streaming gzipped csv file can be.
• _jal 5y ago
  The more interesting question is to try to tease apart what led to CSV's survival, despite all the warts. Clearly a combination of things, including accident. But I'll point out some advantages: - Repairability by mere mortals. If you are competent enough to use Excel and are given bad data, you are competent enough to fix it. (Whether the dataset is too big for mere mortals to find the problem is a different issue.) - Trivial serialization. It is super-easy to dump things to CSV. (This is probably also why there are so many annoying variants.) - Usable by other tools. You don't need special libraries to read it, so pipelines involving the usual suspects is possible. Importantly, there are a ton of different ways to do this sort of thing, so non-experts can frequently find something that works for them, even if it looks wonky to programmers. All that said, I hate dealing with them, too.
• hestefisk 5y ago
  20 years ago, clever consultants said the same when XML was the “next big thing”. I remember reading this book called “The XML Revolution”, which boldly stated that the age of CSV and other “obscure” formats was finally over. Here we are 20 years later with the same rhetoric. CSV isn’t pretty, no, but it’s universally acceptable and works quite nicely on a Unix command line with cat, grep, and cut, so I am sure it will stick around for longer.
• zabzonk 5y ago
  As the author of a CSV munging tool (CSVfix) I think most of the problems with CSV could be fixed if people producing CSV output, and people reading CSV input obeyed the rules of the RFC. Sadly, most people don't, and any textual output or input is routinely described as CSV, when it is nothing of the sort - even to the extent of not being comma-separated!
  • masklinn 5y ago
    > Sadly, most people don't And the important think to remember is that you can not and will not make them.
    • zabzonk 5y ago
      No, but you could possibly suggest to them that what they are producing is not CSV. Otherwise, any sort of description becomes meaningless.
  • javajosh 5y ago
    Is there something like CSVlint? Would be useful particular in the output stage, a kind of functional runtime test to make sure your program's output is valid and consumable.
    • zabzonk 5y ago
      Validating CSV syntax is kind of hard - in the worst case you would have to read the entire CSV file.
      • hermitcrab 5y ago
        Even then it seems that there aren't that many checks you can do, other than: -warning if different rows have different numbers of columns -warning if the file ends without closing escaping Without meta data you can't check data types. A missing escape character might result in a valid CSV (just not the one intended).
  • 5y ago
• andylynch 5y ago
  The author seems to be advocating for something like ISO 20022. Which is great if you actually need it. But also complete overkill if you aren’t doing the kind of things it’s made for (and even then, still a bit painful) CSV is at the other extreme but this is also why it’s so useful.
• RobLach 5y ago
  I can generate a CSV that can be read in excel, etc.. extremely easily in a terminal. Just yesterday I was trying to add some metadata to a list of images that I handed off to a designer and I just piped a single "find . -fprintf" command into a file. To "retire" something so simple and utilitarian makes no sense. There's plenty of formats that deal with all the issues described in this. It's like saying we need to retire plain text files because it's confusing to know how they should be displayed.
• punnerud 5y ago
  There is a CSV standard (RFC 4180), the problem is that a lot of programs don’t follow it. DataGrip don’t quote all text fields and Python remove some of the \r\n or \n\r (don’t remember which). As long as you are following RFC4180 it works. I ended up exporting from Oracle DB using JSON and converting to CSV for one of our contractors to be able to import the data to MongoDB.
• dasil003 5y ago
  Interesting thought experiment that feels good after struggling with some thorny data corruption issues (or whatever inspired this), but this what they call a boil-the-ocean problem. There's no point in thinking about CSV beyond the impact on things that you directly touch as it will never rise above the background noise of the problems faced by all the people whom you hope to inspire.
• progre 5y ago
  The least fun csv-like format i have seen was mandated by a Big Spanish Bank. It was original a fixed fieldwith flatfile. Numerals were 0-leftpadded and fixed decimal (decimal point not written). Except that someone decided they needed to open it in Excel so semicolons were added at the end of each field. Then they discovered that some data didn't actually fit in the fixed with fields. So certain fields where variable width. End result was a file that looked like shit in Excel, and couldn't be parsed by a fixed width parser.
• mark-r 5y ago
  They complain about the loss of metadata, but never get into specifics. I'm surprised they didn't mention the biggest example of this. Excel is forced to guess the type of input data it's reading, and sometimes it guesses wrong and messes up your data. A number of genes had to be renamed because too many genetic markers were being interpreted as dates: https://www.theverge.com/2020/8/6/21355674/human-genes-rename-microsoft-excel-misreading-dates https://www.theverge.com/2020/8/6/21355674/human-genes-renam...
• osigurdson 5y ago
  HDF5 is efficient for large datasets and has its use cases but being a binary (and fairly easy to corrupt) it isn't a reasonable general replacement for CSV. It also has limitations such as it cannot be read while it is being written.
• hcarvalhoalves 5y ago
  1. I wouldn't bet Avro, Parquet, Arrow, or other formats that require a library to parse are going to _entirely_ replace CSV. Those are broader in scope, but can't say are "replacements" if they don't share the simplicity of plain text data following a few conventions – much like how PDF is not a "replacement" for Markdown, but enables higher-fidelity content. 2. I would feel safer storing longer-term data in CSV than in a binary format w/ a complicated spec. Having to make sense of compressed, columnar padded data sounds worse than parsing CSV. 3. Although it's easy to point at corner cases, I don't remember the last time I couldn't figure out how to parse a file because of inconsistent quoting or exotic char encoding – and I've spent a good amount of the past 13 years exchanging CSVs and TSVs w/ 3rd parties full of horrible legacy. Asking those 3rd parties to send me an Avro/Parquet/whatever file would've made the project fail or take 10x longer. There's a reason why CSV stuck around so long, and the alternatives make different trade-offs but miss the pros.
• psanford 5y ago
  CSV files are terrible, but I love them. I love sites that offer an "Export to CSV" option, because I know I can take that export and start working with it immediately. I can give that CSV file to my Dad, who can open it in Excel, or I can run a single command[0] to import it into a sqlite database. It is a lowest common denominator format. That type of thing is incredibly hard to kill unless you can replace it with something that is simpler. Good luck with that. [0]: https://github.com/psanford/csv2sqlite https://github.com/psanford/csv2sqlite
• kissgyorgy 5y ago
  Once my CSV parser blew up because there was a monetary value bigger than $1000 (I mean bigger than $1,000) in a field and the field values were not quoted.
  • bluedino 5y ago
    Garbage in, garbage out.
• parksy 5y ago
  There are better formats but the change needs to come from the top down, I can't see it working from the grass roots. Put it this way, I'm not going to turn around and refuse a non-technical client who volunteers at a non-profit because they can only give me data in Excel rather than whatever I decide the superior format is, or tell them it's impossible when the next developer will click a few buttons and have me taken for a fool, nor will I push hard to sell them up on a data mapping service or cloud pipeline if it's well outside their needs or budget. Unlike topics like web accessibility that's easy to argue on its morals and ethics, or mobile first that's easy to argue based on market share and audience reach, refusing to work with an organisation's data because you don't like the very simple, very established format is a very tough sell. If Excel could natively export to JSON, that's what I'd ask for, or their custom-built website from the 00's had a clean REST API, then it wouldn't even be a discussion. There are workarounds for sure; VB macros, unzip XSLX and parse the XML, write scripts to automate Excel-to-whatever using DCOM, or import into an intermediary service and re-export into the format de-jure, but that all takes time and costs money too, and often causes confusion when even spoken about. Asking for a CSV takes a couple of seconds and is easily understood by most people. Anyone experienced with importing / manipulating CSV data can deal with the variance in delimiters and escape sequences without major issues. It's a headache at times but easier than the alternative of alienating or confusing clients who are looking for a simple solution to whatever issue they have today. On the other hand once the data is in the target system, if they're still asking for CSV exports I do probe to ask why, and try and figure out if there's a better way. Reporting is the usual reason, and plenty of the CRMs I work with have built-in reporting that can replicate and improve upon whatever spreadsheet they are using, and have APIs that can interface with cloud-based reporting services. But there's a lot of inertia against change in most small-to-medium organisations, not everyone is a data expert and you can't sell someone something they can't use or understand. Ultimately people win, and the solution ends up being a balance of hopefully incremental technological improvements that they can still integrate into their day-to-day. Not every organisation is able to undergo a full digital transformation with time, budgets, and skills at hand. I agree with the sentiment but I'd hate to see a future where everyone's locked into proprietary ecosystems - not that that is what's advocated for in the post, but we have CSV because that's what the big platforms seem to allow, not because they don't know there are better options. There's no technical reason Excel couldn't export to WordPress or SuiteCRM. Take CSV away and it gets harder, not easier, to move between platforms.
• bbertelsen 5y ago
  EMBRACE PARQUET.
• edoceo 5y ago
  Why not simply use the ASCII characters dedicated for this? STX,ETX, RS, GS, etc
• qwerty456127 5y ago
  > It's Time to Retire the CSV > This column obviously contains dates, but which dates? Most of the world It's time to retire local formats and always write YYYY-MM-DD (which is both the international and the Swedish standard, and the most convenient for parsing and sorting). > A third major piece of metadata missing from CSVs is information about the file’s character encoding. It's bloody the time to retire all the character encodings and always use UTF-8 (and update all the standards like ISO, RFC etc to require UTF-8). The last time I checked common e-mail clients like Thunderbird and Outlook created new e-mails in ANSI/ISO codepages by default (although they are perfectly capable of using UTF-8) - this infuriated me. > If not CSV, then what? ... HDF5 Indeed! Since the moment I discovered HDF5 I wonder why is it not the default format for spreadsheet apps. It could just store the data, the metadata, the formulae, the formatting details and the file-level properties in different dimensions of its structure to make a perfect spreadsheet file. Nevertheless spreadsheet apps like LibreOffice Calc and MS Excel don't even let you import from HDF5. > An enormous amount of structured information is stored in SQLite databases Yet still very underused. It ought to be more popular. In fact every time I get CSV data I import it to SQLite to store and process but most of the people (non-developers) have never heard of it. IMHO it also begs to be supported (for easy import and export at least) by the spreadsheet apps. A caveat here is it still uses strings to store dates so the dates still can be in any imaginable format. Fortunately most of the developers use a variation of ISO 8601 conventionally. And by the way, almost every application-specific file format could be replaced by SQLite or HDF5 for good. IMHO the only cases where custom format make good sense are streaming and extremely resource-limited embedded solutions.
  • CobaltFire 5y ago
    Honestly I constantly see dates argued about and people state various formats that are still confusing. 4-2-2 of any variety can be confused. Why not 2-3-4 or 4-3-2 (DD-MMM-YYYY or YYYY-MMM-DD)? I’ve never understood why that isn’t more widely used.
    • deleted 5y ago
      [deleted]
    • 5y ago
• csours 5y ago
  I think there's two conversations here: known, working CSVs as part of a workflow; and CSVs from random, unknown sources. There's no such thing as schema-less - there's undefined schema
• dragonwriter 5y ago
  Yes CSV sucks, but beyond that this rant is...quite sloppy. In dismissing YAML and JSON it seems to confuse probability of corruption when using an editor that isn't structure-aware with “lack of precision”, and then treats binary formats as better in this regard. Not only is the terminology bizarre, but the application of the criteria is nonsensical. Binary formats are not less likely to be corrupted when using an editor that isn't specifically aware of the format, and JSON and YAML (especially when accompanied by explicit schemata) are no less amenable to structure (and, when used, schema) aware editors than binary formats are. > To usurp CSV as the path of least resistance, its successor must have equivalent or superior support for both editing and viewing Since the author has already ruled (on spurious grounds) human readability and thus the usability of ubiquitous text editors as incompatible with requirements for a successor format, this is simply impossible. > If Microsoft and Salesforce were somehow convinced to move away from CSV support in Excel and Tableau, a large portion of business users would move to a successor format as a matter of course. Yeah, sorry, you’ve put the cart before the horse. Neither of those firms are going to do that until the vast majority of users have already migrated off of CSV-based workflows, it would be an insane, user hostile move that would be a bigger threat to their established dominance in their respective markets than anything any potential competitor is likely to do in the foreseeable future.
• _moof 5y ago
  This is an example of a genre I like to describe as: programmer objects to a solution that meets everyone's requirements because it doesn't make them feel like a beautiful code-poet. I like elegance as much as anyone. And I think it's a good proxy for other important qualities. But don't prioritize it above building something that actually does the job. Be an engineer.
  • qayxc 5y ago
    Ah yes, and then there's https://www.theverge.com/2020/8/6/21355674/human-genes-rename-microsoft-excel-misreading-dates https://www.theverge.com/2020/8/6/21355674/human-genes-renam... Not to mention the mess that is exchanging documents between different locales. It's all sunshine and roses until you get your CSVs from an office in a different country (which happens a lot in Europe). CSV gets the job done until it doesn't.
    • brushfoot 5y ago
      That's not a problem inherent to CSV the format, though, any more than using Word to edit JSON means smart quotes are a problem with JSON. It's about knowing or not knowing the tools you're using. Unexpected behavior is a potential problem with any tool or format, certainly no less so with the kinds of solutions the article is proposing.
      • qayxc 5y ago
        But the behaviour isn't unexpected at all, e.g. the user wanted to store a number in column 3 and that's exactly what's the tool did. The behaviour is both expected and reproducible, the issue is that it's not communicated by the format. The problem arises from the fact that CSV doesn't contain any metadata so while the same tool reads and writes the same data, the results will be different because the format doesn't specify what a number look like. The formats proposed in the article don't have this problem because they specify both the type and the formatting of the file content so there's no confusion. That's the difference. JSON in particular simply cannot have this problem (though it's not a suggested solution): a number has a well-defined format and that's that. So do strings. This is not the case with CSV: Number,Also a number,A String 1.0,1e15,"Hello there!" "1,0",1e15,"Bienvenue á la maison!" The first row is what you'd expect from a customer in Ireland and the second row might be sent from someone in Switzerland. Both parties agreed to use comma-separated files for data exchange and quoted strings, yet importing the first row using a French locale would fail to parse the first number and same goes for the second row and an English locale. Neither row contains unexpected or malformed data from the respective user's POV. And that's the problem with CSV.
• Sn0wCoder 5y ago
  While it’s great to dream I hope the CSV file stays around until I retire. Still the best way to get ‘simple’ data out of excel and uploaded on the front end (papa parse). More complicated data most likely should be using another format. I guess there is always TSV if the CSV goes away :)
• deleted 5y ago
  [deleted]
• jszymborski 5y ago
  You will pry CSVs form my cold, dead hands. When I have lots of data in multiple related tables where I would benefit from defined data types, I reach for HDF5 or SQLite, but there are so many nice things to say about a simple CSV for lots of simply structured data. Super simple to stream compress/decompress, and the ability to use great CLI tools like xsv, and being able to peak at the data by simply calling less or head... it's just hard to beat.
• tannhaeuser 5y ago
  CSV (well TSV) as a format is a simple as it gets: one special code as field separator, and another one as line/record terminator. Only that CSV/TSV use chars available in text editors rather than the proper (archaic) ASCII C1 codes. Whatever the author has against CSV, I feel like starting a war against CSV is crazy, since any alternative is going to be worse when the problem isn't the format as such, but folks misusing it or using it wrongly (eg. broken XML in RSS, broken JSON, whatever).
  • ziml77 5y ago
    And then a field needs to contain one of those characters and everything breaks. And they're designed to be human editable, so the solution is to complicate parsing by using quoted fields instead of escape characters. Although someone might choose to use escape characters because there's no spec so they can do whatever they want.
  • mongol 5y ago
    I think if only editors and shells supported control codes better and gave them a sane visual representation, we could see a revival for them. Feels like a waste to have them and not to be used.
  • shock-value 5y ago
    Despite the similar names, TSV and CSV are very different, in more ways than just the delimiter. In particular, CSV tries to handle escape sequences but its various implementations do so in different ways. TSV just disallows tab characters within fields, so its specification extremely simple: https://www.iana.org/assignments/media-types/text/tab-separated-values https://www.iana.org/assignments/media-types/text/tab-separa....
• jayd16 5y ago
  I would love to drop csv for something else but I won't trade human readable for a binary format. In fact, I will go even further and say I want something mergeable by source control.
• mrvenkman 5y ago
  > While many programs can’t read or write Excel spreadsheets, almost anything can read and write CSVs, and a human can open a CSV file in any text editor and understand roughly what it contains. Libra Office works in Windows. Google Sheets does a great job too, no?
• mongol 5y ago
  I never thought of Sqlite as a data transfer format before. I know it is frequenly used as application storage format. But to use as data transfer format is a really interesting use case. How much is it used like that in practise?
• Kivutar 5y ago
  No.
• kryptiskt 5y ago
  I have always wondered why there is so little use of the separators in ASCII (the file, group, record and unit separators with codes 28-31). They seem perfect for the job and it would be easy to forbid inclusion of those characters in fields.
  • derbOac 5y ago
    I didn't know they existed, but now I do, and am wondering the same thing!
  • Ekaros 5y ago
    It's sad that so much ascii space is wasted on characters that are used... Really should replace that standard with something better...
• osrec 5y ago
  I can't think of another more efficient format for tabular data that's also human readable with a simple text editor... I'm personally not retiring the CSV any time soon!
• Demiurge 5y ago
  Retiring CSV is as futile as retiring Unix pipes. It's a text format for text manipulations, and there is nothing simpler and more ubiquitous than a new-line and comma delimited table. Simplicity and flexibility has its drawbacks, but the advantages clearly outweigh those drawbacks. So, it will never retire, as long as we use text and blob filesystems that don't have interoperable structured metadata, across the board.
• recursivedoubts 5y ago
  no CSV is the A-10 warthog of data formats
• throwawayswede 5y ago
  Meh. Author probably messed up some important deadline or emailed (or got emailed) some crappy CSV file and then threw a temper tantrum and decided to write this. Every single one of their points are extremely subjective and very wrong. If you have an excel sheet full of equations and colorful cells and then to decide to export it as CSV and open it in Notepad, you really can't complain that that CSV is bad. I mean it's obvious that to each format a set of strengths and a set of weaknesses, and also obviously what could be a strength to someone is a weakness to someone else. The fact that CSV is so simple to parse (almost every modern language can very easily read/write a CSV) makes it a fantastic data transfer format for every single usecase I had (this doesn't mean that other formats are less important of course). Sure you'll lose your Google Sheet or Excel metadata, but this is NOT what CSV is for. What I find fascinating though is that the author decided it's a good idea to make a blanket statement like "Time to retire?" just because they themselves have an issue with SOME use case. I mean the idea that having your personal needs not met to justify arguing that we should ALL stop using CSV is so very bizarre to me, like it's way beyond selfish.
• mrlonglong 5y ago
  CSV files are great but only if things like dates are parsed correctly. Always validate, validate and validate again, that's the critical thing dealing with CSV files.
• crdrost 5y ago
  CSV parsing is not as bad as it sounds and can be a decent fizzbuzzy interview question. The real problem with CSV is the lack of vision of data formats in general. Inside your programming language you need to say that you want to read a CSV? You need to import a different library and change both the parse code and your consumption code to instead do JSON? You need to change everything to use futures in order to stream the results? Are you out of your damn mind? So now that disk space is so cheap it would make sense for any file format to just begin with a single few-kilobytes line that defines the parser for the coming file. Could be sandboxed, WASM-y or something, could be made printable of course... Sure, it might not be possible to get the full SQLite library in there but you could at least make it free to switch between JSON and BSON and CSV without having to recompile the software or force someone to design modular file input systems. Somehow the only flexible containers that hold different data structures are video container formats that do not care about what codec you used. They "get it"— can the rest of us?
• thrower123 5y ago
  CSVs would be fine if Excel didn't import them in the most brain-dead way imaginable by default.
• philistine 5y ago
  Getting rid of human-readability is doomed to fail at getting any traction. CSV being human-readable is the sole reason for its pervasiveness. What we need is something akin to Strict Markdown. Something that qualifies every edge case to produce a strict CSV that can encompass human-readable metadata within the strict delimiters.
• bazzargh 5y ago
  I'm kinda surprised this references RFC-4180 as the most formal spec when there is a _much better_, and more realistic, spec developed by the W3C csv on the web working group (https://www.w3.org/2013/csvw/wiki/Main_Page https://www.w3.org/2013/csvw/wiki/Main_Page) https://www.w3.org/TR/2015/REC-tabular-data-model-20151217/#parsing https://www.w3.org/TR/2015/REC-tabular-data-model-20151217/#... ... at the time RFC-4180 came out, it didn't even accurately describe how to read csv produced by Excel - which was already inconsistent in line endings and character set between office versions and platforms. The w3c spec at least tried to offer a model which would parse junk csv if you could guess the metadata (by eg scanning for the BOM, \" vs ""[^,], and so on) When I worked on this stuff early 2010s, if you wanted to produce a non-ascii csv _download_ that could be opened by all office/openoffice variants you were out of luck. UTF-16LE-with-BOM, as I recall, would work in _most_ office variants but not consistently even across minor version changes in Office for OSX - so it was just a roll of the dice. We offered multiple download formats which _could_ handle this but csv was required by some customers. Anyone saying csv is easy never worked with it in an international context.
• dingosity 5y ago
  When I worked at Amazon, I had a t-shirt and bumper sticker made up that said "Show me again on the doll where CSV hurt you." I can't remember a time that such a simple file format had such wild inconsistencies. Not to mention some CSV export functions just ignore them. Put a comma inside a CSV field? why not? Put a single double quote in a CSV field? sure! Insist that leading (or trailing) spaces in a CSV field are semantically important? OF COURSE! If CSV was used consistently, it wouldn't be that bad. But it's apparently simplicity lulls developers into a false sense of security, which is part of what the original author seems to be saying.
• pharmakom 5y ago
  What are good alternatives to CSV that provide all of these features: - easy to parse - easy to edit with a generic text editor - easy to edit with a widely available GUI, like LibreOffice - allow adding more data with only append operations
  • mongol 5y ago
    I recall ESR's book about Unix conventions and culture mentioned something I think he called stanza-based. A file format where each line is a value and records separated by empty lines. Don't think Office tools can work with that though, except as regular text files.
    • dredmorbius 5y ago
      Awk will eat that for breakfast no problem. The usual problem is when you discover that what you thought was a value is in fact a record of its own, with internal structure. The problem isn't the CSV. The problem is that data parsing is actually a Hard Problem. The author of TFA is ... naive and misguided.
• unixhero 5y ago
  No
• bastardoperator 5y ago
  I'm going to get downvoted, but I'll ask, who cares? You may have perfectly valid reasons to not like CSV, but it's choice at the end of the day to use it or not. Maybe you didn't make the choice, and inherited a system that does, but I much rather read an article about how you changed minds and/or switched to something more advantageous for you and your system versus what amounts to a rant. It's not time to retire CSV, it's time to retire the mentality that just because you don't like something doesn't mean it's wrong or needs to go away.
  • donretag 5y ago
    It is pure clickbait
• thibran 5y ago
  For the start I would be happy if Excel would, in the German version, by default start to separate CSV values by comma and not semicolon. This "bug" did cost us alone hundreds of support hours. We fixed it by switching to XLS, but still CSV is out there and causing headaches (Mailjet for example can't create correct CSV files - luckily SQLite can read them). Microsoft -> The name of the format is 'comma separated values' not 'semicolon separated values'!
  • mongol 5y ago
    Problem is, many countries use comma as decimal separator. Which makes comma inconvenient as CSV separator.
    • thibran 5y ago
      This can easily be solved in CSV by escaping or quotation.
    • unnah 5y ago
      Just use a decimal point instead. Switching the delimiters depending on locale makes it extremely impractical to use CSV for data interchange with continental European Excel users. Microsoft Excel is the only program I know that insists on locale-specific interpretations of CSV. To read a standard CSV into Excel in a continental European locale, you need to either switch your locale at the Windows level, or to manually set every delimiter in the Excel text import wizard (requiring multiple mouse clicks for each column).
• jonnycomputer 5y ago
  CSV in general is terrible. CSV in particular is great. Most people only deal with CSVs in particular: this set of CSVs output from that process. Writing code that can accept and parse arbitrary CSV files is a whole different thing. If I had to do that, I'd be yelling too. Similar story can be said for JSON, markdown, etc, where standard is inadequate, non-existent, or in mutual competition.
• deleted 5y ago
  [deleted]
• pwinnski 5y ago
  This reads like a joke. If you think you can do better than CSV, let's see your proposal. Hint: you probably can't, and if you could, you probably couldn't get Excel to export it, so you still probably can't. "The status quo is bad, more recent popular formats aren't good enough either, but I don't actually have a specific proposal that's better than all of the above" is a lot faster to read than that article, and says the same thing.
  • hatmatrix 5y ago
    Just another confirmation for Betteridge's Law of Headlines
  • proverbialbunny 5y ago
    I wouldn't mind an updated standard to CSV that has the type information in the first line with the column labels. I feel like this is all that CSV is really missing. As a format where you can easily see and interact with the data in plain text if you need to nothing really beats CSV but I believe it could be made better, including strengthening standardization. Eg, a CSV standards version header could be put at the top of the file to minimize import export difficulty.
    • pwinnski 5y ago
      Amusingly, your sibling comment had an alternate proposal that included two features, but did not in any way address the one thing you suggest is missing. Replacing a flexible standard with a more-rigid standard is really, really hard.
      • proverbialbunny 5y ago
        The current standard already accepts a comment line at the top, so in this case technologically it is easy, far easier than most problems. However, getting a consensus from a committee with bike shedding and all that fun (horrible) stuff is very hard.
  • AussieWog93 5y ago
• ho_schi 5y ago
  XML? Which is often not human readable, overly complex and therefore requires enormous amounts of processing power to be parsed. JSON - and also CSV - are ubiquitous because they are actually human readable and usually quick to process. JSON is strictly defined it just doesn't over comments or structures, like XML. CSV is loosely defined but better than another competing standard ;) A good data interchange format must be human readable, JSON and CSV are. Proprietary stuff not. And if you feel the need for speed? Seriously? Okay, then think about a binary format. I prefer also the .conf format (so called INI) over other complex stuff for application settings.
• rjh29 5y ago
  There isn't enough love for TSV imo. Unlike commas, tabs rarely appear in input data, so you don't need complicated quoting or escaping rules. You just have tab and newline as special characters. Processing this data is extremely fast. Grepping for particular values is also very fast, as you can use the tabs as anchors when searching. TSV allows you to do stuff on a single machine and GNU parallel that people would normally create a Hadoop cluster or 128GB database for.
  • shock-value 5y ago
    Strong agree. TSV is better than CSV always (unless you need to interface with an external system that doesn't accept it). For those not aware, TSV and CSV differ by more than just the delimiting character. TSV has a dead-simple specification: https://www.iana.org/assignments/media-types/text/tab-separated-values https://www.iana.org/assignments/media-types/text/tab-separa.... CSV does not have a standard spec and implementations differ quite a bit, but often in subtle ways.
    • segmondy 5y ago
      There's no difference between TSV and CSV but the separator, most good libraries allow you to use any character you wish as the separator. Here's the RFC for CSV - https://datatracker.ietf.org/doc/html/rfc4180 https://datatracker.ietf.org/doc/html/rfc4180
      • Hasnep 5y ago
        The other key difference is instead of having complex quoting rules, TSV just disallows the tab character in the data.
      • shock-value 5y ago
        > There's no difference between TSV and CSV but the separator This is not true, though almost everyone (understandably) assumes this based on the names.
• btilly 5y ago
  When I see someone complaining about the loss of metadata, I have to wonder if they've ever had to deal with a swamp of data from different organizations, all of which disagreed on when to use what piece of metadata in what way. Bad CSV is a PITA, but usually you can make reasonable sense of it. Merging bad/inconsistent/conflicting metadata tends to be an open-ended nightmare with no good resolution at the end.
• alexpw 5y ago
  1) CSV can often be human readable at a glance (more so than most other formats, depending on that data), and that makes it appear deceptively simple and compact. Possibly due to that, I'd bet most of us have been bit by a writer/reader that doesn't respect the RFC rules. 2) I ask for TSV, whenever convenient. It's been more reliable, and I don't have a comprehensive why, but I think it's slightly more resilient to writer/reader inconsistencies, for me. It may be that there's just less need for escaping and quoting, so you might dodge a smart quotes debacle when asking for a one-off from an Excel user, for example. 3) Despite the issues raised, the notion we'd retire it makes me hug it tight, because for the majority of my requirements, it hits a sweet spot. I still reserve the right to raise my fist in frustration when someone does: ",".join(mylist).
  • shock-value 5y ago
    TSV is a dead simple and fully-specified format. Here it is: https://www.iana.org/assignments/media-types/text/tab-separated-values https://www.iana.org/assignments/media-types/text/tab-separa... CSV is a mish-mash of different, complicated, and under-specified standards and/or implementations.
• jrm4 5y ago
  Ha. Not gonna happen; for the same reason Python took off over more "flexible" languages, for the same reason people move from JSON to YAML for configs, etc. Languages and formats are not fundamentally about computers or efficiency, they're about people. Carry on.
• laichzeit0 5y ago
  Lack of types is my biggest gripe with CSV. I think just being able to specify types in column headers would be a win. E.g. “column1:int,column2:string,column3:datetime”. Type inference has bit me too many times.
  • 7952 5y ago
    Maybe have a format based on postgres types? They are fairly comprehensive, well defined and already have a text representation.
• pmelendez 5y ago
  Several things in this article resonated with me negatively. But I think these two are the biggest ones: >values stored in the files are typed. >most importantly, these formats trade human readability and writability for precision Those two properties are advantageous only when CSV is used in cases meant to be just parsed and as a data transfer format. But in reality, CSV files are being used in many different contexts. For instance, data scientists love to leverage and chain Unix tools to create a subset of data to test models. Also, it is often used as an export format to validate outputs quickly. I think the problem is that CSV is often the subject of abuse. In the same way that a spreadsheet is a subpar database, I don't believe we are nearly close to the time when we will retire Excel.
• lwn 5y ago
  U+FEFF"aaa","b CRLF bb","cc"c" CRLF zzz,yyy,xxx Just an example of the wonderful world of CSV \(.)/ which I've used quite a lot, because it enables quick and easy (dirty) data dumps/ exchanges. But using it as a data exchange format between multiple parties often leads to problems, such as above.
• anshargal 5y ago
  CSVJ open standard addresses many common problems of CSV. It is basically a CSV where rows are encoded as a JSON. Unfortunately it is not widely adopted yet and the language support is yet to be improved.
• joelthelion 5y ago
  No love for parquet and/or feather? They have open-source implementations in many languages, are much faster to load than csv, are natively compressed, are strongly typed and don't require parsing... There are few reasons to continue using csv in this day and age.
• Traster 5y ago
  CSV is ubiqitous. That's a massive advantage. If you argue against using CSV you might make it less ubiquitous. You're never going to make anything as ubiquitous as CSV though and that's amply demonsrated by the fact that when you look at alternatives you immediately name 7 different things. And some of the advantages aren't even necessarily advantages - a file format storing typed values is a non-trivial consideration when you're thinking about cross-tool support. Pandas is cursed with this even in CSV - there wasn't an optional int so any column with missing values must be float, and floats don't necessarily handle large integers - have fun with your nanoseconds going funny in your nanosecond timestamps stored as optional ints. CSV is ubiqituous because it's not trying to solve difficult problems. The second you try to solve the difficult problems you necessarily fragment your audience.
• s_dev 5y ago
  CSVs will never go away. They're simply very versatile and very simple. That magic combination is extremely rare in any technology.
• shezi 5y ago
  This is a pet peeve of mine: csv was a bad format already when it was created, because it intermixes data with meta data, ie it uses characters that can appear as either data or control characters, leading to escaping issues. This is in addition to being under specified as to which characters are used as control characters. It was outdated from the start because ASCII already has specific characters for file, group, record, and item separation. Using this would give broad compatibility and a wider feature set (eg. more than one table in a file) while retaining all the benefits of csv.
• simonw 5y ago
  One of my goals with https://datasette.io https://datasette.io is to offer a better alternative for publishing data than sharing a link to a CSV file. The trick is that if you compile data into a SQLite file and then deploy the Datasette web application with a bundled copy of that database file, users who need CSV can still have it: every Datasette table and query offers a CSV export. But... you can also get the data out as JSON. Or you can reshape it (rename columns etc) with a SQL query and export the new shape. Or you can install plugins like https://datasette.io/plugins/datasette-yaml https://datasette.io/plugins/datasette-yaml or https://datasette.io/plugins/datasette-ics https://datasette.io/plugins/datasette-ics or https://datasette.io/plugins/datasette-atom https://datasette.io/plugins/datasette-atom to enable other formats.
  • kthejoker2 5y ago
    As an avid Datasette (and Simon Willison!) fan, > then deploy the Datasette web application Is a huge hurdle for non-technical folks holding on to their CSV workflows. I think there is no barrier low enough that CSV cannot limbo beneath it.
    • simonw 5y ago
      Yeah I've put a ton of effort into that, but it's still nowhere near as easy as I would like.
• kazinator 5y ago
  Let me define a sane CSV standard: 1. Encoding is UTF-8. 2. Header line with column names is mandatory. If there are no column names, the first line must be blank. 3. Each record is a line. A line is defined according to the operating platform's text file format. 4. In the light of (3) CSV does not dictate line endings and does not address the conversion issue of text files from one platform being transferred to another platform for processing. This consideration is a general text issue, off-topic to CSV. 5. CSV consists of items separated by quotes. An item may be: 5. a) a JSON number, surrounded by optional whitespace. Such an object may be specially recognized as a number by the CSV-processing implementation. 5. b) the symbol true, false or nil, optionally surrounded by whitespace. These symbols may have a distinct meaning from "true", "false" or "nil" strings in the CSV-processing implementation. 5. c) a JSON string literal 5. d) any sequence of printable and whitespace characters, other than comma or quote, including empty sequence. 6. In the case of (5) (d), the sequence is interpreted as a character string, after the removal of leading and trailing whitespace. (To preserve leading and trailing whitespace in a datum, a JSON literal must be used.) 7. In (5), whitespace refers to the ASCII space (32) and TAB (9) character. If there are any other control characters, the processing behavior is implementation-defined. Arbitrary character codes may be encoded using JSON literals.
• dbreunig 5y ago
  They answer their own question: >Most of us don’t use punch-cards anymore, but that ease of authorship remains one of CSV’s most attractive qualities. CSVs can be read and written by just about anything, even if that thing doesn’t know about the CSV format itself. Yes, we keep CSVs. If you care about metadata and incredibly strict spec compliance, then yes: avro, parquet, json, whatever. But most CSV usage is small data, where the ease of usage, creation, and evaluation wins. One of the problems with CSVs he cites is a great reason why I like CSVs: >CSVs often begin life as exported spreadsheets or table dumps from legacy databases, and often end life as a pile of undifferentiated files in a data lake, awaiting the restoration of their precious metadata so they can be organized and mined for insights. A benefit of a CSV is a skilled or unskilled operator can evaluate these piles of aging data. Parquet? Even SQLite? Not so much. For small-to-medium sized datasets, CSV is great and accessible to a wider user base. If you're relying on CSV to preserve structure and meta, then meh.
• lmilcin 5y ago
  I call this "criticizing something without understanding why it succeeded in the first place". Also "writing blog posts while blind to other users' use cases". Do you really think there were no other "portable" formats to exchange data and SQLite is the first one? The reason CSV is so popular has nothing to do with technical superiority which it obviously lacks. The reason CSV is so popular is because it is dead simple and extremely easy to integrate within practically any conceivable workflow. And because it is text format which you can trust, even if you have no other tools, you can inspect and edit in a text editor which is exactly the reason why other formats like INI, XML, JSON or YAML are so popular. How would you: a) Run shell command on a locked (can't install anything) PROD server to find the files and write a report of the file location, date of change and file size so that it can be easily processed by something else? Return the data in stream on the same SSH connection? b) Quickly add a line to an existing startup script to get a report of when the script is starting and stopping so you can import data to excel. You just need it for couple of days, then you delete it. c) Produce a report you can send to your coworker knowing they don't know how to program? And you don't want spending time explaining to them how to make use of your superior file format? d) You talk to another team. You need to propose a format and specify a report with as little effort as possible. You know they are not advanced technically so you keep things simple? e) You are solving an emergency and have to cross-check some data with another team. You need to produce a file that they will be able to read and process. You are stressed for time so you don't want to start a committee right now to find out shared technology. What would be safe choice without asking the other team for options? f) A manager asked you for some data. You want to earn quick kudos. How will you send the data to get your kudos rather than irritating him? These are all real life situations where CSV is being used. As much as I understand technical arguments for SQLite, not every person is a developer. And all that technical superiority is worth nothing when they struggle to make use of the file you sent them. I would say, the news of CSV's death are greatly exaggerated.
• karmakaze 5y ago
  The premise is that CSVs are in widespread use and not suitable for their used purposes. The conclusion is that there are many binary formats which are more suitable, except that none are used widely enough, and the post even goes on to not make a recommendation. Finally it says that we have to accept this sad state: "Ultimately, even if it could muster the collective will, the industry doesn’t have to unify around a single successor format. We only need to move to some collection of formats that are built with machine-readability and clarity as first-order design principles. But how do we get there?"
• MrPowers 5y ago
  Here are the main advantages of Parquet over CSV (post author made these points more abstractly): * Parquet stores schema in the metadata, so schema inference isn't required (schema inference is expensive for big datasets) * Parquet files are columnar so individual columns can be grabbed for analyses (Spark does this automatically). This is a huge performance improvement. * Row groups contain min/max info for each column, which allows for predicate pushdown filtering data skipping. Highly recommend playing with PyArrow + Parquet to see the metadata that's available. * Columnar file formats are easier to compress. Binary files are way smaller than text files like CSV even without compression. I wrote a blog post that shows how a Parquet query that leverages column pruning and predicate pushdown filtering can be 85x faster than an equivalent CSV query: https://coiled.io/parquet-column-pruning-predicate-pushdown/ https://coiled.io/parquet-column-pruning-predicate-pushdown/ CSVs are great for small datasets when human readability is an important feature. They're also great when you need to mutate the file. Parquet files are immutable. It's easy to convert CSVs => Parquet/Delta/Avro with Pandas/Dask/Spark. The world is already shifting to different file formats. We just need to show folks how Parquet is easy to use and will greatly increase their analysis speeds & they'll be happy to start using it. Small nit: the article implies Apache Arrow is a file format. It's a memory format.
• SPBS 5y ago
  CSV is often used as a database-agnostic data dump file. Since the author suggested sqlite as a possible alternative to CSV, I think that using sqlite as a database-agnostic data dump file actually sounds pretty good. For starters, all data is kept in one file instead of being spread out over multiple CSV files. Also, the data schema is already well defined inside an sqlite file. All you need is a tool that can read the sqlite file and write the data out accordingly to other databases depending on dialect.
• jqcoffey 5y ago
  I’m going to cite “worse is better” in disagreeing completely with the author. CSV has won because it’s easy to generate, easy to read as a human, easy to parse with standard tools (when generated correctly), and really the last point is the salient one. The format isn’t inherently flawed, though much like other less than perfect standards (I’m looking at you SMTP), the implementations frequently are. And also, much like the author, I’ve been “professionally” dealing with data in all (most?) its forms for the length of my career (~25 years).
• SavantIdiot 5y ago
  Every year this question comes up, and every year the arguments are the same. It's an exercise in hubris because while CSV has one glaring fault, it is far fewer than other competing methods of the simplest possible exchange of textual data. No one can decide to retire CSV because it ubiquitous. Of the three issues OP compains about: line delimiter, field delimiter, and header, only one is really a problem for anyone that has used them for any period of time. 1. Every file format suffers from Windows/NonWindows CRLF issues. 2. Metadata has been an issue since forever, and there are plenty of painful formats that support it (looking at you XML), complete with ginormous parsers and even more issues. 3. Escaping, as OP points out, was pretty clearly defined in RFC 4180. So yes, it has a wart: escaping. Learn what your system expects, and modify accordingly. Because using a CSV will be much faster and simpler than any other format you can try, which is why it has been so pervasive for longer than most of HN has been alive.
• 41209 5y ago
  Why not use Yaml. Yaml is my go to format when I need something human readable and somewhat editable ( very easy to ruin Yaml spacing).. What's the real motivation behind an article like this. I don't imagine anyone has serious trouble with csvs, they tend to just work
• kofejnik 5y ago
  no
• temp8964 5y ago
  I use CSVY (CSV with YAML frontmatter) for my work. It is supported in R data.table fread()/fwrite(). It is just CSV with a little bit information on the top. Great for define column types, so that you reader does not need to guess the column types. See: https://csvy.org/ https://csvy.org/
• kcartlidge 5y ago
  CSV files can be made much nicer by the simple switch to tab-delimited. It doesn't solve all the issues by any means, but makes just enough of a difference to be 'okay'. Whatever is used, I like that tab-delimited (or even csv) is a human-readable and always-machine-readable long-term data format in the same way as ASCII (or Markdown etc) is for text. A hundred years from now, assuming the storage medium is still usable, the content should be easily recoverable. That may not be the case with spreadsheet files or other structured data formats.
• bhawks 5y ago
  CSV is the embodiment of worse is better axiom. Anyone that has dealt with more than 1 CSV is aware of many of the aspects of their horrible nature. The more interesting reason is why they're so damn successful. 1: network effect - not supporting CSV in a product is practically silly. everyone can do it why can't you? 2: ease of producing / consuming (not saying that you do it correctly in all cases :)) 3: data is transparent (or feels transparent) 4: accessible - if you can write language X you can parse or generate reasonable CSV in a mater of minutes. No knowledge of any libraries or tech required That said it's horrible - but it will always be with us.
• hermitcrab 5y ago
  I feel the author's pain. I have written a CSV parser from scratch and despaired at the lack of a decent standard for line endings, escaping, encoding etc (Excel and Numbers don't even agree on how to import the same CSV file). But CSVs aren't going away any time soon and I think the author is naive to suggest vendors should stop supporting CSV. For a start, think about it from a game theory perspective. If I keep CSV import/export in my Easy Data Transform product, while all my competitors with data transformation software (Alteryx, Knime etc) remove it from theirs, it gives me a big sales advantage. What advantage do I get for removing already working CSV import/export code? Nothing.
• jojo2000 5y ago
  It's time to reinvent the wheel, but more complex, more failure-prone. No thanks ! Joke aside good alternatives exist, but are far less ubiquitous. Binary format means problems to read, compatibility issues... Look at how hdf5 is a complex format with a lengthy spec. Checksum and compress your csv files, it will solve most issues.
• lamontcg 5y ago
  > CSVs are built for humans, not machines And that's why they're not going away. Trying to add typing to them is probably missing the point that >95% of humans that use them don't need or want to understand that.
  • MarkLowenstein 5y ago
    All I know is that when people give me a CSV I'm really happy and I get to work on their request right away. When they give me literally anything else I get pissed off and push their task lower in my queue.
• bernardv 5y ago
  If you want to design yet another ‘better’ file format, go to it. But CSV files are fine the way they are, thanks. If I need to preserve metadata, there are many tried and true options available to me.
• robochat 5y ago
  I know that there's an xkcd comic about this but I think that the best solution would be to create a new text based format with a new extension ie. "bsv" - better separated values. This new format would either be much stricter than csv or have a compulsory header that defines the file's format (like the text encoding, separator character etc). If it is stricter, it would have one type of field separator that is not commas since some locales use them as decimal places (I'm looking at you, France) but something like '|'. It would insist that dates were iso8601. It could define how fields can be escaped and quoted - although I would prefer if quoting was kept to a minimum. The format should also allow for comments i.e # so that people can comment their datasets inside the same file. Alternatively or in addition, it could have some header lines: 1) A header that defines the encoding, separator, decimal separator, quote character, escape character, line ending character, date format ... 2) A header that defines each column's name 3) A header that defines each column's data type and formatting 4) A header that defines each column's unit like m/s or kg - ok, this is a bit of a stretch but it would be great to have. or some variation of the above. Fundamentally, this bsv format would still be csv and most programs would still be able to read it with the parsers that already exist or be quickly adapted to read it. It could still be easily edited by hand but the metadata would be present. I suspect that this is just a pipe dream because people would find hundreds of ways to break it but toml took off and that didn't exist so long ago.
  • breck 5y ago
    I've played around with this (https://jtree.treenotation.org/designer/#standard%20iris https://jtree.treenotation.org/designer/#standard%20iris) I don't think you can make a breakthrough through syntax alone. I think you've got to integrate some type of live semantic schema, something like Schema.org. If I used "bsv" and didn't just get a slightly better parsing experience but also got data augmentation for free, or suggested data transformations/visualizations, et cetera, then I could see a community building. I think perhaps a GPT-N will be able to write it's own Schema.org thing, using all the world's content, and then a BSV format could come out of that.
• turtletontine 5y ago
  > Time to retire the CSV? no ♥
• m1234_ 5y ago
  As someone who works for a company in the finance sector that provides services that several large banks, funds and financial institutions use - I can say with almost certainty that CSVs won't go away anytime soon. Most of our data sharing from and to these companies is via CSV files.
• _trampeltier 5y ago
  CSV is perfectly fine for bring data from one App to another App. Is is easy. Everybody can fix things with any editor. You can paste files together, you can seperate files after x lines. Of course not everything is perfect .. and Microsoft made it even worser in the german office. The seperator is not even "," it is ";". The author writes about problems with dates .. i think we everybody saw excel translate dates to funny floats and so on. His problem with the date format is not a csv problem. The official standart is just not accepted in dayli life. Americans have anyway the most terrible ways to write dates. There is everything lost .. CSV is almost perfect for the usecase to bring data from one app to another without trouble.
• ozim 5y ago
  Problem is not with CSV, problem is with people. People want to read and interpret arbitrary CSV ... well you cannot read and interpret any format that is arbitrary. *As a consultant, I’ve written more than one internal system that attempts to reconstruct the metadata of a CSV of unknown provenance using a combination of heuristics and brute force. In short, CSV is a scourge that has followed me throughout my career.* Ideally this should not happen because you should talk with party that you agree on common format. Someone that would explain what each field means and what should it contain, or at least some documentation for the file, not that it just is a CSV. But of course it always is more complicated than that. Garbage in - Garbage out, even in other formats you still can have the same problem.
• rendall 5y ago
  The article's strongest criticism of CSV is that it's easy for someone to mangle it when manually editing. This is true. It's also true for every format. It was weakest when it implied there is no real standard. There is, and it's robust for representing data, even data that includes any combination of commas and double-quotes. The algorithm for creating well-formed CSV from data is straightforward and almost trivial: if the datum has no comma in it, leave it alone. It's good to go. If it has even one comma, wrap the datum in double quotes; and if it also contains double quotes, then double them. Not complicated and covers every edge case. CSV is going nowhere. 1000 years from now, computers will still be using CSV. The answer to his objections could be to extend the format to include metadata. Perhaps a second row that holds type data. fruit,price,expiration string,$0.00,MM/DD/YYYY apple,$0.45,01/24/2022 durian,$1.34,08/20/2021 etc
  • jedimastert 5y ago
    I wonder why escape dquotes with another dquote instead of a backslash or something?
    • contravariant 5y ago
      Because then you'd have to escape backslashes.
  • hnick 5y ago
    You didn't mention handling new lines as data. Excel for example will include these as-is and double quote the cell. It is very easy to overlook edge cases in CSV.
    • rendall 5y ago
      > It is very easy to overlook edge cases in CSV. That was not a criticism from the original article, and isn't even true. If you have newlines in your original data, and you "overlooked" this "edge case", then neither JSON nor YAML nor any other format will save you. The same fix applies to them all. This is really a very poor criticism.
• flowerlad 5y ago
  CSV isn't going away any time soon. It is the only format that is supported by all data-oriented tools. Getting all those tools to agree on an alternative isn't going to happen, especially given that that there are all these old tools that are no longer being updated, that produce and consume CSV. Instead of finding a replacement for CSV it might be easier to standardize it and enhance it. Excel's version of CSV is the de-facto standard. If you want a written-down spec that is available too [1]. To this we need to add enhancements such as a way to specify metadata (i.e., data type of each field). No need to find an alternative to CSV! [1] https://datatracker.ietf.org/doc/html/rfc4180 https://datatracker.ietf.org/doc/html/rfc4180
  • einpoklum 5y ago
    > Excel's version of CSV is the de-facto standard. If you're working someplace that uses Excel, then maybe. Otherwise, it isn't. Anyway, if more tools, including Excel, properly implemented RFC4180, we'd at least have something. Another option is some sort of meta-data file alongside a large CSV describing its format somehow - which, if missing, you have to fall back on RFC4180 or your parser's "try to figure out the quirks" mode.
• tbenst 5y ago
  Another beauty of CSV is its compatability with git / versioning. SQLite databases are in contrast a nightmare, and git merges often corrupt files.
• mybrid 5y ago
  It will be easier to get all the OS creators to normalize new line to one thing. Then pick a text delimiter that is reserved solely for delimiting, like say '|'.
• zz865 5y ago
  As an aside, XML really was great though, its a pity it fell out of fashion. Schemas, XPath, so many great solutions that we're still messing with in JSON, YAML etc etc.
  • anigbrowl 5y ago
    I really wanted to like XML but every book I picked up about it was so awful, and every XML browser program I tried so unbelievably slow, that I just gave up trying to figure it out when it was still current. I didn't need it for work; I just thought the concept was cool and it'd be a fun thing to know. Perhaps people were so eager to prove it was powerful that they ended up making it seem like something for masochists.
• Evidlo 5y ago
  Many of the author's complaints are addressed by CSVY: https://csvy.org/ https://csvy.org/
• EthicalHacker03 5y ago
  Use fixed width files instead... along with a good old COBOL copybook. Nothing beats parsing fixed width records performance-wise. You can auto-generate staging and persistence tables, ETL jobs and quality control with just the copybook as input.
• robbmorganf 5y ago
  I love CSV. Only change I want is some way to indicate a comment. Then I can write a human readable description of what the data means (more than just column names, but how it is calculated). It would be cool if the comment mechanism also could have a machine readable metadata system.
• jiggawatts 5y ago
  My problem with CSV is that it has a logical, obvious structure that is practically self-evident. The very act of having to write an encoder and then a parser makes the various corner-cases inescapable, which means that it is almost impossible to imagine getting it wrong. Yet, everbody gets it wrong. Everybody. Thoroughly, fantastically, almost unimaginably wrong. For example, in the Microsoft world: I come across CSVs in about 5 scenarios, all of them very common, most of them designed to interact: Excel, PowerShell's Export-CSV, SQL Server, Power BI, and the Azure Portal. None of these are obscure. None of these use CSV infrequently. Yet, they're all mutually incompatible!!! That just blows my mind. For example, SQL Server will output string ",NULL," to represent a null field instead of just a pair of commas (",,"), so every other tool will convert this to the string "NULL", which is not a null. PowerShell helpfully outputs the "type" of the value it is outputting, like so: PS C:\> dir | ConvertTo-Csv #TYPE System.IO.DirectoryInfo "PSPath","PSParentPath","PSChildName","PSDrive","PSProvider",... Excel can't open such files! It is entirely unfathomable that the PowerShell team wrote this code and never once double-clicked the resulting CSV to see if it opens successfully in Excel or not. Absolutely mindblowing! It just goes on and on. Different quoting rules. Random ability/inability to handle new lines. Different ways of handling quoted versus unquoted string values. Encoding is UTF-8 by default or not. Handling of quote characters within strings. Etc, etc... Basically, within one vendor's ecosystem, flagship applications have at best a 50:50 chance of opening arbitrary CSV files. Don't even get me started on the inherent ambiguities of the format, like interpreting dates, times, or high precision decimals, etc... Oh, and before I forget: the SQL Server Integration Services team wrote lengthy articles on how their engine can process CSV files faster than the competition. Why is this a feature? Because CSV is a woefully inefficient format and processing it fast is an achievement. Lastly: By default, SQL Server cannot export table data to a flat data file and round-trip it with full fidelity, in any format. Exchanging just a couple of tables between servers is... not fun. So yes, a replacement format with an efficient, high-fidelity binary format is long overdue.
• zmmmmm 5y ago
  I guess it's nice we've reached the generation who can rant about this and not even mention that XML solved nearly all the problems mentioned and everybody hated it. It turns out people hate bloat and complexity more than they hate all the ills of CSV put together!
  • Thiez 5y ago
    I'll happily take xml over csv any day, but my coworkers and our customers won't!
• tester756 5y ago
  What if CSV had optional type name hint next to column name, with default being text? Like Id(int), Name, LastName, BornAt(date) 1, asd, dsa, 1992-07-12
• ubermonkey 5y ago
  Yeah, that's a dumb idea. Sometimes -- frequently, even -- you have to go to some least-common-denominator format to move data around. CSV is often a candidate. When I was younger I thought we'd eventually abandon "primitive" formats like this, but in my middle years I realize their extreme utility. CSV absolutely has a place, and absolutely does a job that no other format does as quickly or as easily.
• jti107 5y ago
  never gonna happen. company i work for has csv files from tests on products they conducted almost two decades ago. they went to a proprietary solution for a few years and that company went bankrupt and we can no longer open certain files because the license was restricted to Sun servers. we've learned the hard way that open systems even if sub optimal are usually the right choice especially when you're trying to store/archive data. we've resorted to csv files + readme + json files in git for version control.
• soheil 5y ago
  Literally there are two characters required to create a csv. Anything that replaces it will necessarily be more complex. So let’s keep csv and create something better at the same time.
• ittan 5y ago
  I might have a different perspective than most, but I think csv as a raw access method is far better than any structured format. The contract on what is transfered or how it's mapped in your csv is developer to developer and not in code. This is a problem and also a benefit. As long as both systems are designed to read and write the csv correctly(there in also the problem of verification), csv works flawlessly and retains its simplicity.
• bsg75 5y ago
  https://en.wikipedia.org/wiki/Delimiter-separated_values https://en.wikipedia.org/wiki/Delimiter-separated_values
• politician 5y ago
  The only reason CSV is hard to parse is that text editors made it hard to type ANSI 29, 30, and 31: the group, record, and field separators. So folks had to resort to comma or pipe and newline. If you use the right characters built specifically for this purpose, then the problems go away.
• anodyne33 5y ago
  In e-Discovery our metadata is exchanged in csv format almost exclusively with one caveat, the defacto delimiters are Pilcrow and Thorn. We've solved the quote/comma in data problem, but as soon as we start mixing character sets, someone writes a load file in ANSI all hell breaks loose.
• spiritplumber 5y ago
  No, not time to retire the CSV. I can open a CSV with an Arduino with a SD card socket soldered onto the pins.
• moderncsv-dev 5y ago
  I'd rather not.
• ArsenArsen 5y ago
  AFAICT, XML fulfils all of the requirements of this. It is self describing, rigorously defined for the machine, highly flexible, highly extensible, human readable, text-based and an open format, and it is only mentioned as a storage format in the post, not even nearly doing it justice! XML is complex, but there's already very established libraries for handling it, so that shouldn't be an issue, and that's the only drawback XML has. Hell, even alternatives like JSON/YAML + JSONSchema exist, still providing incredibly rigorous validation, but staying human readable and ubiquitous. P.S. I think a bigger failure here is trying to generalize the concept of spreadsheets, rather than using a common encoding (XML) for domain-specific data formatting.
  • selfhoster11 5y ago
    Most XML is not human-readable. There's just too much line noise, and most of what's emitted has a weird schema that's difficult to parse using Human Brain 1.0.
    • ArsenArsen 5y ago
      I don't think it's much worse than CSV, with it's inflexible structure, not allowing for any formatting to be inserted. As for weird schemas, that really is an implementation specific issue. This example was sketched up in minutes and already reads better than most spreadsheets I've seen over the years! <bankstatement> <transaction date="2021-08-19" description="Hello, world!"> <debit commodity="USD">100</debit> <balance commodity="USD">237.87</debit> </transaction> </bankstatement>
      • selfhoster11 5y ago
        While weird schemas are an implementation-specific issue, the fact remains that most XML that I've seen in commercial settings is exactly as unreadable as I described. It's harder to go against the grain of the status quo when very few people accompany you on the journey.
  • mr_toad 5y ago
• worik 5y ago
  Whinging a bit There are plenty of formats available. CSV is useful. Not perfect.
• anothernewdude 5y ago
  If CSV is giving you such problems over the years, then I have no confidence in your opinion.
• worik 5y ago
  What a crock. The author completely misses the point of what CSV files are useful for. They are useful when both ends of the communication understand the context. They know what the data types are, they know what the character encoding is etcetera. This is a very common situation. CSV files are easy to process, easy to generate, and can be read by a human without too much bother (they are not "for people" as the author so irritatingly asserts). I have written so many CSV (and other delimiters, not just ',') generators and processors I lost count decades ago. It is so easy. And what does "retire CSV" really mean? Just stop generating them then! The consumers of your data will probably insist you go back to them as writing all that code just to satisfy your fetish with metadata is not useful Now I am whinging.....
  • Antrikshy 5y ago
    I stopped reading, so I may have missed it, but do they even address situations where humans interact with systems by supplying them with tabular data?
  • einpoklum 5y ago
    I don't agree with the author's thesis, but you're wrong. 1. It is a very common use-case to load CSV data generated by an opaque system which did not specify the format exactly to you. 2. CSV files are _difficult_ to process I: Fields are of variable width, which is not specified elsewhere. So you can't start processing different records in parallel, since you have to process previous lines to figure out where they end. You can't even go by some clear line termination pattern, since the quoting and escaping rules, and the inconsistencies the author mentions, make it so that you may not be able to determine with certainty that what you're reading is an actual end-of-record or end-of-field. Maybe it's all just a dream^H^H^H^H^H quoted string? 3. CSV files are _difficult_ to process II: For the same reasons as the above, it is non-trivial and time consuming to recover from CSV generation mistakes, corruptions during transmission, or missing parts of the data. However - if you get some guarantees and abot field width and about the non-use of field and record separators in quoted strings, then your life becomes much easier.
• six0h 5y ago
  I'll never get the time back I wasted hoping that this article had something useful to say
• ineedasername 5y ago
  Under many scenarios it's not currently possible. As one example, I use SQL Developer extensively, and neither its XML or JSON exports include metadata, So that's no more useful than CSV. Excel is a no-go for anything with > 1M rows. Exporting large datasets leaves me with CSV since it's easier for recipients to parse than XML or JSON without additional work. And querying Oracle from within Python or R suffers a big performance hit over doing it directly from SQL developer, so for one-off projects I don't bother building a pipeline. (Repeat projects I'll build and schedule for downtime, or on a different PC.)
• six0h 5y ago
  I've never seen an article say so little in so many words.
• mastrsushi 5y ago
  Are there really new projects stretching the limits of CSV out in production? Everyone knows it’s not capable of storing anything more than table data. It was never meant to be much more. It doesn’t really store objects well. To write the values of a composite object would require following some format order. It would go past human readability and just be a bloated way of writing bytes with readable characters.
• hobs 5y ago
  Gotta post https://github.com/secretGeek/csvz https://github.com/secretGeek/csvz whenever csvs come up, csvs but with metadata!
• slaymaker1907 5y ago
  I think a key element for any potential replacement of CSV is parser simplicity. Writing a basic CSV parser by hand is not very difficult (i.e. ignoring stuff like escaped characters and quotes) compared to writing a SQLite parser. Regardless if it is binary or not, it should be possible to print out a usable grammar on a single sheet of grammar and said grammar should be LL(1) so at most you would need to write a recursive descent parser. Suppose someone wants to do some data processing client side in the browser with an input file. How would you do this for SQLite, even a SQLite file with a known schema such as would be the case most of the time for CSV file uploads? There is sql.js which compiles SQLite to JS or WASM, but even the WASM version weighs in around 400kb.
• j45 5y ago
  Csv isn’t going anywhere for better or worse.
• danellis 5y ago
  The worst I've seen is CSV in which some of the fields contain CSV.
• gigatexal 5y ago
  Let’s replace them with sqlite dbs.
• pbreit 5y ago
  I could not disagree more. Perhaps the author is writing about a small niche area where the data is more complicated and/or typing is useful. I'd instead rather see much, much more CSV as well as flattening data models to make data more suitable for tabular representation. Handling newlines and quotes is really not that difficult.
• pbreit 5y ago
  Zipping two files is an obvious nonstarter. CSV won because it is simple.