It brings the ability to parse in parallel - that's a big deal. And while number vs text might not be a huge difference in theory, in practice it eliminates what, 95% of real-world parse problems?
I think you could fix most of the pain of CSV simply by adding a second header row which defines the type of the column, using a common vocabulary of types. TEXT, NUMBER, ISO8861-DATE, etc.
The trouble is that a lot of existing software (not just Excel) won't properly roundtrip text that looks like numbers and will e.g. strip the leading zero from phone numbers, or worse, change the last digit to make a number that exists in floating point.
> The pain points are usually dates or currencies - the same problem I usually have with JSON, because there's no standard format.
Hmm, I've never had a problem with ISO8861 dates - a string is either a valid date or not, it's very rare for someone to "accidentally" put data in ISO8861 format when it's not actually a date. Dates without timezones can cause problems, but that's more of a semantic issue than a serialization issue. What are the problems that you get?
I can see how currencies could be an issue with the lack of a standardised fixed-precision type. But in my experience they're an order of magnitude less common than issues with phone numbers, postal codes, and the like.
> I think you could fix most of the pain of CSV simply by adding a second header row which defines the type of the column, using a common vocabulary of types. TEXT, NUMBER, ISO8861-DATE, etc.
I'm sure you could. But at that point you're defining a new and incompatible format - you have to make it incompatible, or otherwise people will open these files with a tool that doesn't understand the header format and you're back to square 1 - so you'll pay all the same adoption costs as a completely new format. So it make sense to fix all the issues we can - and a format which can be split and parsed is definitely a major improvement for many use cases.
On text vs numbers, at least some widely-used software (e.g. R, Excel) will try to guess for you. It should be obvious how this might cause problems. Maybe one should turn auto-conversion off (or not use things that don't let you turn it off) and specify which columns are numbers. Some datasets have a lot of columns, so this can be a PITA, even if you do know which ones should be numbers. But the bigger problem is if you have to deal with other people, or the files that they've touched. There are always going to be people that edit data in excel, don't use the right options when they import, etc.
I definitely had my fair share of trouble with locale-defined number format. Importing a column where a thousand is spelled "1.000", any integer between 1000 and 999999 would be wrongly parsed as a float between 1 and 999, while for any other number (like "0,1" for one tenth, "1.000.000" for a million, "1.000,56" for a thousand euros and change) the parser would give up and keep the string.
I usually have had more luck importing as text, then doing some string replacement of separators before finally converting to number.