25 ms·

I don't agree with giving up csvs until the following conditions are met: 1) A truly open format is available and accessible. Csvs are textfiles. There is no s

I don't agree with giving up csvs until the following conditions are met: 1) A truly open format is available and accessible. Csvs are textfiles. There is no system around that cannot open a textfile. If the format is binary or requires patents or whatever, then it's a non-starter. 2) Applications have a speed increase from using csvs. To wit, I loved csvs because often they finish preparing much faster than a "formatted" output in excel, etc. and sometimes I just want to see the numbers, not have everything colored or weird features like merged cells in excel, etc. 3) The new format should not be grossly larger than the one it is replacing. Extracts in Excel format are orders of a magnitude larger than csv in filesize. This affects run-time to prepare the extract, open it (memory constraints, etc.) Is there truly a problem? The author is not forced to use csvs.

Twisell 5y ago
OP and you gave me an idea : "The only true successor of CSV should be forward/backward compatible with any existing CSV variant" If we manage to write a spec that meet this criteria we'll have a powerful standard with easy adoption.
- masklinn 5y ago
  Given the infinity of CSV variants existing is the very reason why CSV blows goats, creating a spec compatible with that is 1. a fool's errand, CSV "variants" are not compatible with one another and regularly contradict one another (one needs not look any further than Excel's localised CSVs) 2. resulting in getting CSV anyway, which is a lot of efforts to do nothing
- ivanbakel 5y ago
  That's ignoring two big points in the OP: that CSV variants are ill-defined and conflicting; and that existing CSV data is too poorly-specified. There is no reason to try to be "backwards-compatible" with existing CSV files - we don't have a single definition of correctness to use to check that the compatibility is correct. Every attempt to be parse existing data would result in unexpected results or even data loss for some CSVs in the wild, because there is no way to reconcile all the different expectations and specifications that people have for their own CSV data.
- Macha 5y ago
  The issue is CSV is not compatible with CSV. It's not possible to write a spec that covers all CSV files in the world. CSV means things that are mutually incompatible in the less common cases, and the only way to really parse them correctly is to know which variant generated it. But you can't even tag that variant in the file by your criteria, as existing CSV parsers won't understand it.
  - Twisell 5y ago
    By this argument UTF8 can't exist. And yet here it is. PS: I never said 100% forward/backward compatible with all variant at the same time and without any noticeable artifact. I meant compatible in a non blocking way.
    - masklinn 5y ago
      What are you talking about? UTF8 is a single well-defined specification, and detecting that data is definitely not UTF8 is trivial.
      - deleted 5y ago
        [deleted]
      - Twisell 5y ago
        And yet it is forward/backward compatible with ASCII and non blocking against all it's ill defined variants.
        Macha 5y ago
        ASCII was well defined, CSV was not. Therefore they could take the highest bit, which they could know that was unused per the ASCII spec, and use that to encode their extra UTF-8 information. Also UTF-8/ascii compatibility is unidirectional. A tool that understands ASCII is going to print nonsense when it encounters emoji or whatever in UTF-8. Even the idea that tools that only understand ASCII won't mangle UTF-8 is limited - sure dumb passthroughs are fine, but if it manipulates the text at all, then you're out of luck - what does it mean to uppercase the first byte of a flag emoji?
- wernercd 5y ago
  obligatory: https://xkcd.com/927/ https://xkcd.com/927/
  - sicariusnoctis 5y ago
    This is true. The best way would be if everyone generated CSV files that were compatible with on one or two already existing CSV formats. (e.g. no-quote-plain-old-csv or quotes-forced-csv.) Hopefully, it would be easy to distinguish between the two in some well-defined manner. Maybe call it .csvs (CSV standardized). Or just introduce new extensions like .csvq ("CSV quoted"). Note the difference is that I am suggesting reducing the number of standards in-use by using only one already existing CSV format. :)
- dragonwriter 5y ago
  > If we manage to write a spec that meet this criteria we'll have a powerful standard with easy adoption. So, a binary format consisting of: (1) a text data segment (2) and end of file character (3) a second text data segment with structured metadata describing the layout of the first text data segment, which can be as simple (in terms of meaning; the structure should be more constrained for machine readability) as “It’s some kind of CSV, yo!” to a description of specific CSV variations (headers? column data types? escaping mechanisms? etc.) or even specify that the main body is JSON, YAML, XML, etc. (which would probably often be detectable by inspection, but this removes any ambiguity).
  - Twisell 5y ago
    You got my vibe Almost any current CSV parser, even the bad ones, tolerate a header line. So it should be possible to define a compact and standardized syntax that is appended before the real header of the first cell (separator,encoding,decimal separator (often disregarded by most parsers but crucial outside USA),quote character,escape character,etc...). Following headers would just use special notation to inform on (data-type,length,comment). Newest parsers would use theses clues, older ones would just append some manageable junk to headers.
    - Macha 5y ago
      So someone opens this CSV in Excel and there's garbage in A1? Does this really count as compatible? You will get user bugs for this.
      - dragonwriter 5y ago
        > So someone opens this CSV in Excel and there's garbage in A1? Yeah, that's why I chose the “thing that looks like a text file—including optionally CSV—but has additional metadata after the EOF mark” approach instead of stuffing additional metadata in the CSV; there's no way to guarantee that existing implementations will safely ignore any added metadata the main CSV body. (My mechanism has some risk in that there are probably CSV readers that treat the file as a binary byte stream and use the file size rather than a text stream that ends at EOF, but I expect its far fewer than will do the wrong thing with additional metadata before the first header.
