4 ms·

CSV is an awful non-standard standard that nobody should use. To understand how awful it is, you just have to ask two questions "How do I represent empty value

CSV is an awful non-standard standard that nobody should use. 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.

taeric 3y ago
JSON is not without its own problems. Not the least for getting very close to what folks want, but just falling short when things get complicated. https://en.wikipedia.org/wiki/CDATA 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.
nradov 3y ago
The CSV standard is RFC 4180, although it's merely informational. https://www.ietf.org/rfc/rfc4180.txt https://www.ietf.org/rfc/rfc4180.txt
maxcoder4 3y ago
I don't understand the question about empty values. A row with there empty values is just: ,, 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).
- lelandbatey 3y ago
  I would guess that their point is that CSV does have any official null-vs-empty-string differentiation mechanism.
- cogman10 3y ago
  Is it ''? 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.
  - jorams 3y ago
    CSV is extremely underspecified, but that doesn't mean it deserves the blame for software that fails to implement even the one thing it inherently specifies. A sequence of tabs is one value in a CSV. A sequence of semicolons is one value in a CSV. Any software that thinks otherwise is buggy, and unfortunately that includes some extremely popular software that is supposed to be good at tables (Excel).
magicalhippo 3y ago
> To understand how awful it is, you just have to ask two questions 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?
- kevindamm 3y ago
  Still basic questions. 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.
user_of_the_wek 3y ago
It always pains me to say, but when processing tabular data from sources I don't personally control, I prefer xlsx format. It's usually well supported by most export processes (in contrast to e.g. the LibreOffice equivalent) and I never had problems finding a library that can parse it. And you never have to solve the "anything goes" CSV problems you're talking about.
RockRobotRock 3y ago
json lines/ndjson is great.
paulddraper 3y ago
> How do I represent empty values Empty string. For example, here are three in a row: ,, > What do I do when my separator is contained in my values? Use quotes. For example, here is a single comma: ","

taeric 3y ago

JSON is not without its own problems. Not the least for getting very close to what folks want, but just falling short when things get complicated. https://en.wikipedia.org/wiki/CDATA 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.

nradov 3y ago

The CSV standard is RFC 4180, although it's merely informational. https://www.ietf.org/rfc/rfc4180.txt https://www.ietf.org/rfc/rfc4180.txt

maxcoder4 3y ago

I don't understand the question about empty values. A row with there empty values is just: ,, 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).

lelandbatey 3y ago
I would guess that their point is that CSV does have any official null-vs-empty-string differentiation mechanism.
cogman10 3y ago
Is it ''? 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.
- jorams 3y ago
  CSV is extremely underspecified, but that doesn't mean it deserves the blame for software that fails to implement even the one thing it inherently specifies. A sequence of tabs is one value in a CSV. A sequence of semicolons is one value in a CSV. Any software that thinks otherwise is buggy, and unfortunately that includes some extremely popular software that is supposed to be good at tables (Excel).

magicalhippo 3y ago

> To understand how awful it is, you just have to ask two questions 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?

kevindamm 3y ago
Still basic questions. 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.

user_of_the_wek 3y ago

It always pains me to say, but when processing tabular data from sources I don't personally control, I prefer xlsx format. It's usually well supported by most export processes (in contrast to e.g. the LibreOffice equivalent) and I never had problems finding a library that can parse it. And you never have to solve the "anything goes" CSV problems you're talking about.

RockRobotRock 3y ago

json lines/ndjson is great.

paulddraper 3y ago

> How do I represent empty values Empty string. For example, here are three in a row: ,, > What do I do when my separator is contained in my values? Use quotes. For example, here is a single comma: ","