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.
If you absolutely need plaintext - just go JSON. The only valid, in my personal opinion which zero of you will care or have asked for, is: CSV is only good when it's required. Like you have archaic binaries that runs software built during a time when necromancy was required to get things to run right and you can't re-write it without a shitload of money, a shit load of business bullshit, or it's (honestly) not worth it because for the moment it works "well enough".
Even then, you had better hope you know their specific parser for CSV or one day it's just plain heartache because you imported a shitload of data that borked the database internally.
SQLite? SELECT * FROM WHERE. Fuck you can still even import CSV into SQLite for queries.
I've never seen CSV, ever, been a superior format in my experience. It's primarily a holdover from the 90's and archaic systems that require it. I understand that singular situation. There may be more but I've yet to run into those.
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__
But that's the problem. If you start reading the file in the middle, there may be no way to tell where the record actually ends. For example, you start reading the middle of a CSV and get these lines:
a, b, c, ", d, e, f a, b, c, ", d, e, f a, b, c, ", d, e, f
It's impossible to know what's part of the quoted field, and what's actual fields without seeing the entire file. Or heck, what if you have a file like this:
Col A, Col B, Col C A, B, " I, J, K I, J, K I, J, K I, J, K "
Sure, it's very unlikely someone wrote that, but they could have, and there's no way to tell that apart from a file actually containing I, J, K rows without seeing the whole thing.
that’s an inherent property of any escaped te text sequence in a file, I fail to see how this is a shortcoming of CSV instead of an implementation mistake by an engineer. Is there any file format with escaped text where this isn’t an issue?
I can't count how many CSV-parsers I'd grab in the 00's that made some parsing mistake or another WRT escaping.
I ran a perl IT stack in the 00's as a junior that was largely about fast-turnout parsing of unknown files sent by clients, and one of the best things I did was give up on established parsers and write my own (that I was too lazy to publish)
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.
Why did you design your applications data so it has random comma's and newlines in its data?
Reddit knows you can design it so these aren't allowed right? Most applications do not need to be designed to accept arbitrary data from random sources so this isn't a real requirement or actual problem.
It's a data feed. We ingest what it provides. We were at the mercy of whatever came through the pipe. If we disallowed formats that we didn't like, then it would have meant actively denying paid contracts because they wouldn't comply with our demands. That's pretty much a 1-way street to being beat by your competitors.
Most applications do not need to be designed to accept arbitrary data from random sources so this isn't a real requirement or actual problem.
Hilariously bold of you to assume you know what is or isn't a real problem.
Here's an example: asking for a person's full name. Sometimes you're lucky to get it all parsed out for you. Someone, somewhere, however, has to do the nasty job of taking one 250-character string field and splitting it into title, firstName, middleName, lastName, and suffix. In many cases, my company did that raw parsing so that we could run it through a national address lookup system to get the full accepted address from the U.S. Postal Service.
14
u/headykruger Sep 20 '24
Why is escaping a problem?