derefr 5y ago
You're comparing CSVs to other spreadsheet document formats. But a CSV is not a spreadsheet. A CSV is raw data. (It's data that is restricted to a shape that enables it to be easily imported into a spreadsheet—but data nevertheless.) As such, it should be compared to other data formats—e.g. YAML, JSON Lines, etc. These other data formats all win on your #2 against CSV, as CSV is actually horrible at parse-time vs. other data formats — the fact that both of its separators (newlines and commas) can appear as-is inside column values, with a different meaning, if those column-values are quoted, means that there's no way to parallelize CSV processing, because there's no way to read-ahead and "chunk" a CSV purely lexically. You actually need to fully parse it (serially), and only then will you know where the row boundaries are. If you've ever dealt with trying to write ETL logic for datasets that exist as multi-GB CSV files, vs. as multi-GB any-other-data-format files, you'll have experienced the pain. > The new format should not be grossly larger than the one it is replacing. Self-describing formats like JSON Lines are big... but when you compress them, they go back to being small. General-purpose compressors like deflate/LZMA/etc. are very good at shearing away the duplication of self-describing rows. As such, IMHO, the ideal format to replace ".csv" is ".jsonl.gz" (or, more conveniently, just ".jsonl" but with the expectation that backends will offer Transport-Encoding and your computer will use filesystem compression to store it — with this being almost the perfect use-case for both features.) ----- There's also Avro, which fails your point #1 (it's a binary format) but that binary format is a lossless alternate encoding of what's canonically a JSON document, and there are both simple CLI tools / and small, free, high-quality libraries that can map back and forth between the "raw" JSON document and the Avro-encoded file. At any time, you can decode the Avro-encoded file to text, to examine/modify it in a text editor. The data-warehouse ecosystem already standardized on Avro as its data interchange format. And spreadsheets are just tiny data warehouses. So why not? ;)
- osigurdson 5y ago
  Take an Excel file and change the extension to .zip, then extract the contents. You will see that it is a collection of XML files. Therefore it should be reasonable to conclude that this approach can work for Excel sized datasets. However it is not particularly readable/diff-able if this is part of your use case.
dragontamer 5y ago
> A truly open format is available and accessible. Sqlite? > Applications have a speed increase from using csvs. Sqlite? > The new format should not be grossly larger than the one it is replacing Sqlite it is. -------- Oh, you mean something that Excel can open? Oh yeah, I guess CSV then. But lets not pretend #1 (openness), #2 (speed), and #3 (size) are the issues.
- ARandomerDude 5y ago
  I'd add the constraint that it needs to be understandable by most people. A typical non-tech office worker can use CSV without any trouble. Sqlite has an extremely steep learning curve for someone unfamiliar with databases.
  - salamandersauce 5y ago
    Exactly. Worst case a CSV can be edited and viewed or even created in MS Notepad or any other text editor which is pretty much guaranteed to be on a system. Something to deal with Sqlite? No such luck and the barrier is much higher.
    - masklinn 5y ago
      > Worst case a CSV can be edited and viewed or even created in MS Notepad or any other text editor which is pretty much guaranteed to be on a system. Good luck getting that garbage to be ingestible by anything. Odds are the system you're trying you shove it in will start by choking on the UTF8 BOM notepad insists on, then it will choke on the record separators, then on the incorrect escaping (or quoting). > Something to deal with Sqlite? No such luck and the barrier is much higher. SQLite is already present in most if not all environments (usually multiple times too, you have at least one instance of sqlite per browser on your machine, and probably a system one as well, and a few others), so it's really at most a matter of providing SQLite clients by default.
      - sigzero 5y ago
        SQLite is not the answer. At all.
cls59 5y ago
To point 1, I'd argue that a SQLite database is a great next step beyond CSV despite being a binary format.
- lmilcin 5y ago
  SQLite also requires external dependency and special execution environment and are not as portable as CSV.
  - srcreigh 5y ago
    SQLite is included in most operating systems by default. It's also on macOS and iOS. It also runs on all kinds of embedded devices in addition to personal computers and servers. https://en.wikipedia.org/wiki/SQLite#Operating_systems https://en.wikipedia.org/wiki/SQLite#Operating_systems
    - deleted 5y ago
      [deleted]
    - lmilcin 5y ago
      Just because most popular operating systems have libraries, doesn't yet mean it is portable. Can I click on it and open it in Excel? If not then it is not portable for me and for a lot of other people. The main reason I use CSV is to produce reports that I can either open myself or send to other people so that they can click on it and open themselves and immediately start hacking away. Excel is still corporate lingua franca and until this changes CSV is not going to retire. CSV means for me that I can send it to anybody and everybody will know how to use it. Try sending SQLite binary to people you work with, to your accountant, to your wife. Then share your experience.
      - wombatpm 5y ago
        Try using tabs. Still open in excel with less problems
