14 ms·
CSV is ever so elegant but it has one fatal flaw - quoting has "non-local" effects, i.e. an extra or missing quote at byte 1 can change the meaning of a comma a
by mjw_byrne 2y ago
CSV is ever so elegant but it has one fatal flaw - quoting has "non-local" effects, i.e. an extra or missing quote at byte 1 can change the meaning of a comma at byte 1000000. This has (at least) two annoying consequences:
1. It's tricky to parallelise processing of CSV.
2. A small amount of data corruption can have a big impact on the readability of a file (one missing or extra quote can bugger the whole thing up).
So these days for serialisation of simple tabular data I prefer plain escaping, e.g. comma, newline and \ are all \-escaped. It's as easy to serialise and deserialise as CSV but without the above drawbacks.
- LPisGood 2y agoI always treat CSVs as comma separated values with new line delimiters. If it’s a new line, it’s a new row.
- criddell 2y agoDo you ever have CSV data that has newlines within a string?
- thesuitonym 2y agoI don't. If I ever have a dataset that requires newlines in a string, I use another method to store it. I don't know why so many people think every solution needs to to be a perfect fit for every problem in order to be viable. CSV is good at certain things, so use it for those things! And for anything it's not good at, use something else!
- criddell 2y ago> use something else You don't always get to pick the format in which data is provided to you.
- thesuitonym 2y agoTrue, but in that case I'm not the one choosing how to store it, until I ingest the data, and then I will store it in whatever format makes sense to me.
- deleted 2y ago[deleted]
- kittoes 2y agoI don't think we do? It's more that a bunch of companies already have their data in CSV format and aren't willing to invest any effort in moving to a new format. Doesn't matter how much one extolls all the benefits, they know right? They're paying someone else to deal with it.
- LPisGood 2y agoNo - that’s what I’m trying to say. If I have newlines I use something else.
- andrepd 2y agoThat would be solved by using the ASCII control chars Record Separator / Unit Separator! I don't get how this is not widely used as standard.
- mjevans 2y agoThe _entire_ point of a CSV file is that it's fully human readable and write-able. The characters you mention could be used in a custom delimiter variant of the format, but at that point it's back to a binary machine format.
- kevmo314 2y agoWhat do you mean? I just push the Record Separator key on my keyboard. /s in case :)
- mbreese 2y agoI’m pretty sure those used to exist. But when looking for a picture to back up my (likely flawed) memory, Google helpfully told me that you can get a record separator character by hitting Ctrl-^ (caret). Who knew?
- corysama 2y agoThe entire argument against ASCII Delimited Text boils down to "No one bothered to support it in popular editors back in 1984. Because I grew up without it, it is impossible to imagine supporting it today." You need 4 new keyboard shortcuts. Use ctrl+, ctrl+. ctrl+[ ctrl+] You need 4 new character symbols. You need a bit of new formatting rules. Pretty much page breaks decorated with the new symbols. It's really not that hard. But, like many problems in tech, the popular advice is "Everyone recognizes the problem and the solution. But, the problematic way is already widely used and the solution is not. Therefore everyone doing anything new should invest in continuing to support the problem forever."
- zzo38computer 2y agoThe keys would be something other than those, though. They would be: CTRL+\ for file separator, CTRL+] for group separator, CTRL+^ for record separator, CTRL+_ for unit separator. Other than that, it would work like you described, I think. > But, like many problems in tech, the popular advice is "Everyone recognizes the problem and the solution. But, the problematic way is already widely used and the solution is not This is unfortunately common. However, what else happens too, is disagreement about what is the problem and the solution.
- da_chicken 2y agoEh, all you're really saying is "I'm not using CSV. Instead I'm using my CSV." Except that's all that anybody does. CSV can just as easily support escaping as any other format, but there is no agreement for a CSV format. After all, a missed escape can just as easily destroy a JSON or XML structure. And parallel processing of text is already a little sketchy simply because UTF-8 exists.
- fragmede 2y agoI want to push Sqlite as a data interchange format! it has the benefit of being well defined, and can store binary data, like images for product pictures inside the database. not a good idea if you're trying to serve users behind a web app, but as interchange, better than a zip file with filenames that have to be "relinked".
- 0cf8612b2e1e 2y agoOne very minor problem is that you max out storing blobs of 2GB(? I think, maybe 4GB). Granted few will hit this, but this limit did kill one previous data transfer idea of mine.
- Someone1234 2y agoFor context: I have a LOT of experience of interchange formats, like "full time job, every day, all day, hundreds of formats, for 20-years" experience. Based on that experience I have come to one key, but maybe, counter-intuitive truth about interchange formats: - Too much freedom is bad. Why? Generating interchange data is cheaper than consuming it, because the creator only needs to consider the stuff they want to include, whereas the consumer needs to consider every single possible edge case and or scenario the format itself can support. This is why XML is WAY more costly to ingest than CSV, because in XML someone is going to use: attributes, CDATA, namespaces, comments, different declaration, includes, et al. In CVS they're going to use rows, a format separator, and quotes (with or without escaping). That's it. That's all it supports. Sqlite as an interchange format is a HORRIFYING suggestion, because every single feature Sqlite supports may need to be supported by consumers. Even if you curtailed Sqlite's vast feature set, you've still created something vastly more expensive to consume than XML, which itself is obnoxious. My favorite interchange formats are, in order: - CVS, JSON (inc. NDJSON), YAML, XML, BSON (due to type system), MessagePack, Protobuf, [Giant Gap] Sqlite, Excel (xlsx, et al) More features mean more cost, more edge cases, more failures, more complex consumers. Keep in mind, this is ONLY about interchange formats between two parties, I have wildly different opinions about what I would use for my own application where I am only ever the creator/consumer, I actually love Sqlite for THAT.
- 2y ago
- koolba 2y agoJSON 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 suck. Whereas JSON is the lingua franca of data.
- juliansimioni 2y agoWhat happens when you need to encode the newline character in your data? That makes splitting _either_ CSV or LDJSON files difficult.
- mananaysiempre 2y agoWhen you need to encode the newline character in your data, you say \n in the JSON. Unlike (the RFC dialect of) CSV, JSON has an escape sequence denoting a newline and in fact requires its use. The only reason to introduce newlines into JSON data is prettyprinting.
- koolba 2y agoThe new line character in a JSON string would always be \n. The new line in the record itself as whitespace would not be acceptable as that breaks the one line record contract. Remember that this does not allow arbitrary representation of serialized JSON data. But it allows for any and all JSON data as you can always roundtrip valid JSON to a compact one line representation without extra whitespace.
- nmz 2y agoIt's tricky, but simple enough, RFC states that " must be used, inserting a " is done with "". This makes knowing what a record is difficult, since you must keep a variable that keeps the entire string. How do you do this simply? you read each line, and if there's an uneven number of ", then you have an incomplete record and you will keep all lines until there is an odd number of ". after having the string, parsing the fields correctly is harder but you can do it in regex or PEGs or a disgusting state machine.
- taeric 2y agoReminds me of a fatal flaw of yaml. Turns out truncating a yaml file doesn't make it invalid. Which can lead to some rather non-obvious failures.
- nextts 2y agoWhat is the failure mode where a yaml file gets truncated? They are normally config files in Git. Or uploaded to S3 or Kubernetes etc. CSV has the same failure mode. As does HTML. (But not XML)
- bobmcnamara 2y agoBad merges.
- taeric 2y agoI couldn't find the story on it, but there was an instance of a config for some major service getting truncated, but since it was yaml it was more difficult to figure out that that was what happened. I think in AWS, but I can't find the story, so can't really remember. And fully fair that you can have similar issues in other formats. I think the complaint here was that it was a bit harder, specifically because it did not trip up any of the loading code. With a big lesson learned that configs should probably either go pascal string style, where they have an expected number of items as the first part of the data, or xml style, where they have a closing tag. Really, it is always amusing to find how many of the annoying parts of XML turned out to be somewhat more well thought out than people want to admit.
- pasc1878 2y agoSame is true of CSV/TSV.
- taeric 2y agoI think you are a bit more likely to notice in a CSV/TSV, as it is unlikely to truncate at a newline? Still, fair point. And is part of why I said it is a flaw, not the flaw. Plenty of other reasons to not like YAML, to me. :D
- 1vuio0pswjnm7 2y agohttps://www.ietf.org/rfc/rfc4180.txt https://www.ietf.org/rfc/rfc4180.txt
- solidsnack9000 2y agoTab-Separated Value, as implemented by many databases, solves these problems, because tab, newline and other control characters are escaped. For example, the default text serialization format of Postgres (`COPY <table> TO '<file>'` without any options) is this way.
- Yomguithereal 2y agoA good way to parallelize CSV processing is to split datasets into multiple files, kinda like manual sharding. xan has a parallel command able to perform a wide variety of map-reduce tasks on splitted files. https://github.com/medialab/xan https://github.com/medialab/xan
- jgord 2y agonice .. xsv is also very handy for wrangling csv files generally
- Yomguithereal 2y agoxan is a maintained fork of xsv
- dietr1ch 2y agoI don't understand why CSV became a thing when TSV, or a format using the nowadays weird ASCII control characters like start/end of text, start of heading, horizontal/vertical tab, file/group/record/unit separator. It seems many possible designs would've avoided the quoting chaos and made parsing sort of trivial.
- niccl 2y agoAny time you have a character with a special meaning you have to handle that character turning up in the data you're encoding. It's inevitable. No matter what obscure character you choose, you'll have to deal with it
- noosphr 2y agoThe difference is that the coma and newline characters are much more common in text than 0x1F and 0x1E, which if you restrict your data to alphanumeric characters (which you really should) will never appear anywhere else.
- dietr1ch 2y agoThe characters would likely be unique, maybe even by the spec. Even if you wanted them, we use backslashes to escape strings in most common programming languages just fine, the problem CSV is that commas aren't easy to recognize because they might be within a single or double quote string, or might just be a separator. Can strings in CSV have newlines? I bet parsers disagree since there's no spec really.
- wvenable 2y agoExcept we have all these low ASCII characters specifically for this purpose that don't turn up in the data at all. But there is, of course, also an escape character specifically for escaping them if necessary.
- Brian_K_White 2y agoYou can't type any of those on a typewriter, or see them in old or simple simple editors, or no editor like just catting to a tty. If you say those are contrived examples that don't matter any more then you have missed the point and will probably never acknowledge the point and there is no purpose in continuing to try to communicate. One can only ever remember and type out just so many examples, and one can always contrive some response to any single or finite number of examples, but they are actually infinite, open-ended. Having a least common denominator that is extremely low that works in all the infinite situations you never even thought of, vs just pretty low and pretty easy to meet in most common situations, is all the difference in the world.
- msla 2y agoCSV's actual problem is that there's no single CSV, and you don't know what type you have (or even if you have single consistent type through the whole file) without trying to parse the whole file and seeing what breaks. Is there quoting? Is that quoting used consistently? Do you have five-digit ZIP codes, or have the East Coast ones been truncated to four digits because they began with zero? Spin the wheel!
- crazygringo 2y agoI'm not clear why quotes prevent parallel processing? I mean, you don't usually parallelize reading a file in the first place, only processing what you've already read and parsed. So read each record in one process and then add it to a multiprocessing queue for multiple processes to handle. And data corruption is data corruption. If a movie I'm watching has a corrupted bit I don't mind a visual glitch and I want it to keep playing. But with a CSV I want to fix the problem, not ignore a record. Do you really have a use case where reading itself is the performance bottleneck and you need to parallelize reading by starting at different file offsets? I know that multiple processes can read faster from certain high-end SSD's than just one process, but that's a level of performance optimization that is pretty extraordinary. I'm kind of curious what it is!
- Eridrus 2y agoDoing sequential reading into a queue for workers to read is a lot more complicated than having a file format that supports parallel reading. And the fix to allow parallel reading is pretty trivial: escape new lines so that you can just keep reading until the first unescaped new line and start at that record. It is particularly helpful if you are distributing work across machines, but even in the single machine case, it's simpler to tell a bunch of workers their offset/limit in a file.
- akritid 2y agoThe practical solution is to generate several CSV files and distribute work at the granularity of files
- Eridrus 2y agoSure, now you need to do this statically ahead of time. It's not unsolvable, but now you have a more complicated system. A better file format would not have this problem. The fix is also trivial (escape new lines into \n or similar) would also make the files easier to view with a text editor.
- 2y ago
- lelanthran 2y ago> So these days for serialisation of simple tabular data I prefer plain escaping, e.g. comma, newline and \ are all \-escaped. It's as easy to serialise and deserialise as CSV but without the above drawbacks. For my own parser, I made everything `\` escaped: outside of a quote or double-quote delimited string, any character prefixed with a `\` is read verbatim. There are no special exceptions resulting in `\,` producing a comma while `\a` produces `\a`. This makes it a good rule, because it is only one rule with no exceptions.
- mjw_byrne 2y agoI considered this but then went the other way - a \ before anything other than a \, newline or comma is treated as an error. This leaves room for adding features, e.g. \N to signify a SQL NULL. Regarding quoting and escaping, there are two options that make sense to me - either use quoting, in which case quotes are self-escaped and that's that; or use escaping, in which case quotes aren't necessary at all.
- widforss 2y agoHow is this not true for every format that includes quote marks?
- mjw_byrne 2y agoIt is true for everything that uses quoting, I didn't mean to imply otherwise.