I'd say better to export tables to CSV files, and dump the schema to an SQL file.
- whether quotes are ""doubled like SQL92 or backslash-\"escaped like C
- whether newlines are quoted or \n like C
- whether there are column headings
- what the character set of the file is (Excel insists it's the local machine's codepage)
- whether that's a BOM or part of a column heading
- whether lines terminate with \n or \r\n or \r or \r\n
- what to do about ragged rows
and so on. Except for the simplest of datasets, CSV is almost certainly going to lose information. The only advantages over SQLite is streaming support and maybe compression, but even then there are better formats.
I would say any format that is compatible with Excel is not suitable for archival. Ever open a file with numeric values with more than say, 20 (I forget the actual cut off) digits? Excel converts to scientific notation and loses precision. When you change the format there is data loss. The length data is preserved but not the full precision.
~ is a better choice for field separator though, it is far less commonly used in most datasets and natural languages. | can also make sense for the same reason, and visually looks like a column divider as an extra bonus.
So you have to close it and use Data Import.
We just moved to the latest version of Office 365 at the job and it still works like this (even if the import action can usually figure out that semicolon is the separator without me having to manually specify it...).
Jokes aside, we had that debate in our team way back and decided to follow the rfc and only use commas. The format is often abused, better not create more tools that do so.
I don't know where there's never been some effort to create a "CSV format specification"; for example the first line could indicate quoting, delimiter, etc. style used in the file.
Guess it's never been big enough of a problem for people to take action. "Good enough" (or rather, "not bad enough").
Or a pipe. Or the unprintable “field separator” character.
> I don't know where there's never been some effort to create a "CSV format specification"; for example the first line could indicate quoting, delimiter, etc. style used in the file.
Fundamentally CSV or any delimited format requires agreement between parties for interchange. At that point standards don’t really help because the parties can just agree to anything that works.
> Guess it's never been big enough of a problem for people to take action. "Good enough" (or rather, "not bad enough").
Exactly.
You used to be able to ctrl + letter to input control keys on traditional terminals — was it (ord(char) - 0x20), but outside Emacs, vi and similar editors, I am sure you can't do that anymore.
If you are questioning existence of human-readable/writeable (typeable?) data formats, you might want to check out SGML/XML, JSON, YAML... Are you surprised that being able to type it out holds true for all of these? Esp on standard keyboards?
It's more interesting how SGML-derived languages have kept <> symbols so accessible on keyboards regular people use.
RFC 4180 (https://tools.ietf.org/html/rfc4180) "Common Format and MIME Type for Comma-Separated Values (CSV) Files"
$ cat file.csv
CSV,quote=always,quotechar=",header=yes,delim=,
Col1,Col2
"val","val2"
[..]
There are other cases where embedding some "metadata" in CSV files is useful; for example the exports I create in my app are versioned by prefixing the first header with the version number. It works, but it's not exactly great.
Of course, the downside is legacy software not being able to process it.
Of course, there might be programs that write invalid CSV or parsers that accept invalid CSV, but that doesn’t mean CSV isn’t a standardised format.
Like Excel?
RFC 4180 exists but that doesn't mean any file with .csv at the end of the name or a text/csv MIME type can be parsed according to it. Also, you still have to track the metadata, such as the charset and if the first line is a header. Both are optional in the RFC.
e: RFC 4180 is not a standard.
The character set is also forgivable given the age of the format; it comes from an era when there wasn’t an agreed single character encoding to rule them all. Sure, RFC 4180 probably should have specified UTF-8 but by that point there was already three decades of CSV usage in different encodings on mainframes which are likely still in use even now to make specifying any character encoding rather pointless.
Don’t get me wrong, I’m not a massive fan of CSV either. The way quoted new lines are encoded, for example, is horrible and I’ve never liked the “” format for escaping quotation marks. But a lot of CSVs biggest problems are ironically a result of its success: as a format it’s so easy to use that a great many developers can hand crank their own parsers without looking at the spec. So I find it hard being critical about the lack of standardisation in CSV when the real problem is the number of developers who don’t follow the standardisation.
It isn't being compared to all formats, it is being compared to sqlite. Some random version of one implementation written in C, stored in "fossil" may be good enough for the library of Congress, but that kind of nonsense went too far for even browser makers to take seriously.
Meanwhile it is trivial to read and infer data from formats like CSV to either use directly or automate a reading process with any tools that exist now or in the future.
It is an informal. RFC just says it's not defining the Internet standard. The reason being, and as I'd already pointed out, CSV predates RFC 4180 by 3 decades so what the RFC is really setting out is to describe what those established conventions are and what the MIME type should be. That doesn't mean that what the RFC describes isn't already a convention (it's the same specifications as published by IBM, W3C and many other big hitters).
> it would still be a poor choice for archival purposes.
I hadn't suggested it was a good choice for archival purposes. You're building a straw man argument there.
My point was that there is a conventional standard to CSV and that is very well documented. The issue with CSV is that it's so easy to write a parser that some developers do so without bothering to read any documentation on how CSV should be parsed. That's not the fault of CSV, that's the fault of lazy developers. But I do completely agree that CSV has other faults (which I had also discussed too).
RFC4180 isn't an Internet standard. Says so right at the top in the first paragraph.
I think I have a few thousand files on my computer right now whose names end in ".csv", and I'll bet money not one of them agrees with RFC4180 to the letter except by accident of the data itself, and that's a Real Problem to me.
I can concede that two parties could agree to interchange according to RFC4180, but as a general format I maintain that for archival and interchange purposes CSV cannot be divorced from the rather complex schema I alluded to without data loss.
I didn't say it was an Internet standard. I said standardised. Ok, I'll concede it is more of an informal or de facto standard but when IBM, W3C, IETF, OKF and others all publish the same parsing rules for CSV, it's hard to agree when people make statements like "there's no standard in CSV". The problem isn't that there isn't a standard to CSV, the problem is that people often don't follow those conventions. But you have that same problem with other file formats too.
> I think I have a few thousand files on my computer right now whose names end in ".csv", and I'll bet money not one of them agrees with RFC4180 to the letter except by accident of the data itself, and that's a Real Problem to me.
That's just conjecture. And even if that were proven true, it's still only anecdotal. That said, I do sympathise with your point. But you could make the same argument for
- JSON files that don't follow spec (support for comments, aren't UTF-8 encoded, have been manually written so don't follow the escaping rules correctly and thus only parse correctly by chance).
- XML files that have been manually cranked and so don't follow schema
- HTML documents that don't follow specification and thus browsers do a lot of non-specification interpretation work to render correctly
The IT industry is littered with example of people not following the docs. CSV isn't unique in that regard.
> I can concede that two parties could agree to interchange according to RFC4180, but as a general format I maintain that for archival and interchange purposes CSV cannot be divorced from the rather complex schema I alluded to without data loss.
I wasn't commenting on whether it's a better format than another file format. I was commenting on your points about standardisation saying there are an abundance of published documents on how to read and write a standard CSV file and C-style escaping isn't part of that specification.
I’m not a CSV fanboy by any means but I have used CSV with a number of enterprise solutions because that’s all they’d offer. And my experience is that the comments levelled against it in this discussion are overstated.
Quotes are doubled
Newlines are quoted
Both having headings and not having headings is valid. Document/data specific.
Use utf8
Do not use bom at all. But if for whatever reason you must use it in column heading, quote it.
Idk which line ending but you should probably handle both
Do not emit ragged rows.I think the implication here is that the archival should be done into a SQLite database and then all you need is a SQLite binary to read it. So there wouldn't even be a need to use a load script.
Excel is too limited, odf formats are typically not very extended outside OO or LOffice..., same with parquet and other file formats. Sadly this is how it is.
You can use SQLite as a file for moving data around, as I do, but I guess it's not practical for everyone.
Or you could just take the entire engine with you, which is what SQLite provides.