cbsmith 5y ago
1. open(2) doesn't care about the file format 2. Yes, mixing formatting with data slows down data processing, don't do it. 3. Excel is not the replacement for CSV, and CSV is not a compact format. I mean, maybe if you are used to XML it is, but otherwise, just no. Yes, there is truly a problem.
- da_chicken 5y ago
  > CSV is not a compact format That's only true if you're trying to send all your data in a single, monolithic CSV. If you're sending multiple CSVs, you're capable of representing data as well as a relational data store. Which is to say, you're representing your data using a system of data normalization specifically designed to minimalize data duplication. A single CSV represents a single table, and in most cases with intelligent delimiter selection you can represent an entire data set with no more than one character spent between fields or records. Yes, you do have situations where you're storing losing data density due to using plain text strings, but that's not a limitation particularly unique to CSV for data serialization formats. Additionally, it is a problem that can largely be mitigated by simple text compression. Furthermore, once you switch to a non-text representation, you're limiting yourself to whatever that data representation is. It's easy to represent an arbitrary precision decimal number in plain text. It's hard to find a binary representation that universally represents the same data regardless of the system on the other end. Again, that's not a problem unique to CSVs. If you're working with an API, object by object, then JSON is certainly going to be better, yes, because you can use the application's object representation. If you're working with bulk data of many disparate, unrelated, complex objects, however, or where you're transferring and entire system, you're not going to do much better than CSV.
  - cbsmith 5y ago
    > That's only true if you're trying to send all your data in a single, monolithic CSV. No, that's true in general. There are exceptional cases where it might not be true, but it's true in general. > Yes, you do have situations where you're storing losing data density due to using plain text strings, but that's not a limitation particularly unique to CSV for data serialization formats. That there are other inefficient data serialization formats does not mean that it is not an inefficient data serialization format. > Additionally, it is a problem that can largely be mitigated by simple text compression. "Simple text compression"? Not sure what you are referring to there. If you are speaking of generalized compression, there's a bunch of information theory pointing out the problem with that thinking. > Furthermore, once you switch to a non-text representation, you're limiting yourself to whatever that data representation is. It's easy to represent an arbitrary precision decimal number in plain text. It's hard to find a binary representation that universally represents the same data regardless of the system on the other end. Again, that's not a problem unique to CSVs. It's pretty easy to represent arbitrary precision decimal numbers in non-text formats and they mean the same thing regardless of the system on the other end, but let's say you were right. Great, now represent arbitrary precision binary numbers (you know, the kind that computers work with), in text formats. > If you're working with an API, object by object, then JSON is certainly going to be better, Let me stop you right there. It is not "certainly going to be better". JSON is generally worse. > If you're working with bulk data of many disparate, unrelated, complex objects, however, or where you're transferring and entire system, you're not going to do much better than CSV. There's a whole world out there of formats that are better than CSV at this job, and in many cases they are A LOT better than CSV. Even Avro, for all its pitfalls, is MUCH better.
dragonwriter 5y ago
> The author is not forced to use csvs. The author appears to be a consultant selling data prep/transformation services. As long as the market is using CSVs, he’s forced to use CSVs, at least as end-of-pipeline inputs and outputs. Of course, “people optimize their workflows for something other than making my job easy” is a common, but also rarely persuasive in motivating action from others with different jobs, complaint.
- dylan604 5y ago
  The consultant should be less concerned about what makes their life/job easier, but what makes their client's job easier by working with the consultant. If the client has to jump through hoops to serve consultant1 data in a format they are not used to, but consultant2 will accept the data they have already available or can be exported in a format familiar to them, the client will prefer consultant2.
  - dragonwriter 5y ago
    Sure, but if everyone is blocked from using the format consultant1 opposes because the industry wakes up one day and abandons it utterly, then consultant2 no longer has an advantage, which is why someone who would be consultant1 except that they don't want to lose business to consultant2 lobbies for it.
    - dylan604 5y ago
      My point is that the consultant needs to understand the capabilities of the client more so than dictating terms just because it is convenient to the consultant. The scenario you describe is not out of the realm of possibility, but it's also not unheard of to have a client with such antiquated systems that there is no using of modern anything. Hence, why CSV will never die.
ab111111111 5y ago
I'd add: 4) Changes to the replacement format should be human-readable in a diff
- jonnycomputer 5y ago
  Agree. But a lot of this is tooling though, whether its adequate and whether its ubiquitously available. e.g. you can diff two directories in Linux, which is super useful, but directories are not text files. The issue is, can the differences be reasonably represented in text formatted output.
- masklinn 5y ago
  I'll take a good format and a diff filter thank you very much. It's easy to write a diff filter for e.g. xlsx, it's not possible to make CSV any good.
