Just don't write that love letter in French ... or any language that uses comma for decimals
A love letter to the CSV format
441–450 of 711 posts
Re: A love letter to the CSV format
#442Excel hates CSV only if you don't use the "From text / csv" function (under the data tab). For whatever reason, it flawlessly manages to import most CSV data using that functionality. It is the only way I can reliably import data to excel with datestamps / formats. Just drag/dropping a CSV file onto a spreadsheet, or "open with excel" sucks.
Even "From Text / CSV" sucks: It inserts an extra row at the top for its pivot table, with entries "Column1, Column2, ...". So if you export to CSV again, you now have 2 header rows. So Excel can't roundtrip CSVs, and the more often you roundtrip, the more header rows you get. You need to remember to manually delete the added header row each time, otherwise software you export back to can't read it.
That's strange, I've never seen this behaviour. Loading a CSV this way (Data -> From Text/CSV) always parses the first record as the header for me.
Re: A love letter to the CSV format
#443The post should at least mention in passing the major problem with CSV: it is a "no spec" family of de-facto formats, not a single thing (it is an example of "historically grown"). And omission of that meams I'm going to have to call this our for its bias (but then it is a love letter, and love makes blind...). Unlike XML or JSON, there isn't a document defining the grammar of well-formed or valid CSV files, and ther…
In fairness there are also several ambiguities with JSON. How do you handle multiple copies of the same key? Does the order of keys have semantic meaning? jq supports several pseudo-JSON formats that are quite useful like record separator separated JSON, newline separated JSON. These are obviously out of spec, but useful enough that I've used them and sometimes piped them into a .json file for storage. Also, encoding…
> How do you handle multiple copies of the same key
That’s unambiguously allowed by the JSON spec, because it’s just a grammar. The semantics are up to the implementation.
Re: A love letter to the CSV format
#444Earlier quoted context omitted.
> CSV is lists of lists of fixed length. I'd definitely put that in my list of falsehoods programmers believe about CSV files.
It seems to be indicated by RCF-4180 which says > This header will contain names corresponding to the fields in the file and should contain the same number of fields as the records in the rest of the file But of course, CSV is the wild west and there's no guarantee that any two encoders will do the same thing (sometimes, there's not even a guarantee that the same encoder will do the same thing with two different inpu…
You should know that "should" isn't very binding.
Headers should have as many rows as possible that contain data items for their column and data items in a row should have a header for the respective columns, but real CSV files should be assumed to have incomplete or variable length lines.
Re: A love letter to the CSV format
#445Earlier quoted context omitted.
Have you had to work with csv files from the wild much? I'm not being snarky but what you're talking about is night and day to what I've experienced over the years. There aren't vast numbers of different JSON formats. There's practically one and realistically maybe two. Headers are in each line, utf8 has never been an issue for me and quoting and escaping are well defined and obeyed. This is because for datasets, alm…
What's the problem with capital I?
The byte for a capital I is the same as the start for an odd file format, slyk maybe? Excel has (or did if they finally fixed it) for years decided this was enough to assume the file (called .csv) cannot possibly be csv but must actually be slyk. It then parses it as such, and is shocked to find your slyk file is totally broken!
Re: A love letter to the CSV format
#446Earlier quoted context omitted.
JSON serialized without extra white space with one line per record is superior to CSV. If you want CSV-ish, enforce an array of strings for each record. Or go further with actual objects and non-string types. You can even jump to an arbitrary point and then seek till you see an actual new line as it’s always a record boundary. It’s not that CSV is an invalid format. It’s that libraries and tools to parse CSV tend to…
What happens when you need to encode the newline character in your data? That makes splitting _either_ CSV or LDJSON files difficult.
Actually even whitespace-separated json would be a valid format and if you forbid json documents to be a single integer or float then even just concatenating json gives a valid format as JSON is a prefix free language.
That is[0] if a string s is a valid JSON then there is no substring s[0..i] for i So you could just consume as many bytes you need to produce a json and then start a new one when that one is complete. To handle malformed data you just need to throw out the partial data on syntax error and start from the following byte (and likely throw away data a few more times if the error was in the middle of a document)
That is [][]""[][]""[] is unambiguos to parse[1]
[0] again assuming that we restrict ourselves to string, null, boolean, array and objects at the root
[1] still this is not a good format as a single missing " can destroy the entire document.
Re: A love letter to the CSV format
#447The post should at least mention in passing the major problem with CSV: it is a "no spec" family of de-facto formats, not a single thing (it is an example of "historically grown"). And omission of that meams I'm going to have to call this our for its bias (but then it is a love letter, and love makes blind...). Unlike XML or JSON, there isn't a document defining the grammar of well-formed or valid CSV files, and ther…
There is no file format that works out of box under all extreme corner cases.
You would think that ie XML-defined WSDL with XSD schema is well battle proven. I've encountered 2 years ago (and still dealing with that) WSDL from a major banking vendor that is technically valid, but no open source library in Java (from all languages) was able to parse it successfully or generate binding classes out of box.
Heck, flat files can end up with extreme cases, just work enough with legacy banking or regulatory systems and you will see some proper shit.
The thing is, any sort of critical integration needs to be battle tested and continuously maintained, otherwise it will eventually go bad, even a decade after implementation and regular use without issues.
Re: A love letter to the CSV format
#448Earlier quoted context omitted.
Right - the JSON-newline equivalent of CSV can look like this: ["id", "species", "nickname"] [1, "Chicken", "Chunky cheesecakes"] [2, "Dog", "Wagging wonders"] [3, "Bunny", "Hopping heroes"] [4, "Bat", "Soaring shadows"]
This misses the point of standardization imo because it’s not possible to know a priori that the first line represents the variable names, that all the rows are supposed to have the same number of elements and in general that this is supposed to represent a table. An arbitrary parser or person wouldn’t know to guess since it's not standard or expected. Of course it would be parsed fine but the default result would be…
application/jsonl+table
Re: A love letter to the CSV format
#449The post should at least mention in passing the major problem with CSV: it is a "no spec" family of de-facto formats, not a single thing (it is an example of "historically grown"). And omission of that meams I'm going to have to call this our for its bias (but then it is a love letter, and love makes blind...). Unlike XML or JSON, there isn't a document defining the grammar of well-formed or valid CSV files, and ther…
To be honest, I'm wondering why you are rating JSON higher than CSV. > Unlike XML or JSON, there isn't a document defining the grammar of well-formed or valid CSV files, There is, actually, RFC 4180 IIRC. > there are many flavours that are incompatible with each other in the sense that a reader for one flavour would not be suitable for reading the other and vice versa. "There are many flavours that deviate from the s…
> There is, actually, RFC 4180 IIRC.
Does any software fully follow that spec (https://www.rfc-editor.org/rfc/rfc4180)? Some requirements that I doubt are commonly followed:
- “Each record is located on a separate line, delimited by a line break (CRLF)” ⇒ editing .csv files using your the typical Unix text editor is complicated.
- “Spaces are considered part of a field and should not be ignored”
- “Fields containing line breaks (CRLF), double quotes, and commas should be enclosed in double-quotes” ⇒ fields containing lone carriage returns or new lines need not be enclosed in double quotes.
Re: A love letter to the CSV format
#450I've recently been developing a raspberry pi based solution which works with telemetry logs. First implementation used an SQLite database (with WAL log) – only to find it corrupted after just couple of days of extensive power on/off cycles. I've since started looking at parquet files – which turned out to not be friendly to append-only operations. I've ended up implementing writing events into ipc files which then pe…
If sqlite ends up corrupted, why wouldn't a CSV? What happens if the system dies partway through a write?
It would, but it'd be very easy to skip corrupted lines. With SQLite, I ended up losing data since the corrupted entry.