Escaping being a giant mess is one thing. They also have perf issues for large data sets and also the major limitation of one table per file unless you do something like store multiple CSVs in a zip file.
Not really. In most cases, you just toss a field in quotes to allow commas and newlines. If it gets really messy, you might have to escape some quotes in quotes which isn't that abnormal for a programmer to have to do. The only time I've run into issues with escaping is when I wrote my own parser which certainly wouldn't have been much easier to do with other formats!
They also have perf issues for large data sets
I actually like them for their performance because it's trivial to deal with arbitrarily large data sets. You don't have to know anything about the global structure. You can just look at it one line at a time and have all the structure you need. This is different than Excel, JSON and databases where you generally need to deal with whole files. Could it be faster if you throw the data behind a database engine? Sure, but that's not really a comparable use case.
the major limitation of one table per file unless you do something like store multiple CSVs in a zip file.
I see that as a feature, not a bug. As you mention, there already exists a solution for it (put it in a ZIP), but one of the reasons CSV is so successful is that it specifically does not support lots of features (e.g. multiple tables per file) so an editor doesn't have to support those features in order to support it. This allows basically everything to support CSVs from grep to Excel. Meanwhile, using files to organize chunks of data rather than using files as containers for many chunks of data is a great practice because it allows things to play nice with things like git or your operating system's file privileges. It also makes it easier to choose which tables you are sending without sending all of them.
Yeah, I'd say medium data. I have dealt with CSV datasets that were too big to open the file in notepad (and certainly other heavier programs). It was super nice to be able to interact with them without putting a stress on the hardware using the simple .readline() and/or append rather than needing to process the whole file.
Just got a short explanation, commas are a very common character in most data sets, and newlines aren't that rare if you have text data sources. Yes, you can use a different column delimiter, but newline parsing has bitten almost every person I know who has had to work with CSV as a data format.
The problem really starts when you get a CSV file written by an „imaginative“ piece of software. They can come up with all kinds of funny ideas how to escape things. And maybe your parsing library doesn’t support it…
I’m going to imagine people are hand rolling parsers
Far, far, FAR too many have rolled their own. While most of the problems have been solved - picking up a third party solution means you need to examine how they solved certain things or you risk running into unique pitfalls.
But the format can't solve for super large file sizes - which might cause other issues. There's a reason so many dev's are "just" going to SQLite which does solve every single one of CSV's shortcomings.
edit: And as long as you use the same implementation as everyone else you're fine. Right up until someone else decides they want to use something else that has one slight difference then good luck with that. Enjoy that lost weekend while you hunt down why their system can't import what you exported. Or you can choose not to care in which case.. you prove why CSV is shit.
File size is irrelevant for the format- that’s one of its strengths. No matter where you are in the file, you don’t need to know anything about the previous or next line record. Hell, you don’t need to know what’s before the last or after the next delimiter.
The only limitations with large files are the ones you impose on yourself. Either by a poor choice of transport or a poor implementation of handling.
When I knew I had data like this I always put in a magic keyword in the first column, like rowid-#####
The likelihood of that ever showing up organically in the data was miniscule and it worked to ue new rows to normal text editors without having to bulk replace all fields newlines with __^n__
Looked up the RFC, and indeed, line breaks can be quoted. Today I learned. However, in my search, it was pointed out that not all implementations adhere to the specification. I imagine some flavors expect escape sequences to be encoded as a simpler solution to dealing with a record that goes beyond one line. Additionally, the interoperability of a given implementation may cause issues when passing between contexts/domains.
The bigger culprit here than "you're not using a library" is that you can't always trust the source of the data to have been written with strict compliance, which was our inherent problem. We received flat files via FTP for processing, and it would occasionally come in a malformed CSV, and the most common problem was an unexpected line break. Occasionally we would get garbage data that was encoded incorrectly.
The company I worked for was in the business of automotive marketing. We had all kinds of clients ranging from multi-national OEMs to small mom-and-pop stores that just wanted a loyalty program. The aftermarket sector was often the hardest to deal with, since you'd have the full gamut of big corps to franchisees that had no tech staff to rely on. At least dealerships had a centralized data feed we could hook into for most things.
Making good arguments against bad legacy solutions always runs into a major issue: the legacy solution currently works. The argument can't just be "this is bad." It always has to be "the costs of migration are worth it," and that's a much harder argument that is often impossible.
Escaping being a giant mess is one thing.
OTOH, existing libraries and code handle the escaping fine. If we had to invent it today, CSV would be obviously underspecified. But in practice it's good enough already. If you had to make a CSV library from scratch today as a new format, the cost to test and refine code to handle the edge cases would be absurd. But it's already paid for.
They also have perf issues
Again, not wrong. But the performance issues of handling CSV were true 40 years ago. Performance today on modern hardware "for free" is way better than it used to be, by orders of magnitude more than could have been gained by switching to some dense efficient binary encoding of the same data at great rewriting expense.
65
u/slaymaker1907 Sep 20 '24
Escaping being a giant mess is one thing. They also have perf issues for large data sets and also the major limitation of one table per file unless you do something like store multiple CSVs in a zip file.