AtlasBarfed 5y ago
There are mature CSV libraries for most major languages that handle 99% of the problems of CSV. CSV should be better standardized, but ... whatever, what should be done to "fix" CSV is to advertise the proper use of the libraries and the nontrivial aspects of a superficially trivial format. A format that is trivially useful in 99% of cases is far better than many other "worse is better" things in computing.
- adgjlsfhk1 5y ago
  The problem is that even the fastest CSV readers are 10-100x slower than readers of better defined binary formats (like Arrow)
- masklinn 5y ago
  > There are mature CSV libraries for most major languages that handle 99% of the problems of CSV. They really don't. In fact I'd go further and confidently state that they really can't, because tons of mis-parsed CSVs are heuristic judgement values, and those tools don't really have the ability to make those calls. I've never seen a "mature CSV library for most major language" which'd guess encoding, separators, quoting/escaping, jaggedness, … to say nothing of being able to fix issues like mojibake.
  - burntsushi 5y ago
    No, they really do. But 99% isn't 100%. Compare Python's csv parser with Go's. The former will prefer a parse for any input, and this is done in a fairly consistent way that at least gives one the opportunity to deal with "malformed" csv. The latter is a strict parser and coughs up an error upon almost any deviation from RFC 4180. I couldn't use the latter in practice because of it. It would just choke on too many csv files I had.
    - masklinn 5y ago
      > No, they really do. But 99% isn't 100%. Neither is 0. Python’s csv library does not handle any problem, it applies the separators / quoting / escapes it was given and that’s that. It gives the developer the opportunity to handle things, but if the developer isn’t aware nothing is handled.
joelthelion 5y ago
Parquet (and a few other competitors) have open-source implementations in many languages, are much faster to load than csv, are natively compressed, are strongly typed and don't require parsing... There are few reasons to continue using csv in this day and age.
karteum 5y ago
I personally believe that at least SQLite matches all those criteria : "1) A truly open format is available" : sqlite is open-source, MIT-licensed, and well specified (even though I am usually not so happy with its weak typing approach, yet in this case this precisely enables a 100% correspondance between CSV and sqlite since CSV has also no typing at all...) "2) Applications have a speed increase from using csvs" : I think it should be obvious to everyone that this is the case... "3) The new format should not be grossly larger than the one it is replacing" : this is also the case
- unnouinceput 5y ago
  sqlite files are binary files, so a non-starter. If I open them in Notepad I can't read the values like I can with CSV.
  - postalrat 5y ago
    "If I open them in Notepad" Well there's your problem.
    - munk-a 5y ago
      There is a definite demand for some import format that you can trivially edit - CSV excels at this right now and JSON isn't that bad. Binary dump formats definitely do have a time and a place but there is also a separate need for trivially human readable formats.
      - postalrat 5y ago
        I guess my point is that CSVs or any text files aren't really "human readable". You still need some application top view them.
        HanaShiratori 5y ago
        So what's the alternative? Good old pen and paper?
osacial 5y ago
Besides, CSV can be written by a human and initially it was the way how data was entered and read, because it is a text file - database. If there is a need for something more convenient, then there are other file types. Nonissue, IMO.
7thaccount 5y ago
CSV is far from perfect, but it's nice that I can easily work with them without needing any libraries. All I need is file I/O and the ability to split strings. It doesn't get much simpler. I'll admit though that "import JSON" and then being able to essentially convert the entire file into a dictionary is nice if the data has more structure to it.
- dheera 5y ago
  CSV is still easier to parse because the C++ dudes still refuse to implement some kind of nice operator-overloaded interface like #include <json> std::json myjson("{\"someArray\": [1,2,3,4,{\"a\": \"b\"}]}"); std::cout << (std::string)myjson["someArray"][4]["a"]; and the result is we have 50 different rogue JSON libraries instead of an STL solution. Until the STL folks wake up, boost::split can deal with the CSV.
  - mivade 5y ago
    https://github.com/nlohmann/json https://github.com/nlohmann/json
    - dheera 5y ago
      ooh this is nice. STL should adopt it
    - meepmorp 5y ago
      Thank you!
  - RodgerTheGreat 5y ago
    A string split function is a poor choice if there's any possibility the CSV file contains quoted fields. Robust handling of both CSV and JSON requires a parser. In my experience, CSV can actually be trickier than JSON to parse because there are so many edge cases, alternatives, and ambiguities.
    - deleted 5y ago
deleted 5y ago
[deleted]
lallysingh 5y ago
Really, just use the right delimeters to start: https://en.wikipedia.org/wiki/Delimiter#ASCII_delimited_text https://en.wikipedia.org/wiki/Delimiter#ASCII_delimited_text Ascii #31 instead of commas, Ascii #30 instead of newlines. Now those characters can go into your values. If that's no good, zstd-compressed proto.
joelbondurant 5y ago
I would add 4) editable in vim.
danellis 5y ago
> The author is not forced to use csvs. But some people are. There are entire industries built around the exchange of CSV files, and the producers and consumers don't necessarily talk to each other.
red_admiral 5y ago
For most of my use cases, I also need 4) can easily interact with Excel. A lot of things 'around here' run off spreadsheets, because relational databases weren't invented here and no-one ever needs more than a few million rows apparently. I seem to spend half my job on the current project exporting stuff to CSV, running it through my code, then opening the resulting CSV in excel and formatting it a bit and saving as .xlsx again. Still, at least I don't have to use Visual Basic that way.

