To understand how awful it is, you just have to ask two questions "How do I represent empty values?" "What do I do when my separator is contained in the data?"
There are answers to these questions, but there's not a standard backing up those answers. And that's what makes CSV is PITA to deal with. It's such a loose "standard" that anything goes.
xml and json are FAR better options even when you just want a table of data.
Those are rookie questions. What about when the data contains newline/crlf? What if the data contains the quote character and newline?
And why is the file mostly Windows-1252 encoded except some fields that are sometimes, at random, UTF-8 encoded?
The real kicker is when your fellow users are opening the CSV in a spreadsheet with a locale that prefers commas for fractional currency amount and then saving the file back.
https://en.wikipedia.org/wiki/CDATA is something far too few people pay attention to when considering the complications of object notation and why markup languages have some escape hatches.
Empty string. For example, here are three in a row:
,,
Use quotes. For example, here is a single comma:
",",,
And yeah, dealing with separators is annoying, but pretty much every reader supports quoted values and escapes (not every writer cares up add them, though).
In practice I use csv to process large amount of tabular data (like logs, events, etc) where I care about greppability and performance more than about potential for 0.001% of corrupted data. YMMV of course, if getting it 100% right is important use something else (JSON is not without sins too, consider that JSON numbers are often parsed as floats).
Or is it ,,,,,,,
Or is it tabtabtabtab
Or perhaps it's "NULL,NULL,NULL"
My part of my work is data ingestion and I've seen all these (and more) as answers to the "empty values" question.
I'm not saying that other formats aren't without their problems, they certainly are. However, CSV doesn't just have those problems, it has multiple other problems on top of them.
It's a basic idea with really obvious edge cases addressed in multiple ways depending on who is producing these documents.