Time to retire the CSV?

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.

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.

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.

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

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.

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.

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.

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.

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.

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.

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.

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

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.

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.

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

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.

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.

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.

  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.

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.

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.

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

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.

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

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.

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.

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.

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!

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.

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

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.

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.

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.

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

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/

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.

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.

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.

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.

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.

https://en.wikipedia.org/wiki/Delimiter-separated_values

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

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

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.

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.

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.

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

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?

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.

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?

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

https://csvy.org/

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

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

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

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

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.

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

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.

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

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.

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

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.

Whinging a bit

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

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

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

no

CSV is the A-10 warthog of data formats

https://xkcd.com/927/

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

No

No.

LOL, no

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

Csv isn’t going anywhere for better or worse.

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.

> Time to retire the CSV?

no ♥

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

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.

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

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.

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

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.

I'd rather not.

I've never seen an article say so little in so many words.

I'll never get the time back I wasted hoping that this article had something useful to say

EMBRACE PARQUET.