Twisell 5y ago

OP and you gave me an idea : "The only true successor of CSV should be forward/backward compatible with any existing CSV variant" If we manage to write a spec that meet this criteria we'll have a powerful standard with easy adoption.

masklinn 5y ago
Given the infinity of CSV variants existing is the very reason why CSV blows goats, creating a spec compatible with that is 1. a fool's errand, CSV "variants" are not compatible with one another and regularly contradict one another (one needs not look any further than Excel's localised CSVs) 2. resulting in getting CSV anyway, which is a lot of efforts to do nothing
ivanbakel 5y ago
That's ignoring two big points in the OP: that CSV variants are ill-defined and conflicting; and that existing CSV data is too poorly-specified. There is no reason to try to be "backwards-compatible" with existing CSV files - we don't have a single definition of correctness to use to check that the compatibility is correct. Every attempt to be parse existing data would result in unexpected results or even data loss for some CSVs in the wild, because there is no way to reconcile all the different expectations and specifications that people have for their own CSV data.
Macha 5y ago
The issue is CSV is not compatible with CSV. It's not possible to write a spec that covers all CSV files in the world. CSV means things that are mutually incompatible in the less common cases, and the only way to really parse them correctly is to know which variant generated it. But you can't even tag that variant in the file by your criteria, as existing CSV parsers won't understand it.
- Twisell 5y ago
  By this argument UTF8 can't exist. And yet here it is. PS: I never said 100% forward/backward compatible with all variant at the same time and without any noticeable artifact. I meant compatible in a non blocking way.
  - masklinn 5y ago
    What are you talking about? UTF8 is a single well-defined specification, and detecting that data is definitely not UTF8 is trivial.
    - deleted 5y ago
      [deleted]
    - Twisell 5y ago
      And yet it is forward/backward compatible with ASCII and non blocking against all it's ill defined variants.
      - Macha 5y ago
        ASCII was well defined, CSV was not. Therefore they could take the highest bit, which they could know that was unused per the ASCII spec, and use that to encode their extra UTF-8 information. Also UTF-8/ascii compatibility is unidirectional. A tool that understands ASCII is going to print nonsense when it encounters emoji or whatever in UTF-8. Even the idea that tools that only understand ASCII won't mangle UTF-8 is limited - sure dumb passthroughs are fine, but if it manipulates the text at all, then you're out of luck - what does it mean to uppercase the first byte of a flag emoji?
wernercd 5y ago
obligatory: https://xkcd.com/927/ https://xkcd.com/927/
- sicariusnoctis 5y ago
  This is true. The best way would be if everyone generated CSV files that were compatible with on one or two already existing CSV formats. (e.g. no-quote-plain-old-csv or quotes-forced-csv.) Hopefully, it would be easy to distinguish between the two in some well-defined manner. Maybe call it .csvs (CSV standardized). Or just introduce new extensions like .csvq ("CSV quoted"). Note the difference is that I am suggesting reducing the number of standards in-use by using only one already existing CSV format. :)
dragonwriter 5y ago
> If we manage to write a spec that meet this criteria we'll have a powerful standard with easy adoption. So, a binary format consisting of: (1) a text data segment (2) and end of file character (3) a second text data segment with structured metadata describing the layout of the first text data segment, which can be as simple (in terms of meaning; the structure should be more constrained for machine readability) as “It’s some kind of CSV, yo!” to a description of specific CSV variations (headers? column data types? escaping mechanisms? etc.) or even specify that the main body is JSON, YAML, XML, etc. (which would probably often be detectable by inspection, but this removes any ambiguity).
- Twisell 5y ago
  You got my vibe Almost any current CSV parser, even the bad ones, tolerate a header line. So it should be possible to define a compact and standardized syntax that is appended before the real header of the first cell (separator,encoding,decimal separator (often disregarded by most parsers but crucial outside USA),quote character,escape character,etc...). Following headers would just use special notation to inform on (data-type,length,comment). Newest parsers would use theses clues, older ones would just append some manageable junk to headers.
  - Macha 5y ago
    So someone opens this CSV in Excel and there's garbage in A1? Does this really count as compatible? You will get user bugs for this.
    - dragonwriter 5y ago
      > So someone opens this CSV in Excel and there's garbage in A1? Yeah, that's why I chose the “thing that looks like a text file—including optionally CSV—but has additional metadata after the EOF mark” approach instead of stuffing additional metadata in the CSV; there's no way to guarantee that existing implementations will safely ignore any added metadata the main CSV body. (My mechanism has some risk in that there are probably CSV readers that treat the file as a binary byte stream and use the file size rather than a text stream that ends at EOF, but I expect its far fewer than will do the wrong thing with additional metadata before the first header.

