Time to retire the CSV?

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.

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

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

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.

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

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.

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.

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.

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.

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

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.

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

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.

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

More correctly titled, "I Don't Like CSV".

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.

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.

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

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.

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

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.

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

It seems like it would've saved us all several decades of headache.

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.

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.

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

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.

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'!

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/

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.

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.

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!

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.

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.

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.

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.

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.

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

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.

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

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.

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.

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.

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?

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.

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.

One of my goals with 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 or https://datasette.io/plugins/datasette-ics or https://datasette.io/plugins/datasette-atom to enable other formats.

Why not simply use the ASCII characters dedicated for this? STX,ETX, RS, GS, etc

  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.

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.

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.

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.

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.

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.

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.

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

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.

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.

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

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.

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

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.

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!

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

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.

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

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?

As long as it's replacement opens in Numbers and Excel.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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/

LOL, no

The only appropriate replacement for CSV would be a better defined CSV that gave hard requirements for things like field quoting

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.

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.

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.

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 :)

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.

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.

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.

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.

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.

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!

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.

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

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.

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.

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

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?

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.

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.

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.

Gotta post https://github.com/secretGeek/csvz whenever csvs come up, csvs but with metadata!

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.

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.

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.

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.

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

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.

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.

Many of the author's complaints are addressed by CSVY:

https://csvy.org/

CSVs will never go away. They're simply very versatile and very simple. That magic combination is extremely rare in any technology.

Another beauty of CSV is its compatability with git / versioning.

SQLite databases are in contrast a nightmare, and git merges often corrupt files.

I agree with many of the problems - but luckily many providers (of data) use the first lines to provide a header of metadata.

No, not time to retire the CSV. I can open a CSV with an Arduino with a SD card socket soldered onto the pins.

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

CSVs would be fine if Excel didn't import them in the most brain-dead way imaginable by default.

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 :)

If CSV is giving you such problems over the years, then I have no confidence in your opinion.

“ 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/

I've been moving my organization to Sqlite and Parquet depending on the use case.

Zipping two files is an obvious nonstarter. CSV won because it is simple.

The worst I've seen is CSV in which some of the fields contain CSV.

Whinging a bit

There are plenty of formats available. CSV is useful. Not perfect.

no

CSV is the A-10 warthog of data formats

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.

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

Csv isn’t going anywhere for better or worse.

No

No.

> Time to retire the CSV?

no ♥

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.

Let’s replace them with sqlite dbs.

no

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.

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.

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.

EMBRACE PARQUET.