20 ms·
CSVs: The Good, the Bad, and the Ugly
- rav 6y agoLine-delimited JSON works, but imposes a high size overhead (field names are repeated for every record) and is not well supported in non-programming environments. The article only discussed line-delimited JSON objects. How about line-delimited JSON arrays? You put the field names in an array on the first line, and the values in arrays on the subsequent lines. Although in my day-to-day, the overhead of field names is rarely problematic.
- dragonwriter 6y agoYeah, line delimited JSON where each entity is a JSON array seems to hit exactly the point being aimed at. Also, like XML (definitely not space efficient) with SAX parsers, plain JSON has streaming processors available. They are naturally more complex than streaming processors for line-oriented formats, but they exist, so plain JSON, either array of arrays objects with the main data in array of array is an alternative, e.g.: { "headers": [...], "body": [ [...], [...], ... ] } the last form is also trivially extensible to support other keys for additional metadata, without harming the ability of applications without knowledge of the other keys to process the main data
- CraftThatBlock 6y agoI think the OP was talking more about: ["name","address"] ["Bob","1 Apple Street"] ["John","2 Apple Street"] It's compatible with JSON Lines, and mostly equivalent to CSVs, at least how they are most commonly used.
- jrimbault 6y agoThat was also my thought getting to this line. First line is a list of the records fields names, then every line is a tuple of a record, convert tuple to struct and done.
- mcswell 6y agoI've seen very large JSON files passed around in zipped form, and there's a Python library that reads them in this form (afaict without first creating an uncompressed version of the entire zipfile). I would think if you zipped a JSON file where the fields are repeated, the compression algorithm would pick up on this and compress those field names (including their quotes and trailing colon etc.). Or do I misunderstand how compression works?
- andi999 6y agoBut dont forget, there is only two kinds of decimal separators in this world, those with comma and those with dots...
- mcswell 6y agoOr as someone might say, there are only 10 kinds of binary separators.
- pachico 6y agoI though it was already a standard to treat \NULL as null in CSV parsers.
- undecisive 6y agoI was hoping to see some mention of the security downsides of CSVs - something that as a programmer of over 10 years experience, I wasn't aware of until a couple of years ago. If you hand a CSV to a normal person, they'll open it in MS Office. Or LibreOffice. If you put a formula into any cell starting with an `=` character, the office suite will give you a terse warning about updates or something, and when you accept, it will run every formula. For example, you could write a formula to convert cells A1:D10 to params, and make a GET request to an address on your personal server. If the CSV contains user submitted data, e.g. if someone has set that formula as their name, and the CSV hasn't been carefully sanitised, then hand someone from the business your CSV to build some sort of report, they may well be sending much of the data to a 3rd party. While maybe not a CSV-specific issue, certainly CSVs are believed to be safe (it's just a text file! We're only using it internally!) They really are not safe, and it sometimes takes a non-trivial amount of work to make them safe.
- fctorial 6y agoThis is a bug in spreadsheet csv import functionality.
- undecisive 6y agoIn that it's undesirable behaviour, yes. Certainly the messaging could be a lot clearer. But the fact that multiple office suites do the same thing... one could argue that there are good reasons for allowing formulae in CSV files. So I would argue that the "bug" is never going to be fixed, and that the real fix has to be our attitudes. We need to start treating CSVs the same way as we treat HTML - sanitise any user-provided strings strongly, quote all fields, etc.
- masklinn 6y ago> If you put a formula into any cell starting with an `=` character, the office suite will give you a terse warning about updates or something, and when you accept, it will run every formula. Also Excel use the prefix apostrophe (') as an escape character to force text interpretation of a cell. It will save that escape character to CSV, and will interpret an apostrophe prefix as an escape character.
- sharpercoder 6y agoWe can still decide to start to use ascii 34, 35, 36 and 37. Sure, we'll need to deal with tsv and csv backward compat. But at least we won't have any escaping problems and can do very simple parsing.
- yardshop 6y agoThose are characters " # $ % if you meant decimal codes, and characters 4 5 6 7 if you meant hexadecimal. I would guess you didn't mean either. Maybe you meant 1 2 3 4 which are SOH STX ETX EOT, which are transmission control codes for serial communications. Or maybe you meant decimal 28 29 30 31 which are FS GS RS US, which are intended for the purpose of structuring data on magnetic tape. The problem with those low order characters [0] is typing them and seeing them in editors. It's not impossible to do, but just not commonly done. 0: https://en.wikipedia.org/wiki/Control_character https://en.wikipedia.org/wiki/Control_character
- every 6y agoYeah, Ctrl+_ (oct 37) has been sitting there patiently waiting since 1963...
- pmoriarty 6y agoI never got why TSV's aren't more popular. TSV's are exactly like CSV's but only use TABs instead of commas as delimiters. This simply and elegantly solves one major issue with CSV's, which is that commas are quite common in real data. TABs, in contrast, are not. It's not perfect, but it's a simple, real-world solution that popular software packages (like Excel) which consume CSVs will readily recognize.
- jacobsenscott 6y agoThere's no upside. Tabs are common in real world data. If someone's csv library is naive enough to fail to quote a field with a comma it will also fail to quote a field with a tab. Changing the delimiter is just moving deck chairs around on the titanic.
- BlueTemplar 6y agoDepends if your data is mostly text or numbers. I'd say that commas are still more common, though one issue with tabs is that they are 'invisible'.
- fctorial 6y agoOr NSVs (null separated values). It doesn't have any downsides.
- masklinn 6y agoNSVs break any text-based tools e.g. not only is it hard to view them using standard tooling, you can't diff them either. And if you're going to use stupid separators, at least use US (0x1F), that's the entire point of that thing.
- fctorial 6y agohttps://prnt.sc/vf7699 https://prnt.sc/vf7699 This is how vim renders an NSV file. And I honestly don't remember the last time I edited a csv file by hand.
- jacobsenscott 6y agoCSVs are the worst and only option for data exchange in 99% of the cases. Most CSVs travel through excel at some point in their lives, so we need to talk about excel: Excel neither reads nor writes UTF-8 CSV files without jumping through hoops. Excel saves files in different character sets on MacOS and Windows. Excel tries to interpret fields with digits as numbers, stripping off leading 0's, etc. (think serial numbers or whatever with leading 0's) Excel will try and interpret date fields and write them out in a different format. Outside of Excel: There is no way to know what character set a CSV file is encoded in. You can only guess. UTF BOM - sometimes there, sometimes not. If the file is malformed but still opens in Excel it is a bug in your CSV parser, and not a problem with the producer of the file (so they will tell you) Most CSV libraries are extremely buggy and cannot generate CSV to spec. Most CSV libraries are extremely buggy and cannot read CSV that meets spec. Many programmers think CSV is easy and do not use a library go generate or read CSV producing even worse results than their language's buggy CSV library. The spec is just a suggestion Nobody reads the spec That's just the tip of the iceberg
- fctorial 6y ago> Most CSV libraries are extremely buggy Examples? (Of libraries and bugs)
- VWWHFSfQ 6y agoop is being very hyperbolic. most widely used and supported csv libraries are not nearly as deficient as op claims.
- maerF0x0 6y agogolang builtin doesnt handle UTF BOM well . have to preprocess the io.Reader
- slrz 6y agoSeems reasonable to me: this way, you don't have to duplicate the BOM stripping in the decoders of every single format you want to support. It's also more general as there are other commonly-needed transformations that have an even weaker case for explicit support in the CSV library (charset conversion, dropping other bogus bytes, ...).
- breck 6y ago> the wrong delimiter I personally try to always generate my data as SSVs. Occasionally I'll have a need for one column with multiple words, in which case I'll make it the last column and it poses no parsing problems as I include the header always and then can just parse the last column as rest. This means you need to be restrictive on the types in your data—which I've always found helps with a lot of other things anyway. No commas, no tabs, no newlines, no quotes—none of that is allowed in a value, and then you have nice clean data ready to parse, that is URL friendly, etc. Of course, when I send my data back out into the world, need to "compile" it to CSV, as almost no one uses SSV. > CSVs have no types, everything is a string. I think this will be solved in the next 10 years or so. We'll have something that's like Schema.org meets DefiniteltyTyped. A "Common Tongue" for types, with a Wikipedia like community and an easy syntax to extend at the last mile for defining your own dialect to suit your worldview.
- BlueTemplar 6y ago> SSV https://en.wikipedia.org/wiki/SSV https://en.wikipedia.org/wiki/SSV All Delimiters Matter !
- grammarnazzzi 6y ago> I personally try to always generate my data as SSVs That's great. I generate my data using either ZSVs or sometimes ΩSVs
- deleted 6y ago[deleted]
- deleted 6y ago[deleted]
- breck 6y agoaddendum: SSV = "Space Separated Values" Name Score Jill 123
- orev 6y agoYou can get away with structured formats only when everything is fully automated, which works for APIs, but anything that needs human review will almost always be opened in Excel. That rules out anything fancy like JSON. Excel supports XML, but it think most people don’t know how to use that, and I suspect it would only be useful for simple tabular data — not complex structures. Many/most business processes are not 100% automated, so you just have to fall back on something that works with standard business tools (Excel).
- rattray 6y agoThe worst thing, to me, mentioned in "Bad" is that CSV's are untyped, which especially makes distinguishing nulls and empty strings impossible. I wonder if a "typed csv" standard could emerge, like `.tscv` eg; Name::String?,ID::String,Born::Date Foo,0123,2020-01-01 \NULL,0124,2020-01-02 Still streamable, human-readable/editable (although not with excel et al til they are modified to open it), compact, simple. Of course, would only be useful to actual Data People for a while, so would not have ubiquity.
- rattray 6y agoComing back to this, I think a tuple-oriented JSON convention, like suggested elsewhere in the comments, would make more sense given the target market.
- bobbylarrybobby 6y agoYes, something like { headers: [...], types: [...], data: [ [...], [...], ... ] }
- rattray 6y agoExactly. Could even add a doctype of some kind to indicate nothing follows the "data" key to help with streaming (though I'm not familiar with streaming json and this may not really help in practice).
- soul_grafitti 6y agoYou could look at it from the perspective that the only type is text. When you do this the responsibility for managing the data type rests with the parser and not the encoder.
- simonebrunozzi 6y agoSlightly tangential, but my feeling is that most people don't know where the "the good, the bad, the ugly" expression comes from. It's a "spaghetti western" movie [0], directed by Sergio Leone, released in 1966, and credited with the rise to fame for Clint Eastwood. If you have never watched it, I highly recommend it. In the genre, it is probably among the best. Music was composed by Ennio Morricone, another pillar of Italian cinema and music, who died last July. One of the theme songs for the movie, "The Ecstasy of Gold", is very famous all around the world, and even Metallica played it at some concerts. [1] Also, interesting detail from a Quora answer [2] - I wasn't aware of the locations used to film until now: > The term "spaghetti western" was used by American filmgoers and reviewers because most were produced and directed by Italian film-makers, many of them trying to copy Leone’s unique style. > Most were filmed on low budgets and shot at various locations around southern Italy and Spain. The Tabernas Desert and Cabo de Gata-Nijar were especially popular shooting locations. [0]: https://en.wikipedia.org/wiki/The_Good,_the_Bad_and_the_Ugly https://en.wikipedia.org/wiki/The_Good,_the_Bad_and_the_Ugly [1]: https://en.wikipedia.org/wiki/The_Ecstasy_of_Gold https://en.wikipedia.org/wiki/The_Ecstasy_of_Gold [2]: https://www.quora.com/Where-does-the-expression-the-good-bad-and-ugly-come-from#:~:text=The%20title%20refers%20to%20the,influential%20western%20movies%20ever%20made https://www.quora.com/Where-does-the-expression-the-good-bad....
- omaranto 6y ago> Slightly tangential, but my feeling is that most people don't know where the "the good, the bad, the ugly" expression comes from. I hope you're wrong about most people not knowing about the movie.
- simonebrunozzi 6y agoMaybe. But perhaps young people (<25-30) don't know it well?
- captrb 6y ago"Parquet files work well, but streaming is a tad more complex (you need to be able to seek to the end of the file to read the metadata before you can stream the contents)" I didn't realize that all the metadata in Parquet was stored at the end. That is indeed unfortunate for streaming use cases. Especially sad because columnar dictionary formats can offer great compaction for some data. I've been achieving 20x+ size redutions by converting from CSV to Parquet.
- KptMarchewa 6y agoDo you think of any solutions for this other than batching? I'm working on something similar just now, and I buffer several million records in memory, then write the file to external storage.
- BorisTheBrave 6y agoParquet is intended as a file storage format primarily. When streaming, I think you are recommended to use Arrow, which is basically an in-memory Parquet. It supports putting the schema first and streaming a undefined number of rows. https://arrow.apache.org/docs/python/ipc.html https://arrow.apache.org/docs/python/ipc.html
- akbo 6y agoYou can stream AVRO records. AVRO is a well defined, binary, space efficient serialization format that can be used to serialize individual records (for streaming) or a whole set of records (for file storage). That should cover all of the authors requirements, right? More info: https://www.confluent.io/blog/avro-kafka-data/ https://www.confluent.io/blog/avro-kafka-data/
- LukeEF 6y agoLuke from TerminusDB here - we've been building automatic import and versioning features for CSVs over the last number of months. Just went into canary release actually. Agree very strongly with the benefits and problems of CSVs in the article. CSVs are crazy flexible and if you are a team with a high velocity of changes and experiments with data, then it can make your life easier to work with CSVs (versus the other options). Terminus is a straight-up open source graph database with revision control at the core. We noticed the CSV (and at best append to Git) problem when we were developing the solution. Loads of teams (irl) use CSVs + email for the storage and distribution of data (and features). What happens is: Data versioning is required but not uniformly specified or facilitated. It ends up being a suffix on a file, rather than a proper version-controlled system as is now common with Code. Data communication is undertaken by sending large files via slack or e-mail. This means multiple inconsistent versions can float around the team without any visibility to project managers, and without any assurance that what you have is the latest version. (As per the article and some comments) CSVs contain no type information, so information is routinely marshaled into and out of formats leading to extra “cleaning” time and hard to find errors (NaNs) which may not even appear in the final feature selection. Apparently 'best practice' is to put all changes back into the data warehouse, but the reality is a 'shadow data' economy within enterprise and an open CSV economy in startups. We are all about bridging those worlds. Give you a GitHub-like way to manage your CSVs so they are version controlled and give the team the ability to see who is making changes. The ability to work offline and then sync up later (like pushing in Git) is a really important aspect. Can really help with CSV problems (we know as first thing we did was shove all our CSVs into TerminusHub).
- neonate 6y agohttps://archive.is/wQQ3h https://archive.is/wQQ3h
- framecowbird 6y agoWe use CSVs a lot and a good schema for a metadata file would be very useful. Rather than making up my own, does anybody know of a good, well-adopted metadata format for CSV files? I found https://specs.frictionlessdata.io/tabular-data-package https://specs.frictionlessdata.io/tabular-data-package, but not sure how widely used it is.
- Evidlo 6y agohttps://www.w3.org/TR/tabular-data-primer/#column-info https://www.w3.org/TR/tabular-data-primer/#column-info Or if you want column information embedded into the csv itself: https://www.w3.org/TR/tabular-data-model/#embedded-metadata https://www.w3.org/TR/tabular-data-model/#embedded-metadata
- drej 6y agoThat incorrect parsing of fields wih newlines is real. Here’s a demonstration how you can essentially inject made up data into someone’s analysis if you know they will use the most popular Apache project and the de-facto standard tool for big data processing (with default settings): https://kokes.github.io/blog/2020/06/12/high-performance-data-loss.html#lesson-6-all-bets-are-off https://kokes.github.io/blog/2020/06/12/high-performance-dat...
- amelius 6y agoIf you want to know how ugly a format is, just look at all the options you have to enter correctly when you try to open it.
- chubot 6y agoUltimately there’s no format that corrects all of CSVs flaws while retaining its benefits. I'm planning to fix this with https://www.oilshell.org/ https://www.oilshell.org/ ! Eliminating the need for ad hoc parsing and splitting is one of the Four Features That Justify a New Unix Shell: http://www.oilshell.org/blog/2020/10/osh-features.html http://www.oilshell.org/blog/2020/10/osh-features.html I've designed a format called QTSV (quoted TSV) which lets you embed newlines and tabs in fields. It's built on QSN (quoted string notation), which are essentially just Rust string literals: http://www.oilshell.org/release/latest/doc/qsn.html http://www.oilshell.org/release/latest/doc/qsn.html QSN is fully implemented, but QTSV isn't done yet. Proposal here: https://github.com/oilshell/oil/wiki/TSV2-Proposal https://github.com/oilshell/oil/wiki/TSV2-Proposal However the latest Oil release actually does emit QTSV! The "pp" builtin (for pretty-print) will print a QTSV file of shell function names (procs) and their doc comments (like docstrings). ---- If you'd like this to be a reality, contact me and help out! Useful things to do: - write a messy CSV to strict QTSV converter (it will need some flags for the heuristics, like Python's CSV library) - write a QTSV library in your language [1] https://www.oilshell.org/release/0.8.4/ https://www.oilshell.org/release/0.8.4/
- neolog 6y agoI've always wondered why parquet and arrow put metadata at the end.