60 ms·

CSV 1.1 – CSV Evolved (for Humans)

84 points by polm23 8y ago

Jaruzel 8y ago
I can't see this gaining any traction. There's nothing wrong with CSV as it is for basic data transfer. CSV had been unchanged now for so long, that any attempt to update it will fail - there are simply too many CSV parsers already implemented. If you are worried about people in the year 3000 understanding your data, add a .txt file alongside the CSV explaining the fields.
- Lazare 8y ago
  > There's nothing wrong with CSV as it is for basic data transfer. I disagree. CSV is horribly underspecified and many parsers have conflicting ideas on how things should work. I've hit areas where, for example, an API was generating a CSV that another API could read, but NOT if it was opened and then re-saved by Excel first (just re-saving, not editing), because Excel was making some trivial change in the format that the consuming parser couldn't handle. Then there's issues with encoding, localisation, and escaping (see, eg, https://chriswarrick.com/blog/2017/04/07/csv-is-not-a-standard/ https://chriswarrick.com/blog/2017/04/07/csv-is-not-a-standa... although that's just scratching the surface). That being said, this proposal seems worse in every way that existing CSVs, but I don't think the issue is "there's a well specified CSV standard with tons of parsers that won't change to support this new one", it's more that there is no standard, all the parsers are incompatible, and no meaningful improvements are possible. :(
  - hodgesrm 8y ago
    To your point it's hard to see how the referenced article qualifies as a standard. It does not deal with escape characters or missing values, two places where CSV implementations tend to vary in random ways.
    - Lazare 8y ago
      My comment may have been unclear, but I agree with this completely.
    - wlesieutre 8y ago
      I've recently been dealing with some CSV data exported from Excel. One of the columns has lengths measured in inches or feet. A column that says 14" exports as , '"14"""', It feels downright silly to me.
      - deathanatos 8y ago
        A cell w/ 14" would be ,"14""", And I verified that all four of Excel's CSV varieties write this, but this is also what RFC CSV writers would emit. Not saying you can't pick up oddities elsewhere, but as stated, it's not that bad. (Not that I condone use of CSV… it's an awful format.)
        wlesieutre 8y ago
        Ah you're right, the spot I was looking at was after having read it into Python and split into a list on the commas, so the outer ' is my string container. Whoops.
  - ken 8y ago
    What we need, I think, is an HTML5-like effort from the industry. That's the one format I can think of which went from "complete mess" to "parses the same everywhere". 1. Look at how everybody is extending CSV in the real world 2. Make a new format which does those things, as closely to all the existing implementations as is reasonable 3. Write a fully-specified new syntax for it, including a complete parser (state machine including error cases) 4. Make a complete set of tests, which make it very obvious when you don't pass, for shaming everyone into compliance 5. Get buy-in from a major standards organization, and all of the major corporate players I don't see any improvements/replacements succeeding if they don't hit all of these points. "CSV 1.1" here hits #1 and #2 only.
- Someone1234 8y ago
  I agree. The biggest problem with CSV is Excel's terrible support. And while CSV is primarily used for data transfer, Excel could hypothetically be a great debug-viewer if it didn't do brain dead stuff like unrecoverably corrupt UPCs and other long numbers by default. There's an Excel Uservoice about this very issue from 2015: https://excel.uservoice.com/forums/304921-excel-for-windows-desktop-application/suggestions/10374741-stop-excel-from-changing-large-numbers-actually?page=8&per_page=20 https://excel.uservoice.com/forums/304921-excel-for-windows-... They're "looking into it." Just as they've been looking into it for the last twenty years. Any. Day. Now.
  - infogulch 8y ago
    Excel's braindead csv corruption is why I never use it for csv now. Add guids to the list of things it can corrupt. (if the first group is numeric it parses as an expression which then causes an error because the next group has hex digits, what!?) Also any long string that happens to only be digits. And two numbers separated by a "/". It's so bad I check any csv file I get if it's been opened in Excel, and if I find any evidence that it has I reject it and make whoever I got it from give me the original source, or transform it with literally any other tool.
  - deleted 8y ago
    [deleted]
  - PakG1 8y ago
    I was surprised by how bad Microsoft's CSV support is in Excel. So much that I use Numbers or text editors for everything CSV, never do I use Excel. Excel is still great for a basic level of intensive data analysis though, so can't get away from it.
  - paulryanrogers 8y ago
    IIRC there is a hack where you put a single quote after the comma and Excel will treat it as a string. Unfortunately it doesn't help exporting to unknown systems which l likely don't do the same.
  - tomc1985 8y ago
    Fix it so now its easier for customers to not use Excel? Heretic!
- geraldbauer 8y ago
  > If you are worried about people in the year 3000 understanding your data, add a .txt file alongside the CSV explaining the fields. Good point. That's what (tabular) data packages are for, see https://github.com/csv11/csvpack https://github.com/csv11/csvpack for some real-world examples.
cntlzw 8y ago
https://xkcd.com/927/ https://xkcd.com/927/
keeguon 8y ago
If it ain't broken don't fix it.
- juststeve 8y ago
  it was already broken, but i dont think this will fix it either
romanovcode 8y ago
I even don't see how it is better. ID,Name,Capital,Area,Tags bc,British Columbia,Victoria,922509,en|western canada vs ########################### # Oh, Canada! 10 provinces and 3 territories # # see en.wikipedia.org/wiki/Provinces_and_territories_of_Canada # # note: key is two-letter canadian postal code # # for regions tags see # en.wikipedia.org/wiki/List_of_regions_of_Canada bc, British Columbia, Victoria, 922 509 km², en|western canada In 1.1 I don't see what is what - what is `bc`? What is `en|western canada`? In vanilla CSV you can clearly see what is what because it shows you on top. Why not just write a CSV formatter?
- Tempest1981 8y ago
  Right, where are the tag names specified? What if a new column is added, and my reader needs to handle both formats? Also, seems like specification-by-example, unless I’m missing something.
  - petepete 8y ago
    To be honest, I'd be quite happy if CSV just had a 'frontmatter' block. Sometimes a bit of background info or description of what the data represents or its source is really useful. Removing column headings would be a killer. Although efforts in the past to add type information are misguided, by that point you may as well just use XML.
edejong 8y ago
Well, if you want to improve tabular data formats: 1. Add a version identifier / content-type on the first line! 2. Create a formal grammar for this CSV format 3. Specify preferred character-encoding 4. Provide some tooling (validation, CSV 1.1 => HTML, CSV => Excel) 5. Add the option to specify column type (string, int, date) 6. Specify ISO-8601 as the preferred date format 7. Allow 'reheading' the columns in the file itself. This is useful in streaming data. 8. Specify the format of the newlines.
- pishpash 8y ago
  Or how about include the parser with the file? That's self specifying.
- westurner 8y ago
  CSVW: CSV on the Web https://w3c.github.io/csvw/ https://w3c.github.io/csvw/ "CSV on the Web: A Primer" http://www.w3.org/TR/tabular-data-primer/ http://www.w3.org/TR/tabular-data-primer/ "Model for Tabular Data and Metadata on the Web" http://www.w3.org/TR/tabular-data-model/ http://www.w3.org/TR/tabular-data-model/ "Generating JSON from Tabular Data on the Web" (csv2json) http://www.w3.org/TR/csv2json/ http://www.w3.org/TR/csv2json/ "Generating RDF from Tabular Data on the Web" (csv2rdf) http://www.w3.org/TR/csv2rdf/ http://www.w3.org/TR/csv2rdf/ ... N. Allow authors to (1) specify how many header rows are metadata and (2) what each row is. For example: 7 metadata header rows: {column label, property URI [path], datatype URI, unit URI, accuracy, precision, significant figures} With URIs, we can merge, join, and concatenate data (when e.g. study control URIs for e.g. single/double/triple blinding/masking indicate that the https://schema.org/Dataset https://schema.org/Dataset meets meta-analysis inclusion criteria). "#LinkedReproducibility"; "#LinkedMetaAnalyses"
- hyperman1 8y ago
  You just invented XML or JSON
enygmata 8y ago
This is not VCS friendly. Resize any column and you have to rewrite 99% of the file, tracking changes becomes hell on earth.
- randyzwitch 8y ago
  Besides VCS unfriendly, the storage space for all the additional space characters provides little utility. For the most part, computers are reading CSV, not humans (though we might scan them).
- jstimpfle 8y ago
  What do you mean by "resize any column"?
  - deathanatos 8y ago
    I presume the parent means changing the data s.t. the max length of strings in a column changes; this would cause the padding after that column to change, so as to keep subsequent columns aligned & looking nice. This doesn't play well w/ VCS diffs, as a bunch of data that isn't changing semantically is now changing syntactically, and a simplistic textual diff reflects now that syntactic change, whereas a human reader would want to see the semantic change. Now… most diff tools also have options to ignore whitespace changes, for cases like this.
ewheeler 8y ago
For those looking for a modern take on improving on the CSV format, I'd recommend Frictionless Data's Datapackage specification[1] which basically consists of a json file of metadata that accompanies a CSV file that describes column types, versions, sources, and how to validate correctness of the CSV's data. This allows for quite a lot of tooling and workflow improvements to CSV files without mucking with the CSV itself Another hack to improve CSV workflows is OCHA's HXL[2] that is used by humanitarian organizations. Basically adding a row of hashtags in addition to column names, which is surprisingly useful considering the ease of adding these to a file. [1] https://frictionlessdata.io/docs/tabular-data-package/ https://frictionlessdata.io/docs/tabular-data-package/ [2] http://hxlstandard.org/ http://hxlstandard.org/
- _pwalsh 8y ago
  One of the authors of the Frictionless Data specifications here. The spec directly relevant to CSV is Table Schema [1], and we’ve also got some nice tools that leverage Table Schema and the family of specifications, such as goodtables [2]. [1] https://frictionlessdata.io/specs/table-schema/ https://frictionlessdata.io/specs/table-schema/ [2] https://github.com/frictionlessdata/goodtables-py https://github.com/frictionlessdata/goodtables-py
  - robochat42 8y ago
    This looks interesting and useful. It might be a feature that wouldn't get used a lot but I've always felt that it would be great to have a 'unit' field to specify the physical units of the values. Have you ever discussed adding this as an optional field to the specification?
    - _pwalsh 8y ago
      We’ve looked into it: https://github.com/frictionlessdata/specs/issues/537 https://github.com/frictionlessdata/specs/issues/537 Feel free to add your use cases to that issue.
- geraldbauer 8y ago
worldofgreed 8y ago
The number parsing is going to be a nightmare with this!
ripperdoc 8y ago
Why not use tab-separated values, you get improved readability and less quoting needed, or am I missing something?
- geraldbauer 8y ago
  See the Awesome CSV page, for the discussion about tabs vs commas - https://github.com/csv11/awesome-csv#tab https://github.com/csv11/awesome-csv#tab Yes, ideally the tab is perfect (no escape rules needed, etc). In practice you cannot tell if you're human if a tab is a space or a space is a tab and than you will get into trouble reading / writing your data etc. Anyways, both are great and needed and use tab2csv or csv2tab to convert or pipe (when using command line tools) :-)
- peapicker 8y ago
  The perfect character is ASCII (& Unicode) 0x1E (30) - "Record Separator" -- except that no modern keyboard can naturally type it, the ones that could are long since gone.
legostormtroopr 8y ago
There are a ton of problems here: * From the documentation "No quotes needed for values ... Use dual quotes ... Use triple quotes" * You haven't solved the problem of describing what the columns are. In fact, its worse because you are encouraging people to put units in the field * Spaces matter! What if the data is literally " Word " vs "Word". This format makes them both the same. * For some reason, the header row is removed. The only thing in a CSV that provides structure is gone. In the first "improved" example, I have no way of knowing what the columns meaning without them. The only good improvement in this is the "#" convention as a comment. If we could get every CSV parser in future to agree (we already can't get them to agree now) to ignore lines that start with "#" then CSV's would be vastly improved.
- asaph 8y ago
  > Spaces matter! What if the data is literally " Word " vs "Word". This format makes them both the same. If leading and/or trailing whitespace are significant, you can quote the value. Problem solved. Having said that, almost always when you see data persisted with leading or trailing whitespace it should have been trimmed away before storage.
- fantispug 8y ago
  Regarding quotes, they're optional (e.g. when you need a comma, leading whitespace, or newline). With arguments by position possible I guess colons have to be quoted too. It's not clear to me that it's possible to convert this to a standard CSV; it can't guess the header and guessing whether a column contains units (consistent units?) is asking for trouble. More pragmatically I can't share these with other people because they're almost like a csv, but incompatible. It's great to try to make CSV more readable (I like the leading whitespace, but it'd be finicky to maintain with simple text editors), but not worth the technical risk of format confusion.
- stephenr 8y ago
  Edit to add: your main points are spot on, IMO, but: Original: I'm not even sure I agree that 'comments' are good, particularly if they're going to be intentionally ignored by GUI s/s apps. If you have meaningful information, put it in the furthest right-column, so it appears in spreadsheet editors on the right of the data. If it's not meaningful to the person who will open the file, why include it?
lifthrasiir 8y ago
I was extremely confused why the website nor the repository does not contain the specification. There is an informal specification littered in a separate repo [1], and the whole organization [2] is dedicated for the documentations and associated Ruby implementations. So well, good luck, but next time try to be more specific. [1] https://github.com/csv11/csv-next https://github.com/csv11/csv-next [2] https://github.com/csv11/ https://github.com/csv11/
- geraldbauer 8y ago
  FYI: The readme (the website) is the specification :-), see https://github.com/csv11/csv11.github.io https://github.com/csv11/csv11.github.io PS: csv-next is NOT an informal specification - these are notes (collection of ideas). PPS: How do I know? I'm the author of the website - I should know ;-).
  - lifthrasiir 8y ago
    Oh, hello! My gut feeling was that csv-next is pointing to the informal specification (and they read like it, but specific to a single problem point). I guess you might not have understood my complaint (my bad), so let me give some examples how the specification should look like. An informal specification looks like this [1]. You should give examples and rules enough to use your format and sufficient to implement most of the things. You should give definitions for keywords that are specific to your specification (but you can omit common definitions). You should give some (but probably not all) ideas where the specification can go wrong: Unicode whitespaces, Byte Order Mark, platform-specific newlines, escape sequences, duplicate keys in the front matter (well, actually there is no provision how to put the front matter at all), numeric overflows, and so on. A formal specification looks like this [2]. In addition to what an informal specification provides, you should give lots of examples and formalized rules (most frequently ABNF [3]) to implement all the things. There should be clear and reasonable error handling policies. The wording of the specification should be clear, unambiguous and preferably standardized (there are specific meanings to "MUST", "SHOULD" etc. [4]). The specification should be honest about its pros and cons. You should be explicit about the flexibility of the format: you should give a list of what can be extended or modified later and what can't. I strongly suggest you to put an informal specification at the least, and to prepare for the eventual development of a formal specification by pondering about missing pieces (it is a daunting task, I know). [1] https://github.com/toml-lang/toml/blob/bb47759841ac368d86eb7a459bd7eea7162b9a80/README.md#user-content-spec https://github.com/toml-lang/toml/blob/bb47759841ac368d86eb7... [2] https://tools.ietf.org/html/rfc7049 https://tools.ietf.org/html/rfc7049 [3] https://en.wikipedia.org/wiki/Augmented_Backus%E2%80%93Naur_form https://en.wikipedia.org/wiki/Augmented_Backus%E2%80%93Naur_... [4] https://tools.ietf.org/html/bcp14 https://tools.ietf.org/html/bcp14
clatan 8y ago
Yeah... No. This doesn't solve anything.
digitalzombie 8y ago
Uh... so the bottom one of the or is CSV 1.1? How do you define headers? There's no header for the bottom csv version so I have to manually type headers for my data frame? I got a dataset with 87 obs and ~8700 columns here. I'm not going to manually name those columns. What's the solution?
- geraldbauer 8y ago
  Yes, you can add a header, of course. No worries. CSV 1.1 is still CSV. It's not CSV 2.0 :-).
icholy 8y ago
Or just open the CSV in a spreadsheet?
gwbas1c 8y ago
What problem is this trying to solve? CSV's problems are the nature of a very flexible convention. It's so simple that everyone writes their own generators and parsers that are slightly different. That's what happens when you use a convention like csv. Revving the spec won't help anything... Because csv is the kind of convention where no one reads the spec anyway!
mvanveen 8y ago
What if you want to start a row with a field that has a `#`? Not to mention how to handle backwards compatibility with the above concern...
- geraldbauer 8y ago
  If a field starts with # you have to escape it, that is, put it into a quote e.g. "# no comment".
jstimpfle 8y ago
Some time ago I've worked on a similar, CSV-like thing that improves on readability and canonical representation, and which can hold multiple tables in a single file or stream like a database: http://jstimpfle.de/projects/wsl/main.html http://jstimpfle.de/projects/wsl/main.html . (I hope it's not rude to reference it here)
- geraldbauer 8y ago
  No worries. Thanks for highlighting WSL - whitespace separated literals. The world needs more of this. Keep it up.
kristofferR 8y ago
CSV is hell, glad to see improvements. Some idiot somewhere decided that Comma Separated Values in certain locales should be based on semicolons (who would have thought files would be shared across country borders!?), so when we open CSV files that are actually comma separated all the information is in the first cell (until a semicolon appears). To get comma separated CSVs to show properly in Excel we have to mess around with OS language settings. CSV as a format should have died years ago, it's a shame so many apps/services only export CSV files. Many developers (mainly US/UK based) are probably not aware of how much of a headache they inflict on people in other countries by using CSV files.
- int_19h 8y ago
  Semicolons come from many countries using the decimal comma in lieu of the decimal period. Of course, locale-specific number formatting should be a big no-no in any kind of a portable format anyway. But...
- walterstucco 8y ago
  Some idiots like... everyone except US and UK? http://mathworld.wolfram.com/DecimalPoint.html http://mathworld.wolfram.com/DecimalPoint.html
int_19h 8y ago
The best thing we have today for "CSV evolved" is JSON array of arrays, IMO. It's much better specified, everything agrees on how things should be quoted, it's perfectly human-readable if you do one array per line, its semantics follows straightforwardly from that of JSON, and we already have parsers for it that produce a reasonable output. Is it slightly more verbose? Sure, but why does it matter? Having to quote all strings is really not a big deal.
- geraldbauer 8y ago
  Good point. See the CSVJSON format - http://csvjson.org http://csvjson.org - Love it. I will add a new variant called CSV <3 JSON shortly to the CSV 1.1 repo and csvreader etc. too.
eksemplar 8y ago
I’m sorry, but when do you ever need to read a CSV file in an environment where you don’t have a spreadsheet editor anyway? I work in the public sector, we use a lot of CVS, even to non tech savvy people. I’ve never heard the use case for this project. Now that might not matter, but you’re breaking CVS to fulfill a nonexistent use case.
kalleboo 8y ago
Reminds me of old IBM punch card fixed-column-aligned tables... Still in use in many systems (such as the US ACH wire system...) Everything old is new again.
cpeterso 8y ago
ASCII defines control codes for record and field separators, thus avoiding CSV's problems with quoting and escaping characters. The only hassle is entering and displaying the control characters. https://en.wikipedia.org/wiki/C0_and_C1_control_codes#Field_separators https://en.wikipedia.org/wiki/C0_and_C1_control_codes#Field_...
webdevetc 8y ago
While it is nice to add some spacing and make things a bit easier to read, I don't really see the point in this. If you want it to be easily parsable by a human then there are hundreds of applications designed for this. The most common being spreadsheets like Excel. And what happens if you have a list of domains, but one contains a really long url (300 characters wide?). It will mess up the columns for every single row above and below it as well. Also adding comments will completely break any existing software (I think most programs can handle some more whitespace, but you couldn't import the final example into Excel, I don't think (untested))
bachmeier 8y ago
You can already do this in R with a fixed-width text file, comments, and the names of variables in a header. The commas serve no actual purpose AFAICT.
walterstucco 8y ago
CSV is unfixable
lozzo 8y ago
Good effort. And I like the minimal documentation. However, and I would love to be proved wrong, I think you are about 20 years late for this project to get any interest aside from some effimerous moments in hackernews...
- geraldbauer 8y ago
  No worries. It's just a write-up for what gets used in football.db, beer.db, world.db, etc. day in, day out. Also remember - as the say it's never too late :-). [1] https://github.com/openfootball https://github.com/openfootball [2] https://github.com/openmundi https://github.com/openmundi [3] https://github.com/openbeer https://github.com/openbeer
CptKriechstrom 8y ago
I don't get the point. If your are on to replace your CSV im-/exporter you can switch to a non-ridiculous format anyway.
billpg 8y ago
[ ["If", "we're", "going", "to", "improve", "CSV"], ["Let's", "do", "it", "with", "some", "JSON"] ]
- geraldbauer 8y ago
  Good point. You're not the first one (even in this thread). To repost: See the CSVJSON format - http://csvjson.org http://csvjson.org - Love it. I will add a new variant called CSV <3 JSON shortly to the CSV 1.1 repo and csvreader etc. too.
pshirali 8y ago
I like the intent "Easy-to-Write, Easy-to-Read", but the contents from the README leave much to be desired. There are far too many corner cases which the README doesn't address (many already mentioned below), and I can think of more, like, how does a parser know "city: Halifax" as "key: Value" and not just value="city: Halifax"? Even if a `spec` is created which clearly defines these cases and standardizes how they should be handled, does it qualify to be called CSV 1.1 (or 2.0? really, as it probably won't play nice with existing CSV 1.0 implementations). It is almost as-good-as creating a new format altogether. And there are many to complete with. I also wonder if it REALLY solves the problems it aims to solve (even if the tech specs were in place). CSV in any form is not human-friendly. This is especially true when you have wide columns (say >30). Even if the records were spaced-out in a human-friendly way in STATE-1, editing records where values are of highly varying width (within a single column) will soon mess the justification when you get to STATE-2. If you skip the requirement for `fixed-number-of-fields` and `key:value` style named values; that messes readability (by humans) even more! I've had good success with `reading` CSVs using the `csvtk` (https://github.com/shenwei356/csvtk https://github.com/shenwei356/csvtk). It provides excellent support for pretty-printing CSVs, filtering select fields etc. `writing` is still a pain, but I still feel spreadsheets are the way to go. They have been around for decades. Its sad if the formatting by specific tools is screwing CSVs, but, solving the composition/modification requirement purely by way of formatted-plain-text is a really tall ask.
peapicker 8y ago
How does this improve on RFC 4180? (I only _wish_ excel and many other parsers behaved predictably like that RFC, but it is a decent proposal. ) ( https://www.ietf.org/rfc/rfc4180.txt https://www.ietf.org/rfc/rfc4180.txt )
- geraldbauer 8y ago
  The [strict] RFC 4180 CSV Format memo is way too simplistic and "specifies" a format for machine-reading and writing. It's not a human csv format. What's human? It's a format that you hand edit and so on - thus, you want comments, blank lines, short cuts etc. Just for the record to quote from the memo: This memo provides information for the internet community. IT DOES NOT SPECIFY AN INTERNET STANDARD OF ANY KIND. IT DOES NOT SPECIFY AN INTERNET STANDARD OF ANY KIND. IT DOES NOT SPECIFY AN INTERNET STANDARD OF ANY KIND. Just repeating it three times in case you missed it, see https://www.ietf.org/rfc/rfc4180.txt https://www.ietf.org/rfc/rfc4180.txt

84 points by polm23 8y ago

I can't see this gaining any traction. There's nothing wrong with CSV as it is for basic data transfer. CSV had been unchanged now for so long, that any attempt to update it will fail - there are simply too many CSV parsers already implemented. If you are worried about people in the year 3000 understanding your data, add a .txt file alongside the CSV explaining the fields.

Lazare 8y ago
> There's nothing wrong with CSV as it is for basic data transfer. I disagree. CSV is horribly underspecified and many parsers have conflicting ideas on how things should work. I've hit areas where, for example, an API was generating a CSV that another API could read, but NOT if it was opened and then re-saved by Excel first (just re-saving, not editing), because Excel was making some trivial change in the format that the consuming parser couldn't handle. Then there's issues with encoding, localisation, and escaping (see, eg, https://chriswarrick.com/blog/2017/04/07/csv-is-not-a-standard/ https://chriswarrick.com/blog/2017/04/07/csv-is-not-a-standa... although that's just scratching the surface). That being said, this proposal seems worse in every way that existing CSVs, but I don't think the issue is "there's a well specified CSV standard with tons of parsers that won't change to support this new one", it's more that there is no standard, all the parsers are incompatible, and no meaningful improvements are possible. :(
- hodgesrm 8y ago
  To your point it's hard to see how the referenced article qualifies as a standard. It does not deal with escape characters or missing values, two places where CSV implementations tend to vary in random ways.
  - Lazare 8y ago
    My comment may have been unclear, but I agree with this completely.
  - wlesieutre 8y ago
    I've recently been dealing with some CSV data exported from Excel. One of the columns has lengths measured in inches or feet. A column that says 14" exports as , '"14"""', It feels downright silly to me.
    - deathanatos 8y ago
      A cell w/ 14" would be ,"14""", And I verified that all four of Excel's CSV varieties write this, but this is also what RFC CSV writers would emit. Not saying you can't pick up oddities elsewhere, but as stated, it's not that bad. (Not that I condone use of CSV… it's an awful format.)
      - wlesieutre 8y ago
        Ah you're right, the spot I was looking at was after having read it into Python and split into a list on the commas, so the outer ' is my string container. Whoops.
- ken 8y ago
  What we need, I think, is an HTML5-like effort from the industry. That's the one format I can think of which went from "complete mess" to "parses the same everywhere". 1. Look at how everybody is extending CSV in the real world 2. Make a new format which does those things, as closely to all the existing implementations as is reasonable 3. Write a fully-specified new syntax for it, including a complete parser (state machine including error cases) 4. Make a complete set of tests, which make it very obvious when you don't pass, for shaming everyone into compliance 5. Get buy-in from a major standards organization, and all of the major corporate players I don't see any improvements/replacements succeeding if they don't hit all of these points. "CSV 1.1" here hits #1 and #2 only.
Someone1234 8y ago
I agree. The biggest problem with CSV is Excel's terrible support. And while CSV is primarily used for data transfer, Excel could hypothetically be a great debug-viewer if it didn't do brain dead stuff like unrecoverably corrupt UPCs and other long numbers by default. There's an Excel Uservoice about this very issue from 2015: https://excel.uservoice.com/forums/304921-excel-for-windows-desktop-application/suggestions/10374741-stop-excel-from-changing-large-numbers-actually?page=8&per_page=20 https://excel.uservoice.com/forums/304921-excel-for-windows-... They're "looking into it." Just as they've been looking into it for the last twenty years. Any. Day. Now.
- infogulch 8y ago
  Excel's braindead csv corruption is why I never use it for csv now. Add guids to the list of things it can corrupt. (if the first group is numeric it parses as an expression which then causes an error because the next group has hex digits, what!?) Also any long string that happens to only be digits. And two numbers separated by a "/". It's so bad I check any csv file I get if it's been opened in Excel, and if I find any evidence that it has I reject it and make whoever I got it from give me the original source, or transform it with literally any other tool.
- deleted 8y ago
  [deleted]
- PakG1 8y ago
  I was surprised by how bad Microsoft's CSV support is in Excel. So much that I use Numbers or text editors for everything CSV, never do I use Excel. Excel is still great for a basic level of intensive data analysis though, so can't get away from it.
- paulryanrogers 8y ago
  IIRC there is a hack where you put a single quote after the comma and Excel will treat it as a string. Unfortunately it doesn't help exporting to unknown systems which l likely don't do the same.
- tomc1985 8y ago
  Fix it so now its easier for customers to not use Excel? Heretic!
geraldbauer 8y ago
> If you are worried about people in the year 3000 understanding your data, add a .txt file alongside the CSV explaining the fields. Good point. That's what (tabular) data packages are for, see https://github.com/csv11/csvpack https://github.com/csv11/csvpack for some real-world examples.

cntlzw 8y ago

https://xkcd.com/927/ https://xkcd.com/927/

keeguon 8y ago

If it ain't broken don't fix it.

juststeve 8y ago
it was already broken, but i dont think this will fix it either

romanovcode 8y ago

I even don't see how it is better. ID,Name,Capital,Area,Tags bc,British Columbia,Victoria,922509,en|western canada vs ########################### # Oh, Canada! 10 provinces and 3 territories # # see en.wikipedia.org/wiki/Provinces_and_territories_of_Canada # # note: key is two-letter canadian postal code # # for regions tags see # en.wikipedia.org/wiki/List_of_regions_of_Canada bc, British Columbia, Victoria, 922 509 km², en|western canada In 1.1 I don't see what is what - what is `bc`? What is `en|western canada`? In vanilla CSV you can clearly see what is what because it shows you on top. Why not just write a CSV formatter?

Tempest1981 8y ago
Right, where are the tag names specified? What if a new column is added, and my reader needs to handle both formats? Also, seems like specification-by-example, unless I’m missing something.
- petepete 8y ago
  To be honest, I'd be quite happy if CSV just had a 'frontmatter' block. Sometimes a bit of background info or description of what the data represents or its source is really useful. Removing column headings would be a killer. Although efforts in the past to add type information are misguided, by that point you may as well just use XML.

edejong 8y ago

Well, if you want to improve tabular data formats: 1. Add a version identifier / content-type on the first line! 2. Create a formal grammar for this CSV format 3. Specify preferred character-encoding 4. Provide some tooling (validation, CSV 1.1 => HTML, CSV => Excel) 5. Add the option to specify column type (string, int, date) 6. Specify ISO-8601 as the preferred date format 7. Allow 'reheading' the columns in the file itself. This is useful in streaming data. 8. Specify the format of the newlines.

pishpash 8y ago
Or how about include the parser with the file? That's self specifying.
westurner 8y ago
CSVW: CSV on the Web https://w3c.github.io/csvw/ https://w3c.github.io/csvw/ "CSV on the Web: A Primer" http://www.w3.org/TR/tabular-data-primer/ http://www.w3.org/TR/tabular-data-primer/ "Model for Tabular Data and Metadata on the Web" http://www.w3.org/TR/tabular-data-model/ http://www.w3.org/TR/tabular-data-model/ "Generating JSON from Tabular Data on the Web" (csv2json) http://www.w3.org/TR/csv2json/ http://www.w3.org/TR/csv2json/ "Generating RDF from Tabular Data on the Web" (csv2rdf) http://www.w3.org/TR/csv2rdf/ http://www.w3.org/TR/csv2rdf/ ... N. Allow authors to (1) specify how many header rows are metadata and (2) what each row is. For example: 7 metadata header rows: {column label, property URI [path], datatype URI, unit URI, accuracy, precision, significant figures} With URIs, we can merge, join, and concatenate data (when e.g. study control URIs for e.g. single/double/triple blinding/masking indicate that the https://schema.org/Dataset https://schema.org/Dataset meets meta-analysis inclusion criteria). "#LinkedReproducibility"; "#LinkedMetaAnalyses"
hyperman1 8y ago
You just invented XML or JSON

enygmata 8y ago

This is not VCS friendly. Resize any column and you have to rewrite 99% of the file, tracking changes becomes hell on earth.

randyzwitch 8y ago
Besides VCS unfriendly, the storage space for all the additional space characters provides little utility. For the most part, computers are reading CSV, not humans (though we might scan them).
jstimpfle 8y ago
What do you mean by "resize any column"?
- deathanatos 8y ago
  I presume the parent means changing the data s.t. the max length of strings in a column changes; this would cause the padding after that column to change, so as to keep subsequent columns aligned & looking nice. This doesn't play well w/ VCS diffs, as a bunch of data that isn't changing semantically is now changing syntactically, and a simplistic textual diff reflects now that syntactic change, whereas a human reader would want to see the semantic change. Now… most diff tools also have options to ignore whitespace changes, for cases like this.

ewheeler 8y ago

For those looking for a modern take on improving on the CSV format, I'd recommend Frictionless Data's Datapackage specification[1] which basically consists of a json file of metadata that accompanies a CSV file that describes column types, versions, sources, and how to validate correctness of the CSV's data. This allows for quite a lot of tooling and workflow improvements to CSV files without mucking with the CSV itself Another hack to improve CSV workflows is OCHA's HXL[2] that is used by humanitarian organizations. Basically adding a row of hashtags in addition to column names, which is surprisingly useful considering the ease of adding these to a file. [1] https://frictionlessdata.io/docs/tabular-data-package/ https://frictionlessdata.io/docs/tabular-data-package/ [2] http://hxlstandard.org/ http://hxlstandard.org/

_pwalsh 8y ago
One of the authors of the Frictionless Data specifications here. The spec directly relevant to CSV is Table Schema [1], and we’ve also got some nice tools that leverage Table Schema and the family of specifications, such as goodtables [2]. [1] https://frictionlessdata.io/specs/table-schema/ https://frictionlessdata.io/specs/table-schema/ [2] https://github.com/frictionlessdata/goodtables-py https://github.com/frictionlessdata/goodtables-py
- robochat42 8y ago
  This looks interesting and useful. It might be a feature that wouldn't get used a lot but I've always felt that it would be great to have a 'unit' field to specify the physical units of the values. Have you ever discussed adding this as an optional field to the specification?
  - _pwalsh 8y ago
    We’ve looked into it: https://github.com/frictionlessdata/specs/issues/537 https://github.com/frictionlessdata/specs/issues/537 Feel free to add your use cases to that issue.
geraldbauer 8y ago

worldofgreed 8y ago

The number parsing is going to be a nightmare with this!

ripperdoc 8y ago

Why not use tab-separated values, you get improved readability and less quoting needed, or am I missing something?

geraldbauer 8y ago
See the Awesome CSV page, for the discussion about tabs vs commas - https://github.com/csv11/awesome-csv#tab https://github.com/csv11/awesome-csv#tab Yes, ideally the tab is perfect (no escape rules needed, etc). In practice you cannot tell if you're human if a tab is a space or a space is a tab and than you will get into trouble reading / writing your data etc. Anyways, both are great and needed and use tab2csv or csv2tab to convert or pipe (when using command line tools) :-)
peapicker 8y ago
The perfect character is ASCII (& Unicode) 0x1E (30) - "Record Separator" -- except that no modern keyboard can naturally type it, the ones that could are long since gone.

legostormtroopr 8y ago

There are a ton of problems here: * From the documentation "No quotes needed for values ... Use dual quotes ... Use triple quotes" * You haven't solved the problem of describing what the columns are. In fact, its worse because you are encouraging people to put units in the field * Spaces matter! What if the data is literally " Word " vs "Word". This format makes them both the same. * For some reason, the header row is removed. The only thing in a CSV that provides structure is gone. In the first "improved" example, I have no way of knowing what the columns meaning without them. The only good improvement in this is the "#" convention as a comment. If we could get every CSV parser in future to agree (we already can't get them to agree now) to ignore lines that start with "#" then CSV's would be vastly improved.

asaph 8y ago
> Spaces matter! What if the data is literally " Word " vs "Word". This format makes them both the same. If leading and/or trailing whitespace are significant, you can quote the value. Problem solved. Having said that, almost always when you see data persisted with leading or trailing whitespace it should have been trimmed away before storage.
fantispug 8y ago
Regarding quotes, they're optional (e.g. when you need a comma, leading whitespace, or newline). With arguments by position possible I guess colons have to be quoted too. It's not clear to me that it's possible to convert this to a standard CSV; it can't guess the header and guessing whether a column contains units (consistent units?) is asking for trouble. More pragmatically I can't share these with other people because they're almost like a csv, but incompatible. It's great to try to make CSV more readable (I like the leading whitespace, but it'd be finicky to maintain with simple text editors), but not worth the technical risk of format confusion.
stephenr 8y ago
Edit to add: your main points are spot on, IMO, but: Original: I'm not even sure I agree that 'comments' are good, particularly if they're going to be intentionally ignored by GUI s/s apps. If you have meaningful information, put it in the furthest right-column, so it appears in spreadsheet editors on the right of the data. If it's not meaningful to the person who will open the file, why include it?

lifthrasiir 8y ago

I was extremely confused why the website nor the repository does not contain the specification. There is an informal specification littered in a separate repo [1], and the whole organization [2] is dedicated for the documentations and associated Ruby implementations. So well, good luck, but next time try to be more specific. [1] https://github.com/csv11/csv-next https://github.com/csv11/csv-next [2] https://github.com/csv11/ https://github.com/csv11/

geraldbauer 8y ago
FYI: The readme (the website) is the specification :-), see https://github.com/csv11/csv11.github.io https://github.com/csv11/csv11.github.io PS: csv-next is NOT an informal specification - these are notes (collection of ideas). PPS: How do I know? I'm the author of the website - I should know ;-).
- lifthrasiir 8y ago
  Oh, hello! My gut feeling was that csv-next is pointing to the informal specification (and they read like it, but specific to a single problem point). I guess you might not have understood my complaint (my bad), so let me give some examples how the specification should look like. An informal specification looks like this [1]. You should give examples and rules enough to use your format and sufficient to implement most of the things. You should give definitions for keywords that are specific to your specification (but you can omit common definitions). You should give some (but probably not all) ideas where the specification can go wrong: Unicode whitespaces, Byte Order Mark, platform-specific newlines, escape sequences, duplicate keys in the front matter (well, actually there is no provision how to put the front matter at all), numeric overflows, and so on. A formal specification looks like this [2]. In addition to what an informal specification provides, you should give lots of examples and formalized rules (most frequently ABNF [3]) to implement all the things. There should be clear and reasonable error handling policies. The wording of the specification should be clear, unambiguous and preferably standardized (there are specific meanings to "MUST", "SHOULD" etc. [4]). The specification should be honest about its pros and cons. You should be explicit about the flexibility of the format: you should give a list of what can be extended or modified later and what can't. I strongly suggest you to put an informal specification at the least, and to prepare for the eventual development of a formal specification by pondering about missing pieces (it is a daunting task, I know). [1] https://github.com/toml-lang/toml/blob/bb47759841ac368d86eb7a459bd7eea7162b9a80/README.md#user-content-spec https://github.com/toml-lang/toml/blob/bb47759841ac368d86eb7... [2] https://tools.ietf.org/html/rfc7049 https://tools.ietf.org/html/rfc7049 [3] https://en.wikipedia.org/wiki/Augmented_Backus%E2%80%93Naur_form https://en.wikipedia.org/wiki/Augmented_Backus%E2%80%93Naur_... [4] https://tools.ietf.org/html/bcp14 https://tools.ietf.org/html/bcp14

clatan 8y ago

Yeah... No. This doesn't solve anything.

digitalzombie 8y ago

Uh... so the bottom one of the or is CSV 1.1? How do you define headers? There's no header for the bottom csv version so I have to manually type headers for my data frame? I got a dataset with 87 obs and ~8700 columns here. I'm not going to manually name those columns. What's the solution?

geraldbauer 8y ago
Yes, you can add a header, of course. No worries. CSV 1.1 is still CSV. It's not CSV 2.0 :-).

icholy 8y ago

Or just open the CSV in a spreadsheet?

gwbas1c 8y ago

What problem is this trying to solve? CSV's problems are the nature of a very flexible convention. It's so simple that everyone writes their own generators and parsers that are slightly different. That's what happens when you use a convention like csv. Revving the spec won't help anything... Because csv is the kind of convention where no one reads the spec anyway!

mvanveen 8y ago

What if you want to start a row with a field that has a `#`? Not to mention how to handle backwards compatibility with the above concern...

geraldbauer 8y ago
If a field starts with # you have to escape it, that is, put it into a quote e.g. "# no comment".

jstimpfle 8y ago

Some time ago I've worked on a similar, CSV-like thing that improves on readability and canonical representation, and which can hold multiple tables in a single file or stream like a database: http://jstimpfle.de/projects/wsl/main.html http://jstimpfle.de/projects/wsl/main.html . (I hope it's not rude to reference it here)

geraldbauer 8y ago
No worries. Thanks for highlighting WSL - whitespace separated literals. The world needs more of this. Keep it up.

kristofferR 8y ago

CSV is hell, glad to see improvements. Some idiot somewhere decided that Comma Separated Values in certain locales should be based on semicolons (who would have thought files would be shared across country borders!?), so when we open CSV files that are actually comma separated all the information is in the first cell (until a semicolon appears). To get comma separated CSVs to show properly in Excel we have to mess around with OS language settings. CSV as a format should have died years ago, it's a shame so many apps/services only export CSV files. Many developers (mainly US/UK based) are probably not aware of how much of a headache they inflict on people in other countries by using CSV files.

int_19h 8y ago
Semicolons come from many countries using the decimal comma in lieu of the decimal period. Of course, locale-specific number formatting should be a big no-no in any kind of a portable format anyway. But...
walterstucco 8y ago
Some idiots like... everyone except US and UK? http://mathworld.wolfram.com/DecimalPoint.html http://mathworld.wolfram.com/DecimalPoint.html

int_19h 8y ago

The best thing we have today for "CSV evolved" is JSON array of arrays, IMO. It's much better specified, everything agrees on how things should be quoted, it's perfectly human-readable if you do one array per line, its semantics follows straightforwardly from that of JSON, and we already have parsers for it that produce a reasonable output. Is it slightly more verbose? Sure, but why does it matter? Having to quote all strings is really not a big deal.

geraldbauer 8y ago
Good point. See the CSVJSON format - http://csvjson.org http://csvjson.org - Love it. I will add a new variant called CSV <3 JSON shortly to the CSV 1.1 repo and csvreader etc. too.

eksemplar 8y ago

I’m sorry, but when do you ever need to read a CSV file in an environment where you don’t have a spreadsheet editor anyway? I work in the public sector, we use a lot of CVS, even to non tech savvy people. I’ve never heard the use case for this project. Now that might not matter, but you’re breaking CVS to fulfill a nonexistent use case.

kalleboo 8y ago

Reminds me of old IBM punch card fixed-column-aligned tables... Still in use in many systems (such as the US ACH wire system...) Everything old is new again.

cpeterso 8y ago

ASCII defines control codes for record and field separators, thus avoiding CSV's problems with quoting and escaping characters. The only hassle is entering and displaying the control characters. https://en.wikipedia.org/wiki/C0_and_C1_control_codes#Field_separators https://en.wikipedia.org/wiki/C0_and_C1_control_codes#Field_...

webdevetc 8y ago

While it is nice to add some spacing and make things a bit easier to read, I don't really see the point in this. If you want it to be easily parsable by a human then there are hundreds of applications designed for this. The most common being spreadsheets like Excel. And what happens if you have a list of domains, but one contains a really long url (300 characters wide?). It will mess up the columns for every single row above and below it as well. Also adding comments will completely break any existing software (I think most programs can handle some more whitespace, but you couldn't import the final example into Excel, I don't think (untested))

bachmeier 8y ago

You can already do this in R with a fixed-width text file, comments, and the names of variables in a header. The commas serve no actual purpose AFAICT.

walterstucco 8y ago

CSV is unfixable

lozzo 8y ago

Good effort. And I like the minimal documentation. However, and I would love to be proved wrong, I think you are about 20 years late for this project to get any interest aside from some effimerous moments in hackernews...

geraldbauer 8y ago
No worries. It's just a write-up for what gets used in football.db, beer.db, world.db, etc. day in, day out. Also remember - as the say it's never too late :-). [1] https://github.com/openfootball https://github.com/openfootball [2] https://github.com/openmundi https://github.com/openmundi [3] https://github.com/openbeer https://github.com/openbeer

CptKriechstrom 8y ago

I don't get the point. If your are on to replace your CSV im-/exporter you can switch to a non-ridiculous format anyway.

billpg 8y ago

[ ["If", "we're", "going", "to", "improve", "CSV"], ["Let's", "do", "it", "with", "some", "JSON"] ]

geraldbauer 8y ago
Good point. You're not the first one (even in this thread). To repost: See the CSVJSON format - http://csvjson.org http://csvjson.org - Love it. I will add a new variant called CSV <3 JSON shortly to the CSV 1.1 repo and csvreader etc. too.

pshirali 8y ago

I like the intent "Easy-to-Write, Easy-to-Read", but the contents from the README leave much to be desired. There are far too many corner cases which the README doesn't address (many already mentioned below), and I can think of more, like, how does a parser know "city: Halifax" as "key: Value" and not just value="city: Halifax"? Even if a `spec` is created which clearly defines these cases and standardizes how they should be handled, does it qualify to be called CSV 1.1 (or 2.0? really, as it probably won't play nice with existing CSV 1.0 implementations). It is almost as-good-as creating a new format altogether. And there are many to complete with. I also wonder if it REALLY solves the problems it aims to solve (even if the tech specs were in place). CSV in any form is not human-friendly. This is especially true when you have wide columns (say >30). Even if the records were spaced-out in a human-friendly way in STATE-1, editing records where values are of highly varying width (within a single column) will soon mess the justification when you get to STATE-2. If you skip the requirement for `fixed-number-of-fields` and `key:value` style named values; that messes readability (by humans) even more! I've had good success with `reading` CSVs using the `csvtk` (https://github.com/shenwei356/csvtk https://github.com/shenwei356/csvtk). It provides excellent support for pretty-printing CSVs, filtering select fields etc. `writing` is still a pain, but I still feel spreadsheets are the way to go. They have been around for decades. Its sad if the formatting by specific tools is screwing CSVs, but, solving the composition/modification requirement purely by way of formatted-plain-text is a really tall ask.

peapicker 8y ago

How does this improve on RFC 4180? (I only _wish_ excel and many other parsers behaved predictably like that RFC, but it is a decent proposal. ) ( https://www.ietf.org/rfc/rfc4180.txt https://www.ietf.org/rfc/rfc4180.txt )

geraldbauer 8y ago
The [strict] RFC 4180 CSV Format memo is way too simplistic and "specifies" a format for machine-reading and writing. It's not a human csv format. What's human? It's a format that you hand edit and so on - thus, you want comments, blank lines, short cuts etc. Just for the record to quote from the memo: This memo provides information for the internet community. IT DOES NOT SPECIFY AN INTERNET STANDARD OF ANY KIND. IT DOES NOT SPECIFY AN INTERNET STANDARD OF ANY KIND. IT DOES NOT SPECIFY AN INTERNET STANDARD OF ANY KIND. Just repeating it three times in case you missed it, see https://www.ietf.org/rfc/rfc4180.txt https://www.ietf.org/rfc/rfc4180.txt