No, SQLite's dynamic data types would silently coerce data just like opening a CSV directly with Excel does.
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.
Excel's default insistence on trying to coerce the data into what it expects the data to be is the source of many of the problems of people using CSV. Excel will even ignore quotes around values if it thinks that the value inside might be a number. I often work with CSV files that contain 17 digit IDs that use numeric characters "01233998742021925" which Excel will convert lossily into scientific notation rather than leaving it as a string. There are ways to override that but they are tedious and people don't do it by default.
I'd be satisfied if Excel was just a little less aggressive about it's data coercion on import.
> No, SQLite's dynamic data types would silently coerce data just like opening a CSV directly with Excel does.
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.
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
da_chicken|4 years ago
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.
Tagbert|4 years ago
I'd be satisfied if Excel was just a little less aggressive about it's data coercion on import.
masklinn|4 years ago
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
lmilcin|4 years ago
srcreigh|4 years ago
It also runs on all kinds of embedded devices in addition to personal computers and servers.
https://en.wikipedia.org/wiki/SQLite#Operating_systems