derefr 5y ago

You're comparing CSVs to other spreadsheet document formats. But a CSV is not a spreadsheet. A CSV is raw data. (It's data that is restricted to a shape that enables it to be easily imported into a spreadsheet—but data nevertheless.) As such, it should be compared to other data formats—e.g. YAML, JSON Lines, etc. These other data formats all win on your #2 against CSV, as CSV is actually horrible at parse-time vs. other data formats — the fact that both of its separators (newlines and commas) can appear as-is inside column values, with a different meaning, if those column-values are quoted, means that there's no way to parallelize CSV processing, because there's no way to read-ahead and "chunk" a CSV purely lexically. You actually need to fully parse it (serially), and only then will you know where the row boundaries are. If you've ever dealt with trying to write ETL logic for datasets that exist as multi-GB CSV files, vs. as multi-GB any-other-data-format files, you'll have experienced the pain. > The new format should not be grossly larger than the one it is replacing. Self-describing formats like JSON Lines are big... but when you compress them, they go back to being small. General-purpose compressors like deflate/LZMA/etc. are very good at shearing away the duplication of self-describing rows. As such, IMHO, the ideal format to replace ".csv" is ".jsonl.gz" (or, more conveniently, just ".jsonl" but with the expectation that backends will offer Transport-Encoding and your computer will use filesystem compression to store it — with this being almost the perfect use-case for both features.) ----- There's also Avro, which fails your point #1 (it's a binary format) but that binary format is a lossless alternate encoding of what's canonically a JSON document, and there are both simple CLI tools / and small, free, high-quality libraries that can map back and forth between the "raw" JSON document and the Avro-encoded file. At any time, you can decode the Avro-encoded file to text, to examine/modify it in a text editor. The data-warehouse ecosystem already standardized on Avro as its data interchange format. And spreadsheets are just tiny data warehouses. So why not? ;)

osigurdson 5y ago
Take an Excel file and change the extension to .zip, then extract the contents. You will see that it is a collection of XML files. Therefore it should be reasonable to conclude that this approach can work for Excel sized datasets. However it is not particularly readable/diff-able if this is part of your use case.

dragontamer 5y ago

> A truly open format is available and accessible. Sqlite? > Applications have a speed increase from using csvs. Sqlite? > The new format should not be grossly larger than the one it is replacing Sqlite it is. -------- Oh, you mean something that Excel can open? Oh yeah, I guess CSV then. But lets not pretend #1 (openness), #2 (speed), and #3 (size) are the issues.

ARandomerDude 5y ago
I'd add the constraint that it needs to be understandable by most people. A typical non-tech office worker can use CSV without any trouble. Sqlite has an extremely steep learning curve for someone unfamiliar with databases.
- salamandersauce 5y ago
  Exactly. Worst case a CSV can be edited and viewed or even created in MS Notepad or any other text editor which is pretty much guaranteed to be on a system. Something to deal with Sqlite? No such luck and the barrier is much higher.
  - masklinn 5y ago
    > Worst case a CSV can be edited and viewed or even created in MS Notepad or any other text editor which is pretty much guaranteed to be on a system. Good luck getting that garbage to be ingestible by anything. Odds are the system you're trying you shove it in will start by choking on the UTF8 BOM notepad insists on, then it will choke on the record separators, then on the incorrect escaping (or quoting). > Something to deal with Sqlite? No such luck and the barrier is much higher. SQLite is already present in most if not all environments (usually multiple times too, you have at least one instance of sqlite per browser on your machine, and probably a system one as well, and a few others), so it's really at most a matter of providing SQLite clients by default.
    - sigzero 5y ago
      SQLite is not the answer. At all.

cls59 5y ago

To point 1, I'd argue that a SQLite database is a great next step beyond CSV despite being a binary format.

lmilcin 5y ago
SQLite also requires external dependency and special execution environment and are not as portable as CSV.
- srcreigh 5y ago
  SQLite is included in most operating systems by default. It's also on macOS and iOS. It also runs on all kinds of embedded devices in addition to personal computers and servers. https://en.wikipedia.org/wiki/SQLite#Operating_systems https://en.wikipedia.org/wiki/SQLite#Operating_systems
  - deleted 5y ago
    [deleted]
  - lmilcin 5y ago
    Just because most popular operating systems have libraries, doesn't yet mean it is portable. Can I click on it and open it in Excel? If not then it is not portable for me and for a lot of other people. The main reason I use CSV is to produce reports that I can either open myself or send to other people so that they can click on it and open themselves and immediately start hacking away. Excel is still corporate lingua franca and until this changes CSV is not going to retire. CSV means for me that I can send it to anybody and everybody will know how to use it. Try sending SQLite binary to people you work with, to your accountant, to your wife. Then share your experience.
    - wombatpm 5y ago
      Try using tabs. Still open in excel with less problems

cbsmith 5y ago

