To point 1, I'd argue that a SQLite database is a great next step beyond CSV despite being a binary format.
The advantage of CSV is that it's as accurate as your plain text representation of your data can be. Since binary data can be represented by character data, that's 100% accurate. As soon as you introduce a storage format that has made assumptions about the type of data being stored, you've lost flexibility.
SQLite is not intended for data serialization. It's intended for data storage to be read back by essentially the same application in the same environment.
SQLite's "dynamic data types" coerce data on input, not output. Once the data is in sqlite the way you wanted it, excel has no interpretation to perform, except insofar as really really wanting dates.
> The advantage of CSV is that it's as accurate as your plain text representation of your data can be.
Yeah nah.
> SQLite is not intended for data serialization. It's intended for data storage to be read back by essentially the same application in the same environment.
That's completely, absolutely, utterly, objectively, nonsensical.
Data analysis, exchange, container, and archiving, are literally all use cases listed as "appropriate uses for sqlite" in "Situations Where SQLite Works Well" on the official sqlite website: https://www.sqlite.org/whentouse.html
Cockiness tells me that you’re insecure about your knowledge, not that you know more than GP.
The idea that a CSV would be more likely to be correctly encoded than a DB is hilarious, thanks for the laugh. But that you were confident enough to seriously put it in writing shows how little experience you have with CSV.
A CSV file represents the exact digits that are to be stored. You have unlimited precision. You could even store irrational numbers, equations, or mix data types in a column. OTOH, you have to make sure the delimiting character is not present in the data - that can be pretty easy, if you use the field-separator character in ASCII, or even just a \t. I've even seen people terminate fields with ^8675309| because they felt confident no data would contain Jenny's number.
A database, like Excel, likes to conform data. This is usually awesome! But sometimes, it's not.
"A bird, like homo sapiens, has two feet" doesn't mean that birds are humans or humans are birds either. It means that in this respect, birds and humans are alike. Which is what GP meant: With respect to conforming data, databases behave just like Excel.
Language note: I think it's the fact that there are two commas around the inserted-clause [terminology?] ", like Excel," that does it. If there were only one comma, before or after "like Excel" it would read the way you read it. Can be tricky for non-native (and sometimes also native) speakers.
for line in input: ','.join(line)
It's not exactly a problem with "CSV" specifically, but the environment in which it exists.
That's not relevant. If a data format coerces data when it's stored, it's still not a data format suitable for serialization.
If you do add column types to your tables as in traditional databases, then of course you should expect some varying behavior based on that.
I can imagine some potential for issues from people expecting to insert exact decimal numbers and choosing floating-point by accident, perhaps, or using integer types for actually-text numeric ID fields. If you go all-strings on input with no column affinity, then you'll get the same strings on output, though, so that's isomorphic to good-CSV.
¹ https://www.sqlite.org/datatype3.html – §3.1 “If the declared type for a column contains the string "BLOB" or if no type is specified then the column has affinity BLOB.” + §3 “A column with affinity BLOB does not prefer one storage class over another and no attempt is made to coerce data from one storage class into another.”
I'd be satisfied if Excel was just a little less aggressive about it's data coercion on import.
It also runs on all kinds of embedded devices in addition to personal computers and servers.
Can I click on it and open it in Excel?
If not then it is not portable for me and for a lot of other people.
The main reason I use CSV is to produce reports that I can either open myself or send to other people so that they can click on it and open themselves and immediately start hacking away.
Excel is still corporate lingua franca and until this changes CSV is not going to retire.
CSV means for me that I can send it to anybody and everybody will know how to use it.
Try sending SQLite binary to people you work with, to your accountant, to your wife. Then share your experience.
Not all editors make it easy for you to use a tab character.
If only there was some other solution that would not require you to use a character that a lot of editors refuse to insert or even try to convert to spaces...