1. open(2) doesn't care about the file format 2. Yes, mixing formatting with data slows down data processing, don't do it. 3. Excel is not the replacement for CSV, and CSV is not a compact format. I mean, maybe if you are used to XML it is, but otherwise, just no. Yes, there is truly a problem.

da_chicken 5y ago
> CSV is not a compact format That's only true if you're trying to send all your data in a single, monolithic CSV. If you're sending multiple CSVs, you're capable of representing data as well as a relational data store. Which is to say, you're representing your data using a system of data normalization specifically designed to minimalize data duplication. A single CSV represents a single table, and in most cases with intelligent delimiter selection you can represent an entire data set with no more than one character spent between fields or records. Yes, you do have situations where you're storing losing data density due to using plain text strings, but that's not a limitation particularly unique to CSV for data serialization formats. Additionally, it is a problem that can largely be mitigated by simple text compression. Furthermore, once you switch to a non-text representation, you're limiting yourself to whatever that data representation is. It's easy to represent an arbitrary precision decimal number in plain text. It's hard to find a binary representation that universally represents the same data regardless of the system on the other end. Again, that's not a problem unique to CSVs. If you're working with an API, object by object, then JSON is certainly going to be better, yes, because you can use the application's object representation. If you're working with bulk data of many disparate, unrelated, complex objects, however, or where you're transferring and entire system, you're not going to do much better than CSV.
- cbsmith 5y ago
  > That's only true if you're trying to send all your data in a single, monolithic CSV. No, that's true in general. There are exceptional cases where it might not be true, but it's true in general. > Yes, you do have situations where you're storing losing data density due to using plain text strings, but that's not a limitation particularly unique to CSV for data serialization formats. That there are other inefficient data serialization formats does not mean that it is not an inefficient data serialization format. > Additionally, it is a problem that can largely be mitigated by simple text compression. "Simple text compression"? Not sure what you are referring to there. If you are speaking of generalized compression, there's a bunch of information theory pointing out the problem with that thinking. > Furthermore, once you switch to a non-text representation, you're limiting yourself to whatever that data representation is. It's easy to represent an arbitrary precision decimal number in plain text. It's hard to find a binary representation that universally represents the same data regardless of the system on the other end. Again, that's not a problem unique to CSVs. It's pretty easy to represent arbitrary precision decimal numbers in non-text formats and they mean the same thing regardless of the system on the other end, but let's say you were right. Great, now represent arbitrary precision binary numbers (you know, the kind that computers work with), in text formats. > If you're working with an API, object by object, then JSON is certainly going to be better, Let me stop you right there. It is not "certainly going to be better". JSON is generally worse. > If you're working with bulk data of many disparate, unrelated, complex objects, however, or where you're transferring and entire system, you're not going to do much better than CSV. There's a whole world out there of formats that are better than CSV at this job, and in many cases they are A LOT better than CSV. Even Avro, for all its pitfalls, is MUCH better.

dragonwriter 5y ago

> The author is not forced to use csvs. The author appears to be a consultant selling data prep/transformation services. As long as the market is using CSVs, he’s forced to use CSVs, at least as end-of-pipeline inputs and outputs. Of course, “people optimize their workflows for something other than making my job easy” is a common, but also rarely persuasive in motivating action from others with different jobs, complaint.

dylan604 5y ago
The consultant should be less concerned about what makes their life/job easier, but what makes their client's job easier by working with the consultant. If the client has to jump through hoops to serve consultant1 data in a format they are not used to, but consultant2 will accept the data they have already available or can be exported in a format familiar to them, the client will prefer consultant2.
- dragonwriter 5y ago
  Sure, but if everyone is blocked from using the format consultant1 opposes because the industry wakes up one day and abandons it utterly, then consultant2 no longer has an advantage, which is why someone who would be consultant1 except that they don't want to lose business to consultant2 lobbies for it.
  - dylan604 5y ago
    My point is that the consultant needs to understand the capabilities of the client more so than dictating terms just because it is convenient to the consultant. The scenario you describe is not out of the realm of possibility, but it's also not unheard of to have a client with such antiquated systems that there is no using of modern anything. Hence, why CSV will never die.

ab111111111 5y ago

I'd add: 4) Changes to the replacement format should be human-readable in a diff

jonnycomputer 5y ago
Agree. But a lot of this is tooling though, whether its adequate and whether its ubiquitously available. e.g. you can diff two directories in Linux, which is super useful, but directories are not text files. The issue is, can the differences be reasonably represented in text formatted output.
masklinn 5y ago
I'll take a good format and a diff filter thank you very much. It's easy to write a diff filter for e.g. xlsx, it's not possible to make CSV any good.

AtlasBarfed 5y ago

There are mature CSV libraries for most major languages that handle 99% of the problems of CSV. CSV should be better standardized, but ... whatever, what should be done to "fix" CSV is to advertise the proper use of the libraries and the nontrivial aspects of a superficially trivial format. A format that is trivially useful in 99% of cases is far better than many other "worse is better" things in computing.

adgjlsfhk1 5y ago
The problem is that even the fastest CSV readers are 10-100x slower than readers of better defined binary formats (like Arrow)
masklinn 5y ago
> There are mature CSV libraries for most major languages that handle 99% of the problems of CSV. They really don't. In fact I'd go further and confidently state that they really can't, because tons of mis-parsed CSVs are heuristic judgement values, and those tools don't really have the ability to make those calls. I've never seen a "mature CSV library for most major language" which'd guess encoding, separators, quoting/escaping, jaggedness, … to say nothing of being able to fix issues like mojibake.
- burntsushi 5y ago
  No, they really do. But 99% isn't 100%. Compare Python's csv parser with Go's. The former will prefer a parse for any input, and this is done in a fairly consistent way that at least gives one the opportunity to deal with "malformed" csv. The latter is a strict parser and coughs up an error upon almost any deviation from RFC 4180. I couldn't use the latter in practice because of it. It would just choke on too many csv files I had.
  - masklinn 5y ago
    > No, they really do. But 99% isn't 100%. Neither is 0. Python’s csv library does not handle any problem, it applies the separators / quoting / escapes it was given and that’s that. It gives the developer the opportunity to handle things, but if the developer isn’t aware nothing is handled.

joelthelion 5y ago

Parquet (and a few other competitors) have open-source implementations in many languages, are much faster to load than csv, are natively compressed, are strongly typed and don't require parsing... There are few reasons to continue using csv in this day and age.

karteum 5y ago

I personally believe that at least SQLite matches all those criteria : "1) A truly open format is available" : sqlite is open-source, MIT-licensed, and well specified (even though I am usually not so happy with its weak typing approach, yet in this case this precisely enables a 100% correspondance between CSV and sqlite since CSV has also no typing at all...) "2) Applications have a speed increase from using csvs" : I think it should be obvious to everyone that this is the case... "3) The new format should not be grossly larger than the one it is replacing" : this is also the case

unnouinceput 5y ago
sqlite files are binary files, so a non-starter. If I open them in Notepad I can't read the values like I can with CSV.
- postalrat 5y ago
  "If I open them in Notepad" Well there's your problem.
  - munk-a 5y ago
    There is a definite demand for some import format that you can trivially edit - CSV excels at this right now and JSON isn't that bad. Binary dump formats definitely do have a time and a place but there is also a separate need for trivially human readable formats.
    - postalrat 5y ago
      I guess my point is that CSVs or any text files aren't really "human readable". You still need some application top view them.
      - HanaShiratori 5y ago
        So what's the alternative? Good old pen and paper?

osacial 5y ago

Besides, CSV can be written by a human and initially it was the way how data was entered and read, because it is a text file - database. If there is a need for something more convenient, then there are other file types. Nonissue, IMO.

7thaccount 5y ago

CSV is far from perfect, but it's nice that I can easily work with them without needing any libraries. All I need is file I/O and the ability to split strings. It doesn't get much simpler. I'll admit though that "import JSON" and then being able to essentially convert the entire file into a dictionary is nice if the data has more structure to it.

dheera 5y ago
CSV is still easier to parse because the C++ dudes still refuse to implement some kind of nice operator-overloaded interface like #include <json> std::json myjson("{\"someArray\": [1,2,3,4,{\"a\": \"b\"}]}"); std::cout << (std::string)myjson["someArray"][4]["a"]; and the result is we have 50 different rogue JSON libraries instead of an STL solution. Until the STL folks wake up, boost::split can deal with the CSV.
- mivade 5y ago
  https://github.com/nlohmann/json https://github.com/nlohmann/json
  - dheera 5y ago
    ooh this is nice. STL should adopt it
  - meepmorp 5y ago
    Thank you!
- RodgerTheGreat 5y ago
  A string split function is a poor choice if there's any possibility the CSV file contains quoted fields. Robust handling of both CSV and JSON requires a parser. In my experience, CSV can actually be trickier than JSON to parse because there are so many edge cases, alternatives, and ambiguities.
  - deleted 5y ago

deleted 5y ago

[deleted]

lallysingh 5y ago

Really, just use the right delimeters to start: https://en.wikipedia.org/wiki/Delimiter#ASCII_delimited_text https://en.wikipedia.org/wiki/Delimiter#ASCII_delimited_text Ascii #31 instead of commas, Ascii #30 instead of newlines. Now those characters can go into your values. If that's no good, zstd-compressed proto.

joelbondurant 5y ago

I would add 4) editable in vim.

danellis 5y ago

> The author is not forced to use csvs. But some people are. There are entire industries built around the exchange of CSV files, and the producers and consumers don't necessarily talk to each other.

red_admiral 5y ago

For most of my use cases, I also need 4) can easily interact with Excel. A lot of things 'around here' run off spreadsheets, because relational databases weren't invented here and no-one ever needs more than a few million rows apparently. I seem to spend half my job on the current project exporting stuff to CSV, running it through my code, then opening the resulting CSV in excel and formatting it a bit and saving as .xlsx again. Still, at least I don't have to use Visual Basic that way.