“ I’m not going to advocate for any one successor file format here, but there are certainly a lot of contenders. The big data ecosystem gave us formats like Avro, Parquet, and Arrow that are widely used as an intermediate representation when transferring data between systems. HDF51 is widely used in the scientific computing community.”
CSV is a format more for humans and less for machines, but that is the use case: a format that is good enough to be compiled by humans and read by machines. At the moment there aren't many alternatives.
Objectively, CSV is terrible for humans despite being a plaintext format. No one reads CSVs: they're incomprehensible since the columns are not aligned with the headings. (You might be drawing an analogy with JSON, which is often human readable because it puts the keys right there next to the values).
The best that can be said for its simplicity is that it's easy to write code that can dump data out in CSV format (and to a lesser extent, it's easy to parse it, though watch out for those variants). This is not a really strong argument, most everyone is going to use a library for serialization, there's no reason to write your own unless it's for learning.
I'm a human and I love CSVs. What other format can I open in a spreadsheet, access through cat/grep/awk, and easily load into any programming language? Any other format has to sacrifice one of these three things, and that's bad. It's this trifecta that makes it so versatile and human friendly.
Developer friendly. Not human friendly. I used to receive CSVs of product data from clients, which they often handcrafted or manipulated by hand, and inevitably, and I mean inevitably, broke.
Of course, excel will make just as much of a mess - when the client hands back their sheet with all of their UPCs expressed as exponents and æ€ ligatures jammed next to every apostrophe, there’s no guessing as to what happened.
Honestly though, if people are doing that with CSVs there is no system where they won't also go off the reservation and do something insane with an excel file or sqlite or whatever. At least with CSVs there are common lines of code all around the internet to wrangle all sorts of issues.
Anything written by hand will be screwed up. Chances are if they're writing it by hand and it's badly formatted, they're not using something that has input or output validation anyways, and they're probably not going to budge from whatever workflow they're using.
Some people like me never generate CSVs like that. If someone sends me a CSV with quotes, I reject it and say "clean up your data". It's okay to say, "we're not going to allow escape characters in our CSV grammar. We're going to have a higher standard"
I recall one of my old Phones could dump SMS messages as CSV. I wanted to be build and interface that would let me search through my messages.
Turns out when you have data that contains both commas and quotes things get screwey real quick. You could have quoted data like:
123,ABC,”,””,456
Where ,” is column data.
I think that standard method is to double quote the field, but the dump sure wasn’t doing that for me, plus what happens when the data is something like abc””,?
That is badly formatted CSV, assuming ” is a double-quote. Double-quote (") is how you escape commas, so the 3rd entry will make a parser barf, since there is no close quote.
It should be 123,ABC,"","",456
> Turns out when you have data that contains both commas and quotes things get screwey real quick.
Not really. It's pretty logical. If the entry has even one comma, put quotes around it. If it also has quotes, double each of them. Otherwise don't worry about it.
> what happens when the data is something like abc””,?
Surprised you would think that JSON is more human-readable, as the layout of that makes it visually quite appalling - not so bad when through a pretty-printer utility, admittedly, but may as well go for XML as another use case
For ages I would just dump json files in a browser and let it auto-pretty print it for a first look.
A while back I discovered https://dadroit.com/ which is small (in feature set) but perfectly formed (nicely designed and extremely fast, even on huge files).
I try to use tab-delimited files when writing out columnar text to be loaded into something else (assuming it can parse them). It's even easier to read since you don't have extraneous commas and double quotes everywhere and most viewers will line things up to some degree via the tabs. Tabs are less common in data fields (at least when compared to double quotes and commas), at least in my experience.
That doesn't qualify as "good enough to be compiled by humans". We need something dumb and easy you can author from scratch with any text editor, and read/understand with just `cat`.
CSV isn't supported in Excel because Microsoft prefers it. MS prefers its own Excel format. Excel supports CSV because a large enough majority of Excel users want it, which means MS includes support for it. For MS to remove CSV support in favor of Arrow or Parquet support would not benefit MS at all, and would make many of its customers push back, so I don't see it happening.
Interesting that the two main "alternatives" mentioned here are columnar data storages. The problem I see with both Arrow and Parquet are that they are binary-based files. Sure, binary files can be VERY efficient. But the "magic" behind files like CSV, JSON or YAML is that they are both "sufficiently" both human and machine readable.
Maybe a CSV killer would be a human readable columnar based file format.
Nevertheless, the article basically discusses the issues encountered with the manual "editability" of CSV files, not so much with its performance. It also mentions parquet or arrow and concedes that they require specialized format to read/write. If we are looking to that, then there are a lot of options such as sqlite format, BerkleDB (used by some cryptocurrency projects) among plenty of others.
And CSV can be consumed and produced by spreadsheet software, which is useful to critical in many contexts where CSV is part of the pipeline. There the alternative would be to use xlsx files which… isn't necessarily any better.
Until someone gets excel to ingest and produce something in a better format, we're pretty much stuck.
As I mentioned down-thread, I can generate a CSV with a couple of fprintf statements and a loop. I definitely can't do that with .xlsx. There is almost zero friction to bolting CSV export capability to an existing system, which is part of why it's so popular.
> As I mentioned down-thread, I can generate a CSV with a couple of fprintf statements and a loop.
And usually generate garbage for anything but the most trivial case, which really nobody gives a shit about. That's the main reason why CSV absolutely sucks too, you have to waste month diagnosing the broken shit you're given to implement the workarounds necessary to deal with it.
> I definitely can't do that with .xlsx.
You probably can though. An xlsx file is just a bunch of XML files in a zip.
Even if it were true it wouldn't matter a whit to the production side of the format, which is what "produce CSVs using fprintf" is: excel can consume them all.
Define "garbage." If I know what my data looks like, I can anticipate the edge cases ahead of time. Plenty of CSV exports work this way, they don't need to be general if the schema is already imposed by the system.
Have you ever worked in embedded systems? Writing XML files and then zipping them on a platform with 32 kilobytes of RAM would be hell. CSV is easy, I can write the file a line at a time through a lightweight microcontroller-friendly filesystem library like FatFS.
I know this is HN and we like to pretend we're all data scientists working on clusters with eleventy billion gigs of RAM, but us embedded systems folks exist too.
Incorrect encoding, incorrect separators (record and field both), incorrect escaping / quoting, etc…
> If I know what my data looks like
If you control the entirety of the pipeline, the format you're using is basically irrelevant. You can pick whatever you want and call it however you want.
> Have you ever worked in embedded systems? Writing XML files and then zipping them on a platform with 32 kilobytes of RAM would be hell. CSV is easy, I can write the file a line at a time through a lightweight microcontroller-friendly filesystem library like FatFS.
You can pretty literally do that with XML and zip files: write the uncompressed data, keep track of the amount of data (for the bits which are not fixed-size), write the file header, done. You just need to keep track of your file sizes and offsets in order to write the central directory. And the reality's if you're replacing a CSV file the only dynamic part will be the one worksheet, everything else will be constant.
> If you control the entirety of the pipeline, the format you're using is basically irrelevant.
I think you are missing the point -- you only need to know about generator to know about format.
Since the parent poster was talking embedded, here is one example: a data logger with tiny embedded records tuples: (elapsed-time, voltage, current). You need this to be readable in the widest variety of programs possible. What format do you use?
I think the answer is pretty clear: CSV. It is compatible with every programming language and spreadsheet out there, and in a pinch, you can even open it in text editor and manually examine the data.
Using something like XLSX here would be total craziness: it will make code significantly bigger, and it will severely decrease compatibility.
People doesn't matter which format is, since it simple work in any spreadsheet software.
Yeah i can generate any file with a bunch of printf, but csv i dont have to read a specification, i its possible to read with a bunch of read without have to use a xml or xlsl library.
As soon as you open it in Excel, it's garbage anyway, since it will replace date-like items with nonsense, drop number digits, convert anything it can, reencode monetary unities, and so on.
If you don't open it in Excel, you can have as strict a parser as you want, just like any other format.
> If you don't open it in Excel, you can have as strict a parser as you want, just like any other format.
No, you can not. Because the CSV format is so fuzzy you can very easily parse incorrectly and end up with a valid parse full of garbage.
Trivially: incorrect separator, file happens to not contain that separator at all, you end up with a single column. That's a completely valid file, and might even make sense for the system. Also trivially: incorrect encoding, anything ascii-compatible will parse fine as iso-8859-*. Also trivially: incorrect quoting / escaping, might not break the parse, will likely corrupt the data (because you will not be stripping the quotes or applying the escapes and will store them instead).
It's like you people have never had to write ingestion pipelines for CSVs coming from randos.
Because there is no way to perform that enforcement, becauae you say “CSV” and people understand “my old garbage” and you can’t fix that; and because millions of incorrect documents will yield a valid but nonsensical parse.
>It's like you people have never had to write ingestion pipelines for CSVs coming from randos.
That's because this is not what this thread is about.
The comment you're responding to is not about CSVs coming from "randos". It's for the case where that rando is you, so you can make sure the problems you mention don't happen on the generation side of CSVs.
> That's the main reason why CSV absolutely sucks too [...]
Is it? I think you're absolutely right that naive points of view like the one you're responding to will lead to avoidable bugs, but I'm not so sure the problem is CSV so much as people who assume CSV is simple enough to parse or generate without using a library.
> I'm not so sure the problem is CSV so much as people who assume CSV is simple enough to parse or generate without using a library.
The simplicity of CSV is what tells people that they can parse and generate it without a library, and even more so that that's a feature of CSV. You just had to read the comments disagreeing with me to see exactly that.
I have a script which generates a CSV file using a bunch of print statements. The columns are hostnames and some numbers, so it is never going to contain commas or newlines. This will be perfectly valid CSV every time.
That’s why CSV is absolutely beautiful - there is a huge number of applications that people really care about, and their data is constrained enough that there is not need to care about CSV escaping and need for any third party libraries.
Creating XSLX file by hand is possible, but this will be a large amount of code and I wouldn’t include this in my script, it would need to be a separate library - which means build system support, learning the API etc...
You can write what "looks" like CSV to you, but there are no guarantees it will import correctly.
The problem is 10x worse when you get CSV from one source and rely on another process to load it. I fought this problem for several days going from NetSuite to Snowflake via CSV.
This is true, but a lot of data processing takes place in a context where frictionless export functionality is more important than a 100% guarantee of import compatibility. I'd rather ingest city = ",CHANGSHA,HUNAN" (real example!) than ingest nothing at all because my vendor doesn't have time to integrate a JSON serializer.
Can you give an example? The rules for CSV files are so simple I'm struggling to imagine a case where something looks correct but in fact isn't correct.
If you're manually generating your own CSV files, you probably know what kind of data you are generating and consequently whether your data is going to contain commas. If commas and newlines don't exist in your data, then you can safely ignore quoting rules when generating CSV files. I know that I've generated CSVs in the past and rather than figuring out the correct way to quote the strings, I just removed any inconvenient characters without any loss to the data at all. Obviously this is not "correct" but you don't have to implement cases if you know they won't show up.
That sounds like the problem of badly formatted CSV, not a problem with CSV per se.
If you stick to one delimiter, and that delimiter is a comma, and escape the delimiter in the data with double-quotes around the entry, and escape double quotes with two double-quotes, well, you have written CSV that is correct and looks correct and will be parsed correctly by literally every CSV parser.
That's really not a serious argument against CSV. Since you paraphrase in a silly way, I can do it too! Your "argument" is "Badly formatted files exist, therefore CSV bad".
Everyone "against CSV" seems to be arguing against badly formatted CSV, and leaping to the conclusion that "CSV is just bad" without much more to say about it. I'm sorry that badly formatted CSV gave you a bad time, but the format is fine and gets its job done.
"It doesn't have x, y or z feature therefore no one should be using it ever" is kind of a dumb argument, honestly.
> Your "argument" is "Badly formatted files exist, therefore CSV bad".
The argument is actually that the badly formatted CSV files have taken over, therefore CSV is bad. You can't reject them, so your import becomes unreliable.
Me, a naive idiot: CSV is simple I will write my own exporter because I am clever
Me, 20 minutes later: Heh that was easy I am a genius
Me, 21 minutes later: Unicode is ruining my life T_T
Don't get me wrong, I really like CSV because it's so primitive and works so well if you are disciplined about it. But it's easy to get something working on a small dataset and forget all the other possibilities only to faceplant as soon as you step outside your front door. In the case above my experience with dealing with CSV data from other people made me arrogant, when I should have just taken a few minutes to learn my way around a mature library.
In UTF-8, the byte for a comma and a quote only exist as their characters. They don't exist as parts of multibyte sequences, by design.
If you have Unicode problems, then you have Unicode problems, but they wouldn't seem to be CSV problems...? Unless you're being incredibly sloppy in your programming and outputting double-byte UTF-16 strings surrounded by single-byte commas and quotes or something...?
> but there are no guarantees it will import correctly.
What do you mean, there are "no guarantees"? You are in charge! You know what data you're dumping, you can see if it imports well. You can tailor your use case.
That's not the same as getting a CSV from some dump, where you have limited (if any) control over the behavior.
After working with so many retailers and online sales channels, things that are considered "legacy" or "outdated" by the HN crowd doesn't seem like it will go away unless both sides make a change. There are numerous articles posted on HN about how "FTP is dead" or no one uses it anymore, when it's far from the case.
Even Amazon's marketplace and vendor files are still using SFTP and EDI files. They've recently made changes, but it's been slow and hasn't had widespread adoption.
There's also the universality and "simplicity" CSV provides to the non-computer literate, and convincing them to make a change to a new standard provides itself some non-technical challenges. CSV is a bad standard, but it's the best one given what it does and its flexibility.
What are all these hypothetical humans doing with CSV files? I've worked with them a lot, and the only time I read one manually is to find out why it isn't parsing properly. I'm not writing them by hand. I'm not extracting data from them by hand. What are other people doing?
Sometimes you need to look up a single code in a 50 MB file. And sometimes you need a quick check to see if one line or a million lines changes.
It's "exception not the rule" type stuff... but it sure comes in handy to be able to check this stuff quick with basic text tools than have to run it through some binary parser. Same as JSON. But unlike protobufs for example.
Interesting. I would reach for `grep` and `diff` in those cases, so it's not much of a leap to imagine similarly using a tool for a binary format. After all, I only need the textual representation of the thing I'm searching for, not the entire dataset.
It's probably not news to anyone who works with data that CSVs are inferior to other file formats like parquet.
I think the valuable insight here is that there needs to be a meme / movement that CSV is bad or deprecated. That's what's actually going to put the nails in its coffin, not private griping from developers when they get CSVs.
I'm with you! But what does a hot new startup do when a rich old legacy corp sends them a data dump that isn't a custom sqlite database? Slowly but surely, the startup begrudgingly uses csv "just this once" until they grow into the legacy corp that sends out files in csv because "we've done it this way forever."
The thing is, I need a hefty library to write parquet. I can write a CSV with a couple fprintf statements and a loop. A parser isn't much harder if we can make assumptions about CSV dialect ahead of time.
If I'm trying to make an exportable format for a data logger with an SD card running on an ARM microcontroller, it doesn't get much easier than CSV. Sure, I could save space by rolling my own binary format, but then I have to provide a PC application to read it (and realistically, the user is probably just going to want that application to dump to a CSV anyway!).
I agree that for many use cases there are much better alternatives, but one of the reasons CSV is so popular is because it's so simple. It shouldn't be used for multi-gigabyte datasets, but for many simple use cases it works great.
no. you can't write CSV with a couple of fprintf statements in a loop. If your data has a comma or a double-quote in it, the field needs to be surrounded by double quotes (and the double quote needs to be doubled). Ditto if you have leading or trailing spaces.
that is... if you're following the RFC 4180 guidance for CSV. microsoft has another internal standard that occasionally gets referenced.
If Jane from accounting and Bobby from marketing know those trick (and they do) then Csv does open in excel, and its replacement has to do at least as good.
Excel definitely opens CSV files, and Windows file associations tend to set that up by default.
However, Excel then goes ahead with changing the content to often wrongly-assumed formats. My favourite hate is how it will convert mobile numbers from a string of digits to an unusable exponential-format number. Hey, it doesn't even set column widths to make the content visible!
Surely the successor should be SQLite. Tabular data like CSV, easy to view on different mediums, free and open source, single file databases. It has to be this, right?
yeah, why not replace a dense human readable easily generated format with a third party format that requires installation of compiled binaries and an over the wire protocol
An SQLite dump is human readable and the data in the dump actually is in a CSV format, just surrounded with information on types and relationships. Here's the first few lines for example of a dump of an SQLite DB that I have of some temperature sensor data:
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE temperature
(
ts integer,
Tc real,
Tf real,
src_id integer
);
INSERT INTO temperature VALUES(1615165342,5.5999999999999996447,42.099999999999999644,1);
INSERT INTO temperature VALUES(1615165350,0.6,32.999999999999998223,3);
INSERT INTO temperature VALUES(1615165404,5.5,41.899999999999995026,1);
INSERT INTO temperature VALUES(1615165410,-17.199999999999999289,1.0,2);
INSERT INTO temperature VALUES(1615165435,5.5,41.899999999999995026,1);
SQLite dumps would actually be a pretty good data exchange format. As mentioned there is a CSV inside there. It's not hard to extract that if you need an unadorned CSV--some grep and sed, or grep and cut, or a few lines of scripting.
Or if you have SQLite installed, it is an easy to have SQLite itself read the dump and export it in CSV, letting it deal with things like quoting that can be a pain with grep and sed/cut. This way also makes it easy to rearrange or omit columns and to do some filtering so that data you don't care about doesn't get into the CSV.
The main feature of CSV is that it's aways broken, not that it's almost trivial.
> with one that requires a specific 85 kLOC codebase to access
sqlite is available essentially everywhere. Every browser uses sqlite extensively internally, so do tons of other software on your standard desktop machine (to say nothing of mobile OS where it's ubiquitous). Using sqlite is not any sort of constraint.
> SQLite is wonderful, but it's definitely not a replacement for CSV
Only in the sense that it's nowhere near as bad and annoying as CSV.
There are probably valid uses for CSV, but more often than not, it's the wrong choice.
As soon as data has any form of structure to it (and most data does). CSV complicates everything. Even for unstructured data, the problem of escape characters often shows it's ugly head. The moment your data contains a comma, tab, or space, you run into a nasty mess that, in the best case makes your system fail, and in the worst case silently adds corrupt data into the system.
Neither JSON nor XML suffer from that problem and both can easily be used in any scenario you'd use CSV. The only argument against either format is they are a bit more bulky than CSV.
Correct, and those methods of escaping are clearly defined in both specs. In CSV, there is no universal escaping system. It's all over the board.
Any variable length text data format is going to run into issues when special characters are used in the data. CSV has no definition of what should happen when that occurs.
The characters to escape are clearly defined in both specs, but that doesn't mean there's a universal escaping system. Just google "how to escape JSON" and you will see the wealth of users struggling to understand what and how to accomplish this. There will always be people struggling with escape characters, I don't see it being any harder or easier in any format.
CSV is bad - but convenience is hard to beat. It's the same thing with excel. People abuse it, but you just can't beat the fact that your programmer, CEO, analyst, and secretary can all contribute to the same file.
Yeah, this is pretty much it. The author complains about CSVs being "notoriously inconsistent" as though switching to some other format would magically change that. They're only inconsistent because sometimes lazy programmers do ",".join(mylist) instead of using an RFC4180 compliant CSV writer. Lazy programmers will just use non-compliant methods of creating whatever magic format OP is dreaming about. Case in point: trailing commas in JSON objects, and other ridiculous things that people have come up with such as encoding a date in JSON like this: "\/Date(628318530718)\/" https://docs.microsoft.com/en-us/previous-versions/dotnet/ar...
CSVs also are great because you can parse them one row at a time. This makes for a very scale-able and memory-efficient way of processing very large files containing millions of rows.
Let there be no mistake: Everyone reading this today will retire long before CSVs retire. And that's just fine by me.
>CSVs also are great because you can parse them one row at a time. This makes for a very scale-able and memory-efficient way of processing very large files containing millions of rows.
Even RFC4180-compliant CSVs can be incredibly memory-inefficient to parse. If you encounter a quoted field, you must continue to the next unescaped quote to discover how large the field is, since all newlines you encounter are part of the field contents. Field sizes (and therefore row sizes) are unbounded, and much harder to determine than simply looking for newlines - if you were to naively treat CSV as a "memory-efficient" format to parse, you would create a parser that would be easy to blow up with a trivial large file.
please don't - CSV is a perfectly fine textual file format for many things that can be used by the technical and non with a wide range of support in applications and programming languages.
As long as it is text-based. At least you can actually look at a CSV and see what is going (wrong), as well as use all the text tools we have. Not that there aren't problems as the article points out. But some binary file based on the whims of a proprietary program...no thanks.
Human can't efficiently write or parse XML or json, though. In some scenarios CSV hits the right spot to be accessible to human and computer, and the table can be laid out so that one can sort/grep/awk to quickly gain some insight.
Well, to work as a CSV-like JSON, i.e greppable similar to a CSV it needs formatting with linebreaks as above. It is still completely valid JSON. ++ is the formatting convention.
Yeah, it's never really come up as it's generally trivial to convert into this format from either a text editor or sed (Look for the `],[` and put a new line after the comma).
Late for the response but: JSON naturally encourages data to structural in ways that are often nested. In your example, if there's one or two additional layers of context (say, affiliation of the two persons), maintaining a CSV-like layout will be more cumbersome and unnecessary. I don't see many CSV or tabular-like JSON/XML in the wild and they really shouldn't be used that way.
Also it's way more difficult to parse than CSV. I'm not saying that CSV is better (or worse), just that to each it's job! Wtf is this about retiring a format because it doesn't fit someone's expectation!
I disagree. Json, in particular, can be nearly as compact as CSV by storing the data as an array of arrays.
[[1,2,3],
[4,5,6]]
It's easy to make a structured data interchange format mimic and unstructured format. It's impossible to go the other way around without severe problems.
Correct. That'd be a moving goalpost. The original claim was that JSON doesn't work well with columnar data.
If the primary usecase is to take data give it to a non-programmer to evaluate it, then CSV isn't terrible. Add a transformer that's ran when someone manually requests to see the data.
However, for machine to machine communication, CSV should never be used.
Excel actually has really good JSON import… it's just "hidden" in the Data tab on the ribbon and users don't want to learn how to use it.
I feel so much of what keeps CSV in use is that it's a format that you can relatively easily generate without pulling in a lot of library dependencies and relatively quickly generate something that users can just "double click it opens in Excel".
What the Excel team could really gift to developer humanity at this point is some dumb file extension Excel registers like XLJSON that you could just rename a JSON file to and Excel opens it like a spreadsheet on double click.
It can be, but what's ensuring that format when you read in a JSON file? JSON is one of the formats Pandas can read, but it has to be structured in a format the python library can read in as tabular data. Excel would have the same issue as would any program that is consuming tabular data. At least with CSVs, you know the data is tabular.
> It can be, but what's ensuring that format when you read in a JSON file?
What's ensuring the format of data in a CSV file?
Format comes from the same place it comes from for CSV. It's part of whatever data contract you are making with whatever system is providing the data. If someone shoves YAML into a Json file you've got problems, just like you have problems if someone starts talking about cars when you expected a file about airplanes.
At some point, you've got to turn file data into application data regardless of format. CSV offers no help in shaping the data and has a bunch of footguns to boot.
> JSON is one of the formats Pandas can read, but it has to be structured in a format the python library can read in as tabular data.
Pandas is highly flexible in it's ability to read in Json data, What I showed would be trivially loadable by Pandas, so would other formats (including more traditional JSON data).
Turning structured data into tabular data is trivial. What isn't trivial is turning tabular data into structured data.
CSVs are plain text, and that's huge, plus they have a few other advantages:
1. You can read them without any software
2. Streamable row by row
3. Compress well
To be honest most of the points in this article could be addressed by standardizing a method of defining a schema for the CSVs. It could even be backward compatible by appending the definition as metadata on the header column or as a separate file.
One thing that would be good to have is a standardized method of indexing CSVs so that random access is possible too, though that would be more involved.
I think an ideal solution would be csv with json metadata elements as the header. If a program supports the header, then you get the metadata. If not, no big deal, you just get an ugly header.
Perhaps, though maybe something simpler and readable would be easier to get people to use
name::string,number of legs::int,height in meters::float,date of birth::date(MM/DD/YYYY),email adress::email,website::url
joe,2,1.76,12/12/1999,joe@joe.com,https://www.joe.com
bob,1,1.84,12/12/1944,bob@vietnam.com,null
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.
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
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.
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.
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.
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?
To be fair, there is basically no way to manipulate arbitrary text at all without mangling it, UTF-8-aware or not. What does it mean to take the first 7 characters of a UTF-8 string which might contain combinator characters and left-to-right special chars? What if the text uses special shaping chars, such as arranging hieroglyphs in cartouches? You basically need a text-rendering aware library to manipulate arbitrary strings.
Couldn't tools that read CSV files scan them first and see which variant best matches the file? The questions arise about which end of line character(s) are used and how double-quotes and commas are handled. There can't be that many ways to escape them, and there are three sets of end of line characters mentioned - just see which one is used (i.e. don't assume only \n if you run into \r\n or \r alone).
The assumption that most software uses is that the import file will be in the same variant of the format as what that tool exports. That seems to be more of a problem than anything else.
> Couldn't tools that read CSV files scan them first and see which variant best matches the file?
Sure, and they sometimes do that if they have to ingest CSVs whose origin they don't control (although not every system implementor cares enough to do it).
But that's still just a bunch of shitty faillible heuristics which would not be necessary if the format was not so horrible.
It also doesn't prevent a human or other system doing:
cat input1.csv input2.csv > output.csv
resulting in a single file containing multiple formats.
Also, what variant is this:
1,5,Here is a string "" that does stuff,2021-1-1
What is the value of the third column?
Is this a CSV file without quoting? Then it's
Here is a string "" that does stuff
Or is it a CSV file with double quote escaping? Then it's
Here is a string " that does stuff
This is fundamentally undecidable without knowledge of what the format it is.
You can decide to just assume RFC compliant CSVs in the event of ambiguity, but then you absolutely will get bugs from users with non-RFC compliant CSV files.
That's true. You could scan the file and see if there are any other types of double quote escaping happening, but if there isn't any that wouldn't help either. It's also negated by the multiple formats in the same file point.
So, yeah. Can't really be done without making too many assumptions that will break later.
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. :)
> 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).
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.
> 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.
If by EOF char you mean Ctrl-Z, Python's `csv` module is at least one case where it will read past the EOF char and you'll get rows of garbage data for any content in the file after that.
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? ;)
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.
To be fair, xlsx came out with Office 2007, so it's not exactly 'new' anymore. Perhaps at this point it's reasonable for 'excel file' to mean the one that's been the default for 14 years?
The formats you describe sound interesting, but in the big corporate/government world the barriers to adoption are high. For example, the software package Cognos which is owned by IBM and connects to database servers. To extract data from it, you have a choice of csv, Excel or pdf. The pdf is only used if you have done a perfect job creating a report in Cognos and don't need to edit/adjust it further in Excel. So really, only 2 output formats.
I realize that this is a specific use case here, but I was on Cognos for years, and then when they started shifting over to Tableau, it wasn't any better.. csv, MS formats, proprietary formats, etc.
Cognos is a BI tool, and the formats you are talking about are for rendering reports, not data. If you're picking data formats based on being able to load & edit them in to Excel, you're already doomed.
The entire article is about replacing CSVs for exchanging data exported from Excel... so why wouldn't he be picking data formats based on being able to load and edit them in Excel? If you're trying to solve this problem in a way that EXCLUDES Excel, you're already doomed. The business world will laugh at you and continue on their merry CSV way.
>The biggest and most thorny problem to solve is the people problem: how do you convince people to stop creating new CSVs when they’ve never done things any other way? Fortunately - for this problem, anyway - most of the world’s business data is born in one of a handful of programs that are owned by an increasingly small number of companies. If Microsoft and Salesforce were somehow convinced to move away from CSV support in Excel and Tableau, a large portion of business users would move to a successor format as a matter of course. Of course, it’s debatable whether that kind of a change is in the best interest of those companies, but I’m cautiously optimistic.
> The entire article is about replacing CSVs for exchanging data exported from Excel...
No, it's not. It's about replacing CSVs for exchanging data. It mentions that CSVs often are the product of someone exporting data from a spreadsheet or doing a table dump, and how just doing that tends to create a ton of problems, but Excel is an example, not the subject matter of the article.
> The business world will laugh at you and continue on their merry CSV way.
The business world pays me a lot of money to teach them not to use CSVs.
If only I could type them on my keyboard. (I think this is a big part of why CSV is the way it is — people want to be able to hand-edit it, or at least hand-produce small test datasets to test the systems on the other end.)
The funny thing is you can type any character on a keyboard. It's the same weird disconnect about "text file formats are human-readable". Either way you need a tool that can render & read the format in question. It does't much matter what format you actually store the data in, because you don't read & write the bites directly.
Yup. I mean, if you're going to go with a text encoding, you might want to, you know, use the features of the text encoding that were put there explicitly for said purpose...
...or you could invent abominations like CSV, TSV, etc. ;-)
Yes. You could get a long way with a text format in which:
-the first line is always a header
-fields are separated by Unit separator characters
-records are separated by Record separator characters
-encoding is UTF8
If you wanted to get fancy you could also have:
-comment lines
-column metadata (e.g. column 0 is an ISO date, column 2 is text, column 3 is an integer)
Both the above could start with a Unicode character unlikely to be used for anything else.
I think that would avoid 99% of the pain of CSV files. The downside is that the use of things like the Unit separator mean that it wouldn't be easy to create/edit manually.
I mean, you'd have to be using a pretty terrible tool for it not being able to handle that, and I suspect if such an approach were to become prevalent, that tool would either fix the glitch or become largely unused.
As with pretty much everything else computing, the world suffers because Microsoft has been dumping terrible tools on it for decades, and people just take their garbage as the way things have to be.
It's not possible to read ahead and chunk with 100% assurance it will always work, but libraries like pandas and R's data.table do a reasonable job of reading in the first X rows, doing some usually correct type inference on the columns, and then read ahead and chunking the rest of the rows.
For what it's worth, I totally agree something like compressed json lines is a better data exchange format, but part of why csv remains as universal and supported as it is is that so much existing data storage applications export to either csv or excel and that's about it. So any ETL system that can't strictly control the source of its input data has no choice but to support csv.
> 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.
Avro is not a lossless alternate encoding of what's canonically a JSON document. Yes Avro supports a JSON encoding, but it's not canonical.
In general though, you're point about Avro being able to be represented as text is valid, and applies to practically any binary formThere'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.at, which is why the whole "but it needs to be a text format" argument is garbage.
> The data-warehouse ecosystem already standardized on Avro as its data interchange format. And spreadsheets are just tiny data warehouses. So why not? ;)
I wish that the data-warehouse ecosystem standardized on anything. ;-)
That said, there are plenty of good reasons why a data-warehouse standard would not be advisable for spreadsheets.
> Yes Avro supports a JSON encoding, but it's not canonical.
To be clear, I'm not talking about using an Avro library to encode data to JSON. I'm saying that when you decode an Avro document, the result that comes out — presuming you don't tell the Avro decoder anything special about custom types your runtime supports and how it should map them — is a JSON document.
Where, by "JSON document" here, I don't mean "a JSON-encoded text string", but rather an in-memory ADT that has the exact set of types that exist in JSON, no more and no less. The sum-type of (JSONArray | JSONObject | String | Integer | Float | true | false | null). The "top" expression type recognized by a JSON parser. Some might call such a document a "JSON DOM." But usually it's a "JSON document", same as how the ADT you get by parsing XML is usually referred to as an "XML document."
Or, to put that another way, Avro is a way to encode JSON-typed data, just as "JSON text", or https://bsonspec.org/, is a way to encode JSON-typed data. They're all alternative encodings that have equivalent lossless encoding power over the same supported inputs.
> I'm saying that when you decode an Avro document, the result that comes out (presuming you don't tell the Avro decoder anything special about custom types your runtime supports and how it should map them) is a JSON document.
Semantic point: it's not a "document".
There are tools which will decode Avro and output the data in JSON (typically using the JSON encoding of Avro: https://avro.apache.org/docs/current/spec.html#json_encoding), but the ADT that is created is by no means a JSON document. The ADT that is created has more complex semantics than JSON; JSON is not the canonical representation.
> By which I don't mean JSON-encoded text, but rather an in-memory ADT that has the exact set of types that exist in JSON, no more and no less.
Except Avro has data types that are not the exact set of types that exist in JSON. The first clue on this might be that the Avro spec includes mappings that list how primitive Avro types are mapped to JSON types.
> Or, to put that another way, Avro is a way to encode JSON-typed data, just as "JSON text", or https://bsonspec.org/, is a way to encode JSON-typed data
BSON, by design, was meant to be a more efficient way to encode JSON data, so yes, it is a way to encode JSON-typed data. Avro, however, was not defined as a way to encode JSON data. It was defined as a way to encode data (with a degree of specialization for the case of Hadoop sequence files, where you are generally storing a large number of small records in one file).
A simple counter example: Avro has a "float" type, which is a 32-bit IEEE 754 floating point number. Neither JSON nor BSON have that type.
Technically, JSON doesn't really have types, it has values, but even if you pretend that JavaScript's types are JSON's types, there's nothing "canonical" about JavaScript's types for Avro.
Yes, you can represent JSON data in Avro, and Avro in JSON, much as you can represent data in two different serialization formats. Avro's data model is very much defined independently of JSON's data model (as you'd expect).
> The first clue on this might be that the Avro spec includes mappings that list how primitive Avro types are mapped to JSON types.
My understanding was always:
1. that the "primitive Avro types" are Avro's wire types, which are separate from its representable domain types. (Sort of like how RLE-ified data has wire types of "literal string" and "repeat literal N times".)
2. that any data that would not be valid as input to a JSON encoder, is not valid as input to an Avro encoder, because its wire types are defined in terms of their a mapping from a set of domain types that are exactly the set of domain types accepted by JSON encoders (whether they're explicitly noted as being those or not.)
Or, to put that another way: an Avro schema is — besides a validation step that constrains your data into a slightly-more-normalized/cleaned format — mostly a big fat hint for how to most-efficiently pack an (IMHO strictly JSONly-typed) value into a binary encoding. Differences between "long" and "int" on the wire aren't meant to decode to different domain types (at least, by default); they're just meant to restrict the data's allowed values (like a SQL DOMAIN constraint) in ways that allow it to be more predictable, and so to be wire-encoded more optimally.
Let me lay out some evidence for that assertion:
• Avro supports specifying e.g. "bytes" vs. {"array": "byte"} — there's literally no domain-type difference in those! But one is a wire-encoding optimization over the other.
• Avro has a "default" property, and this property—as part of the JSON-typed schema—can only take on JSON-typed values. Do you think this is an implementation constraint, or a design choice?
• Avro's enum type's "symbols" array? Once again, defined by (and therefore limited to) JSON string values.
• Avro doesn't implement an arbitrary-precision integer type, even though its wire-encoding for integers would support one just fine. Why? Seemingly only because JSON doesn't have an arbitrary-precision integer type (because JavaScript doesn't have a native BigNum type); nor does JavaScript/JSON have any obvious type to O(1)-efficiently deserialize a BigNum out into. (Deserializing BigNums to strings wouldn't be O(1).) Every other language offers a clean 1:1 mapping for bignums, but JavaScript doesn't, so JSON didn't, so Avro doesn't.
• And why do you think Avro schemas are stored as embedded explicitly-defined-to-be-JSON documents within the root-level record / .avsc file, anyway? This means that you are required to have a JSON decoder around (either at decode time, or at decoder codegen time) to decode Avro documents. Why would this be, if not because the Avro implementation is (ot at least originally was) expected to decode the Avro document's wire types into the JSON library's already-defined ADTs, relying on e.g. having those "default"-parameter values already loaded in in JSON-value format from the schema's decode-output, ready to be dropped seamlessly into the resulting Avro decode-output?
And the biggest knock-down argument I'm aware of:
• Avro "string" doesn't support "\u0000". Why not? Because as you've said, Avro has a "JSON encoding", which specifies one-to-one mapping for strings; and JSON doesn't support "\u0000" in strings. (Just ask Postgres's jsonb type about that.) Since an Avro string containing "\u0000" wouldn't round-trip losslessly between the JSON and binary wire-encodings, it's not allowed in strings in the binary encoding.
Since it is a serialization format, Avro's types are its wire types. However, the primitive types are just a subset of the types that Avro supports.
Based on these comments, my best guess is you got the idea that Avro was for encoding JSON because the schema declaration is encoded in JSON, but that's not nearly the same as the data model. There are some terrible implementations of Avro libraries out there that use JSON as some kind of middleware, but that's not how Avro actually works.
If there's a type model it is derived from at all, it's the Java type model.
"byte" is not a valid type in Avro. There is only "bytes", and the domain model reflects this. You can't work with individual "byte" of a "bytes" object.
Default values are encoded in the schema, and so that does limit what kind of default values you can have, but again this is a limitation derived from the schema being defined in JSON, and how the schema language was defined in general. So your defaults have to be represented as JSON literals, but they don't even necessarily share the type of the JSON literal (e.g. a field defined as: '{"name": "foo", "type": "long", "default":1}' does not have the same default value as '{"name": "bar", "type": "int", "default":1}", because "foo" has a default value that is a long while "bar" has one that is an "int"). Note that "default values" are a property of the type, and only apply to elements inside complex data types. JSON has no such equivalent concept.
Avro's type model does have an arbitrary precision type that doesn't correlate to anything in JSON: the "decimal" logical type.
You aren't required to use a JSON decoder to decode Avro documents, nor are you required to use a .avsc file. The Avro schema file is just the standard way to represent a schema. If you have the schema, you don't need the file. JSON schema files are one of the poorer choices in the Avro design, but you'll notice that the schema is defined the way it is specifically so that it can cover a type model well outside of JSON. You'll also notice the names of types in Avro don't directly correlate to names of types in JSON.
* The \u0000 thing is a bug in avro tools, but there is nothing about the spec that prohibits having \u0000 in your strings.
I feel like in general this is like a retcon exercise, where you've reached a conclusion and are drawing evidence to prove it, while ignoring the information that contradicts. I spoke with Cutting a fair bit when he came up with Avro, and I can assure you, while the schema language does very intentionally use JSON, Avro is not a representation for JSON types.
As someone who's used Avro [0], it's a pain because Avro records must be ordered, but JSON by definition is unordered. Avro's "JSON" format is more of a JSON-like format. At one point, when I was writing a script that would ingest an Avro record and then output a new one, I had to fiddle with things to make Python use an OrderedDict [1] so the new record would be output in the right order.
[0] though the last time I touched it was in 2014, so things might've changed since
[1] as per [0], this was before Python dicts were ordered by default
I'm not sure what you mean by Avro records "must" be ordered. If you mean that the serialization format specifies the ordering of the fields, then yes, that is true, but that's an advantage in terms of compactness and processing efficiency (https://avro.apache.org/docs/current/spec.html#order). If you don't like it though, there are other formats like protobuf and thrift that have no such requirement, at the cost of ~3 bits per field, which can be a comparatively efficient trade off.
The problem is that fields aren't ordered in the JSON spec, so fields being ordered in Avro's dialect of JSON automatically makes it non-standard JSON which makes it difficult to use standard tooling with Avro JSON.
OIC. Yeah, in general the JSON representation is really just for cases where some tool can't handle the binary. Think of it as an escape hatch more than a core feature.
If you're using Avro as a way to do JSON, that's definitely a bad choice. That's why I found the notion of Avro as a way to work with JSON documents as really... odd. (https://news.ycombinator.com/item?id=28222491) It's a decent way to manage data transfers (and I continue to argue a much better one that CSV or JSON), but as a "lossless alternate encoding of what's canonically a JSON document", it's a bad fit.
If you interpret "CSV" as purely comma seperated values then maybe. But in my bubble "CSV" means textfiles that are separated by some separator. Be it tabs, spaces, commas, or any other ASCII character. Some are more usable then others, if you have commas in your data then use tabs. If you have tabs use Form Feed or Record Separator or vertical tabs ... and so on.
Of course this is not always applicable, since you sometimes don't control the format you get your data.
> But in my bubble "CSV" means textfiles that are separated by some separator. Be it tabs, spaces, commas, or any other ASCII character. Some are more usable then others, if you have commas in your data then use tabs. If you have tabs use Form Feed or Record Separator or vertical tabs ... and so on. Of course this is not always applicable, since you sometimes don't control the format you get your data.
When I see CSV parsers like https://www.papaparse.com/ that even try to support comments and empty lines in the format, I wonder if it'd really be that bad to just raise an error on anything that doesn't fit RFC 4180[1], with explanation on where and how the file is corrupted. Push the issue to the writers of such files.
Then I remember the Robustness Principle[2] and I chill a little.
In theory, yes, to be pedantic, but for example, LibreOffice saves its exported CSVs by default as tab delimited. You actually have to manually specify you want commas to get those.
> LibreOffice saves its exported CSVs by default as tab delimited
Maybe it's actually presenting what you've selected last?
It's giving me comma as the default separator, and it's the first option in the dropdown. Tab is the 3rd option.
>> Those other things have different names like TSV
That depends on the writer. I've gotten what should be named PTVs (pipe-terminated values) as CSVs. I can understand how it happened. If the underlying software outputs PTVs, you don't want to bother converting that because you're working in a legacy language that's a pain to work with (the type where identifiers can't be longer than 4 chars), and you want the user to be able to double-click on it and have it open in a spreadsheet without prior configuration, you just push the issue to the reader of the file, since by tradition readers are already quite tolerant of format differences...
Of course, there'll always be the case where the reader is simply not tolerant enough, like when the escaping syntax differs. There doesn't seem to be a way to get LO Calc to interpret "foo|bar\|baz|" as cells "foo" and "bar|baz", for example.
And then Excel imports them all using heuristics when you select "Type: CSV". So you'll never train anyone on the demand end of these documents that they're called anything besides CSVs.
Technically true, but like I said "CSV" is more a term for human readable data, with some delimiter in them. Maybe it's a comma, maybe not. In every case you need someonne to look at it. If you want a machine-machine data protocol you can use XML or JSON if it needs to be somewhat human readable.
It seems quite common in some European countries to use semi-colons as the delimiter instead of commas (because they use commas as the decimal separator?), adding a new level of fun to parsing. In Easy Data Transform we count the number of commas, semi-colons, tabs in the file to make an educated guess as the delimiter (which the user can override).
Semicolon-separated "CSV" is a quirk of Microsoft Excel, which they have never fixed. I figure Microsoft would prefer that people use XLS instead of CSV.
"Self-describing formats like JSON Lines are big... but when you compress them, they go back to being small."
For CSV file replacements, I'd expect something like "one JSON array per line, all values must be JSON scalars". In that case, it's not much larger than a CSV, especially one using quotes already for string values.
But this demonstrates the problem with JSON for CSV, I suppose. Is each line an object? Is it wrapped in a top-level array or not? If it is objects, do the objects have to be one line? If it is an object, where do we put field order? The whole problem we're trying to solve with CSV is that it's not a format, it's a family of formats, but without some authority coming in and declaring a specialized JSON format we end up with a family of JSON formats to replace CSV as well. I'd still say it's a step up; at least the family of JSON formats is unambiguously parseable and the correct string values will pop out. But it's less of a full solution than I'd like.
(It wouldn't even have to be that much of an authority necessarily, but certainly more than "The HN user named jerf declares it to be thus." Though I suppose if I registered "csvjson.org" or some obvious variant and put up a suitably professional-looking page that might just do the trick. I know of a few other "standards" that don't seem to be much more than that. Technically, even JSON itself wasn't much more than that for a lot of its run, though it is an IETF standard now.)
> But this demonstrates the problem with JSON for CSV, I suppose. Is each line an object?
How is that not a problem with every data serialization format? It does me no real good if I have an XML schema and a corresponding file. If I don't know what those elements and attributes represent I'm not really any better off.
It's not like JSON or XML can meaningfully be marshaled back into objects for use generically without knowledge of what is represented. There are generic JSON and XML readers that allow you to parse the data elements sure, but so, too, do generic CSV readers like C#'s CsvHelper or Python's csv. In all cases you have to know what the object turns into in the application before the serialized data is useful.
And, yes, CSV has slightly differing formats, but so does JSON. Date formats are conventionally ISO 8601, but that's not in the spec. That's why Microsoft got away with proprietary date formats in System.Text.Json. XML isn't really any better.
Sorry, that should be System.Runtime.Serialization.Json. System.Text.Json is the newer class that replaced it.
In .Net Framework 4.6 and earlier, the only built-in JSON serializer in the .Net Framework was System.Runtime.Serialization.Json.DataContractJsonSerializer.
You can still see it. If you're on Windows 10, run Windows Powershell v5.1 and run:
Microsoft didn't fix their weird JSON serialization until quite late. They may have back ported it to the .Net Framework, but they've deleted that documentation. Powershell v6 and v7 include the newer classes that are properly behaved. This is why Json.NET used to be so popular and ubiquitous for C# and ASP applications. It generated JSON like most web applications do, not the way Microsoft's wonky class did. Indeed, I believe it may be what System.Text.Json is based on.
Oh that one - yeah I've always steered clear of DataContractJsonSerializer. Never understood why they did it so weird.
To be fair, RFC 3339 wasn't even published back when this class was implemented (in .NET 3.5) so I guess they just went with whatever worked for their needs. ¯\_(ツ)_/¯
I'd be quicker to believe that it's because 2007 was still in the middle of Steve Ballmer's Microsoft, where embrace-extend-extinguish was their de jure practice.
I have wondered about a file format where a parser could be specified for [at the start of] each line. You could even have different json parsers with different well-characterized limits and relative speeds. Formats could change over time on a line-by-line basis, without being locked into a full-file IDL or similar.
To be fair it's not an objectionable format. Using line breaks to separate objects makes it streamable, and you don't need to enclose the whole thing in an array to make it a valid JSON document.
That is not quite a CSV replacement. I use it for things with objects and stuff all the time. To be a CSV replacement you really need to add that each line must be a JSON array, and that it can only have scalars in it (no sub-arrays or objects). That would be a decent enough replacement for CSV itself. Not perfect, but the CSV "standard" is already a nightmare at the edge anyhow and honestly a lot of it can't be fixed anyway, so, this is probably as good as it could get.
> that it can only have scalars in it (no sub-arrays or objects)
I see CSV files that contain JSON arrays/objects in their fields all the time. Mainly from exporting Postgres tables that contain json/jsonb-typed columns. Are you saying that these aren't valid CSVs?
JSON Lines is a specified format that answers those questions. https://jsonlines.org/ Seems like it qualifies to the level of authority you're requiring.
Commas, quotes, colons, newlines, and hyphens (JSON and YAML separators) can occur in content as well. How is that any different from separators occurring in csvs?
In practice, I used TSVs a lot, as tabs do not usually occur in most data. Alternatively, you could use pipes (|) or control characters as field or row separators.
CSV does not have date or currency types. Those are an Excel conventional microformat overlaid on top of CSV.
The semantics of CSV — how it decodes, if you don't have an application making additional configured assumptions or using heuristics to detect microformats — is just N columns, one of header names, N of raw text cells. Nothing more, nothing less. No schema defining what a column's type is.
You misunderstood my point. CSV, for all its faults (and I am intimately aware of them), is ubiquitous. You're not going to replace a widespread standard with something that only offers a tiny incremental improvement. Ubiquitous is a feature, and generally trumps all other features.
If you want something better than CSV to take off, at the very least it needs to solve the common pain points of CSV. JSON doesn't - all it brings to the table is the ability to distinguish number vs text. That's a yawn.
JSON solves tons and tons of pain points around CSV... for implementing anything that reads/imports/normalizes CSV data. Just the fact that CSV has multiple standards around the quote/escape/separator/NULL character-sequences, with the choice not get encoded into the document, means that any format that doesn't have that problem is instantly 100x better in terms of not experiencing a disaster of data-garbling-on-import.
> If you want something better than CSV to take off...JSON doesn't...
Agreed. I don't ever see something better than CSV taking off (having worked on this problem for better part of a decade) unless you could get to the point where you've got a largely similar notation, but a richer semantic grammar powered by something like Schema.org.
In other words, a strongly typed CSV with a community backed schema. I think GPT-3 and the like are a big wild card here. I can't describe how, but I expect in 20 years we will be using something better for storing data and will look back and saw "CSV used to be the best but then GPT-N _____________".
> If you want something better than CSV to take off, at the very least it needs to solve the common pain points of CSV. JSON doesn't - all it brings to the table is the ability to distinguish number vs text. That's a yawn.
It brings the ability to parse in parallel - that's a big deal. And while number vs text might not be a huge difference in theory, in practice it eliminates what, 95% of real-world parse problems?
At least in my experience, I don't see a lot of trouble with number vs string in CSV data. You convert the string to a number or you don't. The pain points are usually dates or currencies - the same problem I usually have with JSON, because there's no standard format.
I think you could fix most of the pain of CSV simply by adding a second header row which defines the type of the column, using a common vocabulary of types. TEXT, NUMBER, ISO8861-DATE, etc.
I envy your life of pristine CSVs and compatible implementations, where JSON doesn't solve any problems. But CSVs have a broad range of uses, and there are, in fact, plenty of implementations that do not follow RFC 4180, and even if everything did there would still be issues like the header row being optional (without an in-band way of telling). JSON is, in practice, much more well-defined.
On text vs numbers, at least some widely-used software (e.g. R, Excel) will try to guess for you. It should be obvious how this might cause problems. Maybe one should turn auto-conversion off (or not use things that don't let you turn it off) and specify which columns are numbers. Some datasets have a lot of columns, so this can be a PITA, even if you do know which ones should be numbers. But the bigger problem is if you have to deal with other people, or the files that they've touched. There are always going to be people that edit data in excel, don't use the right options when they import, etc.
> At least in my experience, I don't see a lot of trouble with number vs string in CSV data.
I definitely had my fair share of trouble with locale-defined number format. Importing a column where a thousand is spelled "1.000", any integer between 1000 and 999999 would be wrongly parsed as a float between 1 and 999, while for any other number (like "0,1" for one tenth, "1.000.000" for a million, "1.000,56" for a thousand euros and change) the parser would give up and keep the string.
I usually have had more luck importing as text, then doing some string replacement of separators before finally converting to number.
> I don't see a lot of trouble with number vs string in CSV data. You convert the string to a number or you don't.
The trouble is that a lot of existing software (not just Excel) won't properly roundtrip text that looks like numbers and will e.g. strip the leading zero from phone numbers, or worse, change the last digit to make a number that exists in floating point.
> The pain points are usually dates or currencies - the same problem I usually have with JSON, because there's no standard format.
Hmm, I've never had a problem with ISO8861 dates - a string is either a valid date or not, it's very rare for someone to "accidentally" put data in ISO8861 format when it's not actually a date. Dates without timezones can cause problems, but that's more of a semantic issue than a serialization issue. What are the problems that you get?
I can see how currencies could be an issue with the lack of a standardised fixed-precision type. But in my experience they're an order of magnitude less common than issues with phone numbers, postal codes, and the like.
> I think you could fix most of the pain of CSV simply by adding a second header row which defines the type of the column, using a common vocabulary of types. TEXT, NUMBER, ISO8861-DATE, etc.
I'm sure you could. But at that point you're defining a new and incompatible format - you have to make it incompatible, or otherwise people will open these files with a tool that doesn't understand the header format and you're back to square 1 - so you'll pay all the same adoption costs as a completely new format. So it make sense to fix all the issues we can - and a format which can be split and parsed is definitely a major improvement for many use cases.
>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
Yes, this is a major pain. It can be avoided by using Tab separated value (TSV) files, which don't use escaping. But then you can't store Tabs or carriage returns in your data. Also there is no way to store metadata in TSV.
JSON is far from ideal for storing 2D data tables as it is a tree. This means it is much more verbose than it needs to be. The same is also true for XML.
ASCII has special delimiters 0x1E Record Separator and 0x1F Unit Separator to avoid conflicting with values, but they have never gained widespread adoption.
I worked on a project that used these in a proprietary file format. It was actually quite nice. There was no escaping required because they simply stripped those characters out of the actual content.
More generally than editor support, how is the average person expected to type them? I could easily add them to my keyboard, but if "editor support" turned out to mean "Edit > Insert > Record Separator Character" and I was now bound only to editors that support this, absolutely nobody is going to bother. They specifically need to be trivially typable in exactly the way commas and newlines are.
Realistically I don't think anyone (at least on HN) would presume that editor support would be so weirdly obtuse, but it's not exactly well-defined here, either.
I'm mostly just thinking out loud, here, not really critiquing anyone.
which modifier would that be, Alt/Ctrl or Cmd? Also there is a chance that this combo will not work on some platforms, some may already be in use as a hotkey.
Actually, there is already a standard way for someone to type these with the control key. To be specific, they are:
FS: ^\ GS: ^] RS: ^^ US: ^_
Since these delimiters are not much used, on many terminals ^\ is mapped to send SIGQUIT. Use 'stty quit undef' and you can then type this at the keyboard. This works on Linux and OS X, and likely most other systems. Note that with most keyboards you are also using shift for RS and US. For emacs, prefix with C-q so that it inserts the literal.
Other posters have noted that they have ASCII separators recently, I have as recently as 2018 internally at a FAANG. So they are not completely unknown in the wild.
The other advantage not being discussed here is that once you have a csv parser, it can be trivially generalized to use any character as a delimiter, and you can usually find a character that isn't found in the data you're working with unless you're working with binary data. I work on a legacy system where passing delimited files around is usually the integration method of the day, and if you can't delimit with commas (common) then you just delimit with tabs. If you can't delimit with tabs or commas, then delimit with pipes, which are rarely if ever used outside of code. Or use tildes, or grave accents, or...
Doing this avoids quoting, which avoids an entire class of problems. And yes, if you're passing multi-megabyte files around on a regular basis, or transferring giant blobs of text or binary data, or looking for a storage format with metadata then csvs are an absolutely awful choice, but as with any problem you pick the right tool for the job, and csvs are a great tool when you want to easily move some structured alphanumeric data around in a format that's trivial to parse in any language and widely supported.
>it can be trivially generalized to use any character as a delimiter
But then you have the additional problem of trying to work out what the delimiter is. You can take an educated guess, but this isn't going to be 100% reliable.
This comes back to "picking the right tool for the job". If you're ingesting csvs/tsvs, you're expecting a given format anyway. If you expect the wrong delimiter, the worst thing that happens is you detect that the file has the wrong number of columns and fail out. Even if you're using a more structured format, if you ingest a file containing the wrong dataset, your process will still fail because the correct fields aren't there. No matter what your format is, you can't recover from somebody sending you the wrong file.
> means that there's no way to parallelize CSV processing, because there's no way to read-ahead and "chunk" a CSV purely lexically.
There's a subset of CSV that forbids escapes that is super fast to parse. All fast CSV parsers I'm aware of take advantage of this subset. I try to never ever publish a CSV that has quotes, and always aim for a more restrictive grammar that is cleaner, better thought out data.
>CSV is actually horrible at parse-time vs. other data formats
I find this really hard to believe given it's a simple enough syntax. And parsing is usually not the limiting factor, usually fast enough to not be noticed alongside interpreting or loading the source data. Every (much more sophisticated) compiler I can think of uses a linear parser based on this assumption.
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.
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.
> 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.
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.
I find files where the exact type of whitespace used is meaningful to be a sick joke.
Not all editors make it easy for you to use a tab character.
If only there was some other solution that would not require you to use a character that a lot of editors refuse to insert or even try to convert to spaces...
No, SQLite's dynamic data types would silently coerce data just like opening a CSV directly with Excel does.
The advantage of CSV is that it's as accurate as your plain text representation of your data can be. Since binary data can be represented by character data, that's 100% accurate. As soon as you introduce a storage format that has made assumptions about the type of data being stored, you've lost flexibility.
SQLite is not intended for data serialization. It's intended for data storage to be read back by essentially the same application in the same environment.
> No, SQLite's dynamic data types would silently coerce data just like opening a CSV directly with Excel does.
SQLite's "dynamic data types" coerce data on input, not output. Once the data is in sqlite the way you wanted it, excel has no interpretation to perform, except insofar as really really wanting dates.
> The advantage of CSV is that it's as accurate as your plain text representation of your data can be.
Yeah nah.
> SQLite is not intended for data serialization. It's intended for data storage to be read back by essentially the same application in the same environment.
Data analysis, exchange, container, and archiving, are literally all use cases listed as "appropriate uses for sqlite" in "Situations Where SQLite Works Well" on the official sqlite website: https://www.sqlite.org/whentouse.html
How is a csv not the most accurate representation of the data? If you trust the other agent encoded it properly in the db, then sure. Your flippant dismissal was inappropriate in tone and detracted from the rest of your opinion.
Cockiness tells me that you’re insecure about your knowledge, not that you know more than GP.
> How is a csv not the most accurate representation of the data? If you trust the other agent encoded it properly in the db, then sure.
The idea that a CSV would be more likely to be correctly encoded than a DB is hilarious, thanks for the laugh. But that you were confident enough to seriously put it in writing shows how little experience you have with CSV.
Yep, you got me. I'm actually a trapeze artist moonlighting on these forums.
A CSV file represents the exact digits that are to be stored. You have unlimited precision. You could even store irrational numbers, equations, or mix data types in a column. OTOH, you have to make sure the delimiting character is not present in the data - that can be pretty easy, if you use the field-separator character in ASCII, or even just a \t. I've even seen people terminate fields with ^8675309| because they felt confident no data would contain Jenny's number.
A database, like Excel, likes to conform data. This is usually awesome! But sometimes, it's not.
"A bird, like homo sapiens, has two feet" doesn't mean that birds are humans or humans are birds either. It means that in this respect, birds and humans are alike. Which is what GP meant: With respect to conforming data, databases behave just like Excel.
Language note: I think it's the fact that there are two commas around the inserted-clause [terminology?] ", like Excel," that does it. If there were only one comma, before or after "like Excel" it would read the way you read it. Can be tricky for non-native (and sometimes also native) speakers.
It's strictly dependent on the table definitions you use. A column with no type (the simplest way to use them) will take data in any of the natively supported types—whatever you actually insert in your SQL statement—and will not attempt any translation.¹
If you do add column types to your tables as in traditional databases, then of course you should expect some varying behavior based on that.
I can imagine some potential for issues from people expecting to insert exact decimal numbers and choosing floating-point by accident, perhaps, or using integer types for actually-text numeric ID fields. If you go all-strings on input with no column affinity, then you'll get the same strings on output, though, so that's isomorphic to good-CSV.
¹ https://www.sqlite.org/datatype3.html – §3.1 “If the declared type for a column contains the string "BLOB" or if no type is specified then the column has affinity BLOB.” + §3 “A column with affinity BLOB does not prefer one storage class over another and no attempt is made to coerce data from one storage class into another.”
Excel's default insistence on trying to coerce the data into what it expects the data to be is the source of many of the problems of people using CSV. Excel will even ignore quotes around values if it thinks that the value inside might be a number. I often work with CSV files that contain 17 digit IDs that use numeric characters "01233998742021925" which Excel will convert lossily into scientific notation rather than leaving it as a string. There are ways to override that but they are tedious and people don't do it by default.
I'd be satisfied if Excel was just a little less aggressive about it's data coercion on import.
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.
> 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.
CSV uses decimal representations of numeric data, which means you are getting 3.5 bits of data for every 8 bits of storage space (and that's assuming you are using a reasonably compact text encoding... if you are using UTF-16, it's 16 bits). Using a binary representation you can store 8 bits of data for every 8 bits of storage space.
CSV uses a variety of date-time formats, but a prevalent one is YYYY-MM-DDThh:mm:ss.sssZ. I'll leave it as an exercise for the reader to determine whether that is as compact as an 8-byte millis since the epoch value.
CSV also requires escaping of separator characters, or quoting of strings (and escaping of quotes), despite ASCII (and therefore UTF-8) having a specific unit separator character already reserved. So you're wasting space for each escape, and effectively wasting symbol space as well (and that's ignoring the other bits of space for record separators, group separators, etc.).
Then there's floating point data...
Honestly, this is a silly argument. CSV is, by design, a format that trades space for other factors. It should not be debatable whether it is a compact format or not.
> CSV uses decimal representations of numeric data, which means you are getting 3.5 bits of data for every 8 bits of storage space (and that's assuming you are using a reasonably compact text encoding... if you are using UTF-16, it's 16 bits). Using a binary representation you can store 8 bits of data for every 8 bits of storage space.
XML, JSON, and YAML all have this issue, too.
> CSV uses a variety of date-time formats, but a prevalent one is YYYY-MM-DDThh:mm:ss.sssZ. I'll leave it as an exercise for the reader to determine whether that is as compact as an 8-byte millis since the epoch value.
This is also identical to XML, YAML and JSON.
And I know what you're about to argue, but JSON's datetime format is not in the spec. The common JSON datetime format is convention, not standard.
> CSV also requires escaping of separator characters, or quoting of strings (and escaping of quotes), despite ASCII (and therefore UTF-8) having a specific unit separator character already reserved. So you're wasting space for each escape, and effectively wasting symbol space as well (and that's ignoring the other bits of space for record separators, group separators, etc.).
This is also identical to XML (escaping XML entities, sometimes having to resort to CDATA), YAML (escaping dashes) and JSON (escaping double quotes).
All you've shown is that CSV has the same limitations that XML, YAML, and JSON have, and those three formats specifically designed and intended for data serialization. Yes, the other formats do have other advantages, but they don't eliminate those three limitations, either.
This is for data serialization, which means it's going to potentially be used with data systems that are wholly foreign separating great distances or great timespans. What data serialization format are you comparing CSV to? What do you think CSV is actually used for?
Are you arguing for straight binary? You know that CSV, XML, YAML and JSON all grew out of the reaction to how inscrutable both binary files and fixed width files were in the 80s and 90s, right? Binary has all sorts of lovely problems you get to work with like endianness and some systems getting confused if they encounter a mid-file EOF. If you don't like the fact that two systems can format text differently, you're going to have a whole lot of fun when you see how they can screw up binary formatting. Nevermind things like, "Hey, here's a binary file from 25 years ago... and nothing can read it and nobody alive knows the format," that you just don't get with plain text.
Yes, you do end up with a wasted space, but the file is in plain text and ZIP compression is a thing if that's actually a concern.
Yes. Though to their credit, some of those work with her numbers, which at least gets you 4 bits out of every 8 bits.
> And I know what you're about to argue, but JSON's datetime format is not in the spec. The common JSON datetime format is convention, not standard.
I'm not sure what argument you thought I was making, or why that comment is relevant.
> All you've shown is that CSV has the same limitations that XML, YAML, and JSON have, and those three formats specifically designed and intended for data serialization. Yes, the other formats do have other advantages, but they don't eliminate those three limitations, either.
I'm not sure what your mean by "eliminate", or why you think it matters that there are other formats with the same design trade offs.
> This is for data serialization, which means it's going to potentially be used with data systems that are wholly foreign separating great distances or great timespans. What data serialization format are you comparing CSV to? What do you think CSV is actually used for?
CSV is used for a variety of purposes. The context of the article is using it for data transfer.
The claim was that it was a compact format for data transfer, which is demonstrably not true.
> Are you arguing for straight binary? You know that CSV, XML, YAML and JSON all grew out of the reaction to how inscrutable both binary files and fixed width files were in the 80s and 90s, right?
I'm not sure what "straight binary" means to you. JSON is, for the most part, a binary encoding standard (just not a particularly good one).
You've got the heritage a bit wrong, as XML was not originally designed for data transfer at all. It was an attempt to simplify the SGML document markup language, and the data transfer aspects were subsequently grafted on. JSON & YAML have a slightly more complicated heritage, but neither was intended as a data transfer format. They've all been pressed in to service for that purpose, for a variety of reasons, that can charitably described as tactically advantageous but strategically flawed.
> Binary has all sorts of lovely problems you get to work with like endianness and some systems getting confused if they encounter a mid-file EOF.
I don't know how to break this to you, but text formats can have endianess (in fact, insanely UTF-8 does!), and systems being confused and whether they are at EOF as well.
> Yes, you do end up with a wasted space, but the file is in plain text and ZIP compression is a thing if that's actually a concern.
Wouldn't ZIP be a binary format, with all the problems and concerns you have with binary formats?
So to summarize what you are saying... "CSV is a compact format because you can compress it if you are concerned and all the space it wastes".
Would it be fair to say then that any binary format is a text format because you can convert the binary into a text representation of the data? ;-)
> > 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.
For most natural text-only data, .csv.gz is probably close to the limits of what we can do. For numerical or categorical text containing patterns or structure, I agree that better formats may exist (e.g. H.264 for CSV files containing video frames). Not sure how a more general format will solve anything you couldn't already do by converting to a more specific format and tarring everything afterwards.
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.
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.
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.
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.
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.
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.
> 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.
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.
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.
If you're just going to quote me out of context and not bother responding to the actual substance of my comment (which was a high level but concrete comparison of two different CSV parsers, demonstrating an important qualitative difference with respect to csv parsers solving problems), then please, don't bother responding.
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.
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
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.
In some incredibly pedantic sense, sure. But the difference is that every computer comes with what a human needs to read a CSV file. That's not true of an SQLite database.
Sure, could be. In about the same sense that it could be that every computer comes with a free and open source operating system. Could be; it's not theoretically impossible -- but...
How about an app that opens an SQLite DB so that strings and numbers can easily be edited, and then on file save, SQLite's internal pointers and markers are updated ? Maybe an Emacs mode ?
It's worth mentioning that SQLite is committed by its publishers to be backward-compatible out to 2050 and is the recommended by the Library of Congress as a safe long-term storage format (as are XML, JSON, and CSV). https://www.sqlite.org/locrsf.html
For single tables a database is probably overkill, but it's nice to have around when you need something reasonably powerful without being overly complex or hard to get started with.
I suppose if I was to recommend some "safe long-term storage formats" I, too, would choose things that are readable in plain ASCII and/or have open source roots.
Shoutout to the Library of Congress for doing the real heavy lifting here?
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.
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.
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.
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.
> All I need is file I/O and the ability to split strings.
...until there is a newline inside a field.
The moronic quoting mechanism of CSV is one half of the problem; people like you, who try to parse it by "just splitting strings" is the other half. The third half is that it's locale dependent and after 30+ years, people still don't use Unicode.
As printed above, I'm not writing software where that would be a problem. In general if I was writing a commercial application or something in production where I don't control all the inputs I would agree 100%, but I'm lucky to not have those problems. I could use a bunch of libraries and additional code to try to catch non-existent errors I don't have, or fix my actual problems and move on.
I appreciate the perspective though for sure. I'm guilty of the same thing on HN, where I assume people have the same uses as me when they're writing code that has to be extremely robust or blazingly fast.
You've assumed an awful lot about my use cases. The data I deal with in .CSV form is always pre-processed and doesn't have any of the minefield occurrences you've mentioned. There can't be a newline or anything like that in an input. In my decade of using .CSV files daily, I've only had one tertiary system where that is a problem.
Also, when doing interactive work, it's a bit different than writing production IT software.
Define parsing. I'm still going through GB of data in thousands of files and building complex reports, and data structures for scientific analysis. Just because I don't need thousands of lines of code to navigate edge cases doesn't mean I'm not parsing .CSV files.
The real advantage of CSV, in my mind, is that if the CSV is valid and normal then it's going to be a rectangular dataset (ignoring semantics within the dataset).
If I import JSON data I have no idea what shape the result will be in, and it requires a separate standard to let me know about columns and rows and validation can get complicated.
CSV is that way too. There's nothing that says each row has to have the same number of what maps out to columns, or anything that tells me what those columns really represent (there's no schema). You could use the first line of the CSV doc to say what each tuple is named, but that's not a standard or anything. And without a schema, it certainly could be easy to lose the metadata of the info the CSV file is trying to represent. Is this column just a bunch of numbers or a date format? (for example). CSV is OK for importing and exporting data across systems that know what the format is without the help of a schema, but anything else and you run into a pile of edge cases. Even using a CSV file to import into a spreadsheet works usually but context is often lost.
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.
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.
Every few years an article like this pops up. I find it tiring - because they are primarily from a software engineer's viewpoint who is probably trying to write a parser and needs to handle the edge cases. As a data scientist, I receive and process around 75GB of CSV every day - of course I don't process it manually. Our processes have been running a few years now and millions of dollars of revenue rides on it. I don't see any need to retire anything, and am pretty sure our processes will be working and running 10 years from now. The only thing which will change are some statistical models which make use of this data.
So you can constrain what type of CSV you will allow and if this happens it will bail. It's that simple. There is nothing wrong with having additional constraints on top of just saying it must be "CSV" especially in these scenarios.
I'm in a similar situation, we've been using CSV for over a decade to move billions of dollars worth of product each year. It just works.
> I'm pretty sure most devs are going to use whatever CSV library that comes with their language. When that breaks, it's generally not a simple fix.
Call me a yak-shaver, but in every language I've worked with I've written my own csv parsing library when I needed one.
It's such a trivial thing for the majority cases (varying of delimiters, line-endings, ascii/uft8, quoting, escaping, and embedding of delimiters/line-endings) that it takes barely no time at all after you've done it once in another language. Of course there are edge cases and special cases depending upon specific workloads, but if your team has it's own parser (which is a small amount of obvious code) then it does indeed usually become a simple fix.
Sounds good using someone else's library, but below a certain complexity it's rarely worth it in the medium to long term except for when doing proof of concept or demo code, or if the domain space is complex.
We've got at least a few dozen customer integrations that parse CSV-ish files, and they all have a custom parser. Many of these have been chugging for over a decade, sending "mission critical" data back and forth.
It's dead simple to whip up, and we can easily tweak it to whatever the customers software spits out, like one field suddenly being UTF-8 encoded in an otherwise Windows-1252 file.
That's pretty much how I feel as well. People that complain about all the variations in CSV "formats" out there aren't wrong but in practice, for me anyway, most of the files I work with in any given situation come from the same source or a small set of sources and use the same variant.
If/when I get a data source introduced into my workflow that differs from this variant I come up with a routine to normalize it, integrate that into my workflow and move on.
Pretty much the opposite here. Dealing with lots of different formats from different sources, often once off. There's all kinds of crazy things people will call "data" (CSV 'variants' are the tip of the iceberg).
100%. Don’t fix if it ain’t broken. This is the pragmatic approach that often comes across to new devs as “unsexy”. I got some news for “unsexy” software - it works, and it brings the revenue.
Yeah - I used to lead a department that would process somewhere around 10TB of CSV formatted data per day.
The edge cases are a hassle but they don't become less of a hassle from a business perspective by switching to json or really any other format. We tried an experiment of using more json and eventually gave it up because it wasn't saving any time at a holistic level because the "data schema" conversations massively dominated the entirety of the development and testing time.
Obviously being able to jam out some json helped quite a bit initially, but then on the QA side we started to run in to problems with tooling not really being designed to handle massive json files. Basically, when something was invalid (such as the first time we encountered an invalid quote) it was not enjoyable to figure out where that was in a 15GB file.
That said, I fully concur with the general premise that CSV doesn't let you encode the solutions to these problems, which really really sucks. But, to solve that, we would output to a more columnar storage format like Parquet or something. This would let us fully encode and manage the data how we wanted while letting our clients continue working their processes.
What I would really like to see is a file format where the validity of the file could be established by only using the header. E.g. I could validate that all the values in a specific column were integers without having to read them all.
> "data schema" conversations massively dominated the entirety of the development and testing time.
Agreed. JSON let's me know something is a number. That's great, but I still have to check for min/max,zero etc. A string? That's great, but I got to check it against a set of enums, and so forth. Basically, the "types" JSON gives you is about 20% of the work, and you're going to have to parse things into your own types anyway.
> What I would really like to see is a file format where the validity of the file could be established by only using the header.
Are you saying something like a checksum so not only is a schema provided but some method to verify that the data obeys the schema?
If you're talking about just some stronger shared ontology, I think that's a direction things will go. I call this concept "Type the world" or "World Wide Types". I'm starting to think something like GPT-N will be the primary author, rather than a committee of humans like Schema.org.
Honestly with the schema thing I'd probably be fine with either/or!
A checksum would be crude and user-hostile, only being able to say "you did it wrong" but not really good at tell you what it means to do it right.
If I understand the concepts correctly then it seems like a shared ontology could potentially solve the problem in a non-hostile way.
Plus, it makes me happy because I feel like types are a real-world problem, so it is always nice if the type system could enforce that real-world-ness and all the messiness that comes along for the ride.
We looked at it and there were a few problems we had with where it would force us to put VMs that we just weren't super comfortable with due to the in-process-ness.
More a byproduct of decisions made 5 - 7 years ago when the company was in raw startup mode versus a more mature roadmap.
Really appreciate the insight from you and the GP here. I have been struggling with data format decisions around a personal project that will only be used by a few people, being unsure about the extent i should try to make it bulletproof (but harder to maintain and modify) or just keeping it simple (but primitive). It's helpful to see an experienced professional perspective showing that you can fall into a tooling rabbit hole at any scale.
The whole point of CSV is that it's simple. You don't need and special libraries. If you can write hello world to a file you can make a CSV and more often than not it'll just work. Yes somethings, like DNA, shouldn't be CSV files. But don't blame the tool for bad craftsmanship. There are more things that it works for than doesn't.
As a side note dates should be ISO 8601
CSV will never go away. But surely we can build better systems to handle all the complexities and variability in CSV? If we can handle image recognition with all its complexities, we can handle the more constrained variety in CSVs. There are only so many variations to CSV files, and perhaps a better preprocessor is the answer here versus asking for the near-impossible task of "retiring" CSV.
I find JSON “array of arrays” to be a better solution while also remaining human-readable. Especially when formatted as one array per line, analogous to CSV.
Many JSON Parsers already have an incremental forward read mode (think SAX-style parser if you are familiar with XML parser styles) where you can ask for each array inside that array of arrays one at a time as it reads them. If something unexpected happens at the end of the large outer array such as syntax error you can decide at that point if you rollback what you've already read/operated on or not.
JSON.Parse() in the browser isn't that sort of parser obviously, but there options in many languages such as both major .NET JSON libraries (Newtonsoft and System.Text.Json) have ways to parse that way. Similar examples exist in other languages too.
Yes, Python's bundled json module does not support that style of parsing. I saw several suggestions in a quick search to try NAYA [1] if you need that in Python (it's a no dependency Python 3 library) or one of the C-backed wrapper libraries NAYA mentions at the bottom of its Readme if you can afford native dependencies.
CSV was a thing long before I was born, so I'm not privy to how it came about. But at least in day-to-day work, the single biggest drawback of CSV in my experience is the fact that the comma and most of the other common delimiters occur regularly in real data, forcing all of the cumbersome escape sequences. To say nothing of someone misplacing a quote somewhere and throwing off the cell count.
So, question to the greybeards: when this format was coming about, why didn't we use one of the dedicated Data Structure separator control codes (e.g. File/Group/Record/Unit separator) that were part of the ASCII standard?
Agree that the commma delimiter is a pain, as it is a frequently used character in the content. Then adding escape-type chars or quotes around fields just makes things more and more messy.
Consider also that IBM EBDCIC and other non-ASCII character sets were (and are) in common use, so C0/C1 may not have made sense 'back in the day'
Csv is only one form of delimited files. Tsv or Tab Separated Values is yet another. If you have either field delimiters or row delimiters (new line) in your data, change your delimiters and process appropriately.
But commas can be seen, edited, and typed with ease in any text editor.
As per comment above: your CEO, SWE, or secretary can all use or contribute to a csv file. And using an easily recognizable and typeable separator has proven to be worth the downsides.
> why didn't we use one of the dedicated Data Structure separator control codes (e.g. File/Group/Record/Unit separator) that were part of the ASCII standard?
Because you can't see them. CSV is, at its core, a text format. Using FS/GS/RS/US would effectively make it a binary format.
Also, what happens if one of those bytes appears in data?
The simple fact is you will never be able to come up with a text-based format that can handle all possible values without escape sequences, quoting, or length-encoding. And really, that's not that big a deal. It just means you have to write a simple
state machine instead of using regex or your language's equivalent of split().
XSLX covers a lot of ground in this area, works with the market leading spreadsheet program, but there are also great libraries to encode and decode them in Python, Java and other languages.
I would agree, csv is probably not the way to go. But try to go implement a Parquet reader and then see how nice processing a streaming gzipped csv file can be.
593 comments
[ 3.3 ms ] story [ 342 ms ] threadXkcd: https://m.xkcd.com/927/
CSV is a format more for humans and less for machines, but that is the use case: a format that is good enough to be compiled by humans and read by machines. At the moment there aren't many alternatives.
The best that can be said for its simplicity is that it's easy to write code that can dump data out in CSV format (and to a lesser extent, it's easy to parse it, though watch out for those variants). This is not a really strong argument, most everyone is going to use a library for serialization, there's no reason to write your own unless it's for learning.
Of course, excel will make just as much of a mess - when the client hands back their sheet with all of their UPCs expressed as exponents and æ€ ligatures jammed next to every apostrophe, there’s no guessing as to what happened.
In theory, it’s a great and simple format.
In practice, it causes much pain.
Until the CSV fields contain commas themselves. Even if fields are surrounded by "".
Turns out when you have data that contains both commas and quotes things get screwey real quick. You could have quoted data like:
123,ABC,”,””,456
Where ,” is column data.
I think that standard method is to double quote the field, but the dump sure wasn’t doing that for me, plus what happens when the data is something like abc””,?
That is badly formatted CSV, assuming ” is a double-quote. Double-quote (") is how you escape commas, so the 3rd entry will make a parser barf, since there is no close quote.
It should be 123,ABC,"","",456
> Turns out when you have data that contains both commas and quotes things get screwey real quick.
Not really. It's pretty logical. If the entry has even one comma, put quotes around it. If it also has quotes, double each of them. Otherwise don't worry about it.
> what happens when the data is something like abc””,?
abc"", becomes "abc"""","
A while back I discovered https://dadroit.com/ which is small (in feature set) but perfectly formed (nicely designed and extremely fast, even on huge files).
Maybe a CSV killer would be a human readable columnar based file format.
Nevertheless, the article basically discusses the issues encountered with the manual "editability" of CSV files, not so much with its performance. It also mentions parquet or arrow and concedes that they require specialized format to read/write. If we are looking to that, then there are a lot of options such as sqlite format, BerkleDB (used by some cryptocurrency projects) among plenty of others.
Until someone gets excel to ingest and produce something in a better format, we're pretty much stuck.
And usually generate garbage for anything but the most trivial case, which really nobody gives a shit about. That's the main reason why CSV absolutely sucks too, you have to waste month diagnosing the broken shit you're given to implement the workarounds necessary to deal with it.
> I definitely can't do that with .xlsx.
You probably can though. An xlsx file is just a bunch of XML files in a zip.
A bunch of XML files with opaque formats that MS constantly makes changes to to make its competitors have to keep chasing the format.
Libreoffice handles normal UTF-8 encoded, quoted value CSV files fine. Excel not so much.
Have you ever worked in embedded systems? Writing XML files and then zipping them on a platform with 32 kilobytes of RAM would be hell. CSV is easy, I can write the file a line at a time through a lightweight microcontroller-friendly filesystem library like FatFS.
I know this is HN and we like to pretend we're all data scientists working on clusters with eleventy billion gigs of RAM, but us embedded systems folks exist too.
Incorrect encoding, incorrect separators (record and field both), incorrect escaping / quoting, etc…
> If I know what my data looks like
If you control the entirety of the pipeline, the format you're using is basically irrelevant. You can pick whatever you want and call it however you want.
> Have you ever worked in embedded systems? Writing XML files and then zipping them on a platform with 32 kilobytes of RAM would be hell. CSV is easy, I can write the file a line at a time through a lightweight microcontroller-friendly filesystem library like FatFS.
You can pretty literally do that with XML and zip files: write the uncompressed data, keep track of the amount of data (for the bits which are not fixed-size), write the file header, done. You just need to keep track of your file sizes and offsets in order to write the central directory. And the reality's if you're replacing a CSV file the only dynamic part will be the one worksheet, everything else will be constant.
I think you are missing the point -- you only need to know about generator to know about format.
Since the parent poster was talking embedded, here is one example: a data logger with tiny embedded records tuples: (elapsed-time, voltage, current). You need this to be readable in the widest variety of programs possible. What format do you use?
I think the answer is pretty clear: CSV. It is compatible with every programming language and spreadsheet out there, and in a pinch, you can even open it in text editor and manually examine the data.
Using something like XLSX here would be total craziness: it will make code significantly bigger, and it will severely decrease compatibility.
Yeah i can generate any file with a bunch of printf, but csv i dont have to read a specification, i its possible to read with a bunch of read without have to use a xml or xlsl library.
The only thing funnier than producing broken CSV is consuming broken CSV.
If you don't open it in Excel, you can have as strict a parser as you want, just like any other format.
But neither is going anywhere anyway.
No, you can not. Because the CSV format is so fuzzy you can very easily parse incorrectly and end up with a valid parse full of garbage.
Trivially: incorrect separator, file happens to not contain that separator at all, you end up with a single column. That's a completely valid file, and might even make sense for the system. Also trivially: incorrect encoding, anything ascii-compatible will parse fine as iso-8859-*. Also trivially: incorrect quoting / escaping, might not break the parse, will likely corrupt the data (because you will not be stripping the quotes or applying the escapes and will store them instead).
It's like you people have never had to write ingestion pipelines for CSVs coming from randos.
That's because this is not what this thread is about.
The comment you're responding to is not about CSVs coming from "randos". It's for the case where that rando is you, so you can make sure the problems you mention don't happen on the generation side of CSVs.
Is it? I think you're absolutely right that naive points of view like the one you're responding to will lead to avoidable bugs, but I'm not so sure the problem is CSV so much as people who assume CSV is simple enough to parse or generate without using a library.
The simplicity of CSV is what tells people that they can parse and generate it without a library, and even more so that that's a feature of CSV. You just had to read the comments disagreeing with me to see exactly that.
That’s why CSV is absolutely beautiful - there is a huge number of applications that people really care about, and their data is constrained enough that there is not need to care about CSV escaping and need for any third party libraries.
Creating XSLX file by hand is possible, but this will be a large amount of code and I wouldn’t include this in my script, it would need to be a separate library - which means build system support, learning the API etc...
The problem is 10x worse when you get CSV from one source and rely on another process to load it. I fought this problem for several days going from NetSuite to Snowflake via CSV.
Yeah if you see a CSV import feature without a billion knobs you know you're in for a world of hurt.
If you see a CSV import feature with a billion knobs, you're probably still in a world of hurt.
Poor handling from standard CSV libraries. Either unable to read or unable to create for some downstream process.
If you stick to one delimiter, and that delimiter is a comma, and escape the delimiter in the data with double-quotes around the entry, and escape double quotes with two double-quotes, well, you have written CSV that is correct and looks correct and will be parsed correctly by literally every CSV parser.
Parsers are trickier if you want to be lenient, but exporters are dead simple.
That’s what CSV is. That’s what happens when you ingest CSVs whose production you don’t control.
> If you [ignore everything people literally clamour for in these comments and praise csv for]
Yes i also like ponies.
That's really not a serious argument against CSV. Since you paraphrase in a silly way, I can do it too! Your "argument" is "Badly formatted files exist, therefore CSV bad".
Everyone "against CSV" seems to be arguing against badly formatted CSV, and leaping to the conclusion that "CSV is just bad" without much more to say about it. I'm sorry that badly formatted CSV gave you a bad time, but the format is fine and gets its job done.
"It doesn't have x, y or z feature therefore no one should be using it ever" is kind of a dumb argument, honestly.
The argument is actually that the badly formatted CSV files have taken over, therefore CSV is bad. You can't reject them, so your import becomes unreliable.
Me, 20 minutes later: Heh that was easy I am a genius
Me, 21 minutes later: Unicode is ruining my life T_T
Don't get me wrong, I really like CSV because it's so primitive and works so well if you are disciplined about it. But it's easy to get something working on a small dataset and forget all the other possibilities only to faceplant as soon as you step outside your front door. In the case above my experience with dealing with CSV data from other people made me arrogant, when I should have just taken a few minutes to learn my way around a mature library.
In UTF-8, the byte for a comma and a quote only exist as their characters. They don't exist as parts of multibyte sequences, by design.
If you have Unicode problems, then you have Unicode problems, but they wouldn't seem to be CSV problems...? Unless you're being incredibly sloppy in your programming and outputting double-byte UTF-16 strings surrounded by single-byte commas and quotes or something...?
What do you mean, there are "no guarantees"? You are in charge! You know what data you're dumping, you can see if it imports well. You can tailor your use case.
That's not the same as getting a CSV from some dump, where you have limited (if any) control over the behavior.
After working with so many retailers and online sales channels, things that are considered "legacy" or "outdated" by the HN crowd doesn't seem like it will go away unless both sides make a change. There are numerous articles posted on HN about how "FTP is dead" or no one uses it anymore, when it's far from the case.
Even Amazon's marketplace and vendor files are still using SFTP and EDI files. They've recently made changes, but it's been slow and hasn't had widespread adoption.
There's also the universality and "simplicity" CSV provides to the non-computer literate, and convincing them to make a change to a new standard provides itself some non-technical challenges. CSV is a bad standard, but it's the best one given what it does and its flexibility.
Sometimes you need to look up a single code in a 50 MB file. And sometimes you need a quick check to see if one line or a million lines changes.
It's "exception not the rule" type stuff... but it sure comes in handy to be able to check this stuff quick with basic text tools than have to run it through some binary parser. Same as JSON. But unlike protobufs for example.
And honestly they usually simply don't exist. In the vast majority of cases, in my experience.
I think the valuable insight here is that there needs to be a meme / movement that CSV is bad or deprecated. That's what's actually going to put the nails in its coffin, not private griping from developers when they get CSVs.
I'm all for it. Down with CSV :)
And the cycle continues.
If I'm trying to make an exportable format for a data logger with an SD card running on an ARM microcontroller, it doesn't get much easier than CSV. Sure, I could save space by rolling my own binary format, but then I have to provide a PC application to read it (and realistically, the user is probably just going to want that application to dump to a CSV anyway!).
I agree that for many use cases there are much better alternatives, but one of the reasons CSV is so popular is because it's so simple. It shouldn't be used for multi-gigabyte datasets, but for many simple use cases it works great.
that is... if you're following the RFC 4180 guidance for CSV. microsoft has another internal standard that occasionally gets referenced.
Are you referring to a particular format variant?
Or if you have SQLite installed, it is an easy to have SQLite itself read the dump and export it in CSV, letting it deal with things like quoting that can be a pain with grep and sed/cut. This way also makes it easy to rearrange or omit columns and to do some filtering so that data you don't care about doesn't get into the CSV.
SQLite is wonderful, but it's definitely not a replacement for CSV
The main feature of CSV is that it's aways broken, not that it's almost trivial.
> with one that requires a specific 85 kLOC codebase to access
sqlite is available essentially everywhere. Every browser uses sqlite extensively internally, so do tons of other software on your standard desktop machine (to say nothing of mobile OS where it's ubiquitous). Using sqlite is not any sort of constraint.
> SQLite is wonderful, but it's definitely not a replacement for CSV
Only in the sense that it's nowhere near as bad and annoying as CSV.
"I Cannot Fathom A Use Case For CSV So I Would Like To Ban Everyone From Using It For Any Reason"
As soon as data has any form of structure to it (and most data does). CSV complicates everything. Even for unstructured data, the problem of escape characters often shows it's ugly head. The moment your data contains a comma, tab, or space, you run into a nasty mess that, in the best case makes your system fail, and in the worst case silently adds corrupt data into the system.
Neither JSON nor XML suffer from that problem and both can easily be used in any scenario you'd use CSV. The only argument against either format is they are a bit more bulky than CSV.
Any variable length text data format is going to run into issues when special characters are used in the data. CSV has no definition of what should happen when that occurs.
CSVs also are great because you can parse them one row at a time. This makes for a very scale-able and memory-efficient way of processing very large files containing millions of rows.
Let there be no mistake: Everyone reading this today will retire long before CSVs retire. And that's just fine by me.
Even RFC4180-compliant CSVs can be incredibly memory-inefficient to parse. If you encounter a quoted field, you must continue to the next unescaped quote to discover how large the field is, since all newlines you encounter are part of the field contents. Field sizes (and therefore row sizes) are unbounded, and much harder to determine than simply looking for newlines - if you were to naively treat CSV as a "memory-efficient" format to parse, you would create a parser that would be easy to blow up with a trivial large file.
",".join(["a,b","c","d\ne","\"f\",g\""])
yields:
a,b,c,d
e,"f",g"
Try opening that in any csv reader.
This is valid JSON
That's unreadable and unparsable by a human?Not only is JSON often more parsable, because it's structured it also becomes a lot easier to query.
I grep and awk xml and json stuff all the time. I also have the added bonus of being able to use `jq` for json content.
When someone says "Maybe we can fix CSV" this is what you should do instead of trying to "fix" CSV.
Yeah, it's never really come up as it's generally trivial to convert into this format from either a text editor or sed (Look for the `],[` and put a new line after the comma).
If the primary usecase is to take data give it to a non-programmer to evaluate it, then CSV isn't terrible. Add a transformer that's ran when someone manually requests to see the data.
However, for machine to machine communication, CSV should never be used.
I feel so much of what keeps CSV in use is that it's a format that you can relatively easily generate without pulling in a lot of library dependencies and relatively quickly generate something that users can just "double click it opens in Excel".
What the Excel team could really gift to developer humanity at this point is some dumb file extension Excel registers like XLJSON that you could just rename a JSON file to and Excel opens it like a spreadsheet on double click.
What's ensuring the format of data in a CSV file?
Format comes from the same place it comes from for CSV. It's part of whatever data contract you are making with whatever system is providing the data. If someone shoves YAML into a Json file you've got problems, just like you have problems if someone starts talking about cars when you expected a file about airplanes.
At some point, you've got to turn file data into application data regardless of format. CSV offers no help in shaping the data and has a bunch of footguns to boot.
> JSON is one of the formats Pandas can read, but it has to be structured in a format the python library can read in as tabular data.
Pandas is highly flexible in it's ability to read in Json data, What I showed would be trivially loadable by Pandas, so would other formats (including more traditional JSON data).
Turning structured data into tabular data is trivial. What isn't trivial is turning tabular data into structured data.
1. You can read them without any software
2. Streamable row by row
3. Compress well
To be honest most of the points in this article could be addressed by standardizing a method of defining a schema for the CSVs. It could even be backward compatible by appending the definition as metadata on the header column or as a separate file.
One thing that would be good to have is a standardized method of indexing CSVs so that random access is possible too, though that would be more involved.
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.
"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.
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
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.
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.
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?
The assumption that most software uses is that the import file will be in the same variant of the format as what that tool exports. That seems to be more of a problem than anything else.
Sure, and they sometimes do that if they have to ingest CSVs whose origin they don't control (although not every system implementor cares enough to do it).
But that's still just a bunch of shitty faillible heuristics which would not be necessary if the format was not so horrible.
cat input1.csv input2.csv > output.csv
resulting in a single file containing multiple formats.
Also, what variant is this:
What is the value of the third column?Is this a CSV file without quoting? Then it's
Or is it a CSV file with double quote escaping? Then it's This is fundamentally undecidable without knowledge of what the format it is.You can decide to just assume RFC compliant CSVs in the event of ambiguity, but then you absolutely will get bugs from users with non-RFC compliant CSV files.
So, yeah. Can't really be done without making too many assumptions that will break later.
Yes. And my software does that. But it is always going to be a guess which the user needs to be able to override.
Note the difference is that I am suggesting reducing the number of standards in-use by using only one already existing CSV format. :)
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).
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.
Does this really count as compatible? You will get user bugs for this.
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.
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? ;)
However it is not particularly readable/diff-able if this is part of your use case.
https://en.wikipedia.org/wiki/Microsoft_Excel#XML_Spreadshee...
I realize that this is a specific use case here, but I was on Cognos for years, and then when they started shifting over to Tableau, it wasn't any better.. csv, MS formats, proprietary formats, etc.
>The biggest and most thorny problem to solve is the people problem: how do you convince people to stop creating new CSVs when they’ve never done things any other way? Fortunately - for this problem, anyway - most of the world’s business data is born in one of a handful of programs that are owned by an increasingly small number of companies. If Microsoft and Salesforce were somehow convinced to move away from CSV support in Excel and Tableau, a large portion of business users would move to a successor format as a matter of course. Of course, it’s debatable whether that kind of a change is in the best interest of those companies, but I’m cautiously optimistic.
No, it's not. It's about replacing CSVs for exchanging data. It mentions that CSVs often are the product of someone exporting data from a spreadsheet or doing a table dump, and how just doing that tends to create a ton of problems, but Excel is an example, not the subject matter of the article.
> The business world will laugh at you and continue on their merry CSV way.
The business world pays me a lot of money to teach them not to use CSVs.
Could you teach them better and faster? I don't think they're getting it. You have my blessing to use violence.
I'm trying man. I'm trying.
...or you could invent abominations like CSV, TSV, etc. ;-)
-the first line is always a header
-fields are separated by Unit separator characters
-records are separated by Record separator characters
-encoding is UTF8
If you wanted to get fancy you could also have:
-comment lines
-column metadata (e.g. column 0 is an ISO date, column 2 is text, column 3 is an integer)
Both the above could start with a Unicode character unlikely to be used for anything else.
I think that would avoid 99% of the pain of CSV files. The downside is that the use of things like the Unit separator mean that it wouldn't be easy to create/edit manually.
I don't suppose it will ever happen though.
I mean, you'd have to be using a pretty terrible tool for it not being able to handle that, and I suspect if such an approach were to become prevalent, that tool would either fix the glitch or become largely unused.
But you gotta admit that excel is a pretty terrible tool to hand edit a CSV with... ;-)
*shudder
For what it's worth, I totally agree something like compressed json lines is a better data exchange format, but part of why csv remains as universal and supported as it is is that so much existing data storage applications export to either csv or excel and that's about it. So any ETL system that can't strictly control the source of its input data has no choice but to support csv.
Avro is not a lossless alternate encoding of what's canonically a JSON document. Yes Avro supports a JSON encoding, but it's not canonical.
In general though, you're point about Avro being able to be represented as text is valid, and applies to practically any binary formThere'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.at, which is why the whole "but it needs to be a text format" argument is garbage.
> The data-warehouse ecosystem already standardized on Avro as its data interchange format. And spreadsheets are just tiny data warehouses. So why not? ;)
I wish that the data-warehouse ecosystem standardized on anything. ;-)
That said, there are plenty of good reasons why a data-warehouse standard would not be advisable for spreadsheets.
To be clear, I'm not talking about using an Avro library to encode data to JSON. I'm saying that when you decode an Avro document, the result that comes out — presuming you don't tell the Avro decoder anything special about custom types your runtime supports and how it should map them — is a JSON document.
Where, by "JSON document" here, I don't mean "a JSON-encoded text string", but rather an in-memory ADT that has the exact set of types that exist in JSON, no more and no less. The sum-type of (JSONArray | JSONObject | String | Integer | Float | true | false | null). The "top" expression type recognized by a JSON parser. Some might call such a document a "JSON DOM." But usually it's a "JSON document", same as how the ADT you get by parsing XML is usually referred to as an "XML document."
Or, to put that another way, Avro is a way to encode JSON-typed data, just as "JSON text", or https://bsonspec.org/, is a way to encode JSON-typed data. They're all alternative encodings that have equivalent lossless encoding power over the same supported inputs.
Semantic point: it's not a "document".
There are tools which will decode Avro and output the data in JSON (typically using the JSON encoding of Avro: https://avro.apache.org/docs/current/spec.html#json_encoding), but the ADT that is created is by no means a JSON document. The ADT that is created has more complex semantics than JSON; JSON is not the canonical representation.
> By which I don't mean JSON-encoded text, but rather an in-memory ADT that has the exact set of types that exist in JSON, no more and no less.
Except Avro has data types that are not the exact set of types that exist in JSON. The first clue on this might be that the Avro spec includes mappings that list how primitive Avro types are mapped to JSON types.
> Or, to put that another way, Avro is a way to encode JSON-typed data, just as "JSON text", or https://bsonspec.org/, is a way to encode JSON-typed data
BSON, by design, was meant to be a more efficient way to encode JSON data, so yes, it is a way to encode JSON-typed data. Avro, however, was not defined as a way to encode JSON data. It was defined as a way to encode data (with a degree of specialization for the case of Hadoop sequence files, where you are generally storing a large number of small records in one file).
A simple counter example: Avro has a "float" type, which is a 32-bit IEEE 754 floating point number. Neither JSON nor BSON have that type.
Technically, JSON doesn't really have types, it has values, but even if you pretend that JavaScript's types are JSON's types, there's nothing "canonical" about JavaScript's types for Avro.
Yes, you can represent JSON data in Avro, and Avro in JSON, much as you can represent data in two different serialization formats. Avro's data model is very much defined independently of JSON's data model (as you'd expect).
My understanding was always:
1. that the "primitive Avro types" are Avro's wire types, which are separate from its representable domain types. (Sort of like how RLE-ified data has wire types of "literal string" and "repeat literal N times".)
2. that any data that would not be valid as input to a JSON encoder, is not valid as input to an Avro encoder, because its wire types are defined in terms of their a mapping from a set of domain types that are exactly the set of domain types accepted by JSON encoders (whether they're explicitly noted as being those or not.)
Or, to put that another way: an Avro schema is — besides a validation step that constrains your data into a slightly-more-normalized/cleaned format — mostly a big fat hint for how to most-efficiently pack an (IMHO strictly JSONly-typed) value into a binary encoding. Differences between "long" and "int" on the wire aren't meant to decode to different domain types (at least, by default); they're just meant to restrict the data's allowed values (like a SQL DOMAIN constraint) in ways that allow it to be more predictable, and so to be wire-encoded more optimally.
Let me lay out some evidence for that assertion:
• Avro supports specifying e.g. "bytes" vs. {"array": "byte"} — there's literally no domain-type difference in those! But one is a wire-encoding optimization over the other.
• Avro has a "default" property, and this property—as part of the JSON-typed schema—can only take on JSON-typed values. Do you think this is an implementation constraint, or a design choice?
• Avro's enum type's "symbols" array? Once again, defined by (and therefore limited to) JSON string values.
• Avro doesn't implement an arbitrary-precision integer type, even though its wire-encoding for integers would support one just fine. Why? Seemingly only because JSON doesn't have an arbitrary-precision integer type (because JavaScript doesn't have a native BigNum type); nor does JavaScript/JSON have any obvious type to O(1)-efficiently deserialize a BigNum out into. (Deserializing BigNums to strings wouldn't be O(1).) Every other language offers a clean 1:1 mapping for bignums, but JavaScript doesn't, so JSON didn't, so Avro doesn't.
• And why do you think Avro schemas are stored as embedded explicitly-defined-to-be-JSON documents within the root-level record / .avsc file, anyway? This means that you are required to have a JSON decoder around (either at decode time, or at decoder codegen time) to decode Avro documents. Why would this be, if not because the Avro implementation is (ot at least originally was) expected to decode the Avro document's wire types into the JSON library's already-defined ADTs, relying on e.g. having those "default"-parameter values already loaded in in JSON-value format from the schema's decode-output, ready to be dropped seamlessly into the resulting Avro decode-output?
And the biggest knock-down argument I'm aware of:
• Avro "string" doesn't support "\u0000". Why not? Because as you've said, Avro has a "JSON encoding", which specifies one-to-one mapping for strings; and JSON doesn't support "\u0000" in strings. (Just ask Postgres's jsonb type about that.) Since an Avro string containing "\u0000" wouldn't round-trip losslessly between the JSON and binary wire-encodings, it's not allowed in strings in the binary encoding.
Based on these comments, my best guess is you got the idea that Avro was for encoding JSON because the schema declaration is encoded in JSON, but that's not nearly the same as the data model. There are some terrible implementations of Avro libraries out there that use JSON as some kind of middleware, but that's not how Avro actually works.
If there's a type model it is derived from at all, it's the Java type model.
"byte" is not a valid type in Avro. There is only "bytes", and the domain model reflects this. You can't work with individual "byte" of a "bytes" object.
Default values are encoded in the schema, and so that does limit what kind of default values you can have, but again this is a limitation derived from the schema being defined in JSON, and how the schema language was defined in general. So your defaults have to be represented as JSON literals, but they don't even necessarily share the type of the JSON literal (e.g. a field defined as: '{"name": "foo", "type": "long", "default":1}' does not have the same default value as '{"name": "bar", "type": "int", "default":1}", because "foo" has a default value that is a long while "bar" has one that is an "int"). Note that "default values" are a property of the type, and only apply to elements inside complex data types. JSON has no such equivalent concept.
Avro's type model does have an arbitrary precision type that doesn't correlate to anything in JSON: the "decimal" logical type.
You aren't required to use a JSON decoder to decode Avro documents, nor are you required to use a .avsc file. The Avro schema file is just the standard way to represent a schema. If you have the schema, you don't need the file. JSON schema files are one of the poorer choices in the Avro design, but you'll notice that the schema is defined the way it is specifically so that it can cover a type model well outside of JSON. You'll also notice the names of types in Avro don't directly correlate to names of types in JSON.
* The \u0000 thing is a bug in avro tools, but there is nothing about the spec that prohibits having \u0000 in your strings.
I feel like in general this is like a retcon exercise, where you've reached a conclusion and are drawing evidence to prove it, while ignoring the information that contradicts. I spoke with Cutting a fair bit when he came up with Avro, and I can assure you, while the schema language does very intentionally use JSON, Avro is not a representation for JSON types.
[0] though the last time I touched it was in 2014, so things might've changed since
[1] as per [0], this was before Python dicts were ordered by default
If you're using Avro as a way to do JSON, that's definitely a bad choice. That's why I found the notion of Avro as a way to work with JSON documents as really... odd. (https://news.ycombinator.com/item?id=28222491) It's a decent way to manage data transfers (and I continue to argue a much better one that CSV or JSON), but as a "lossless alternate encoding of what's canonically a JSON document", it's a bad fit.
Of course this is not always applicable, since you sometimes don't control the format you get your data.
When I see CSV parsers like https://www.papaparse.com/ that even try to support comments and empty lines in the format, I wonder if it'd really be that bad to just raise an error on anything that doesn't fit RFC 4180[1], with explanation on where and how the file is corrupted. Push the issue to the writers of such files.
Then I remember the Robustness Principle[2] and I chill a little.
[1] https://datatracker.ietf.org/doc/html/rfc4180
[2] https://en.wikipedia.org/wiki/Robustness_principle
Maybe it's actually presenting what you've selected last?
It's giving me comma as the default separator, and it's the first option in the dropdown. Tab is the 3rd option.
>> Those other things have different names like TSV
That depends on the writer. I've gotten what should be named PTVs (pipe-terminated values) as CSVs. I can understand how it happened. If the underlying software outputs PTVs, you don't want to bother converting that because you're working in a legacy language that's a pain to work with (the type where identifiers can't be longer than 4 chars), and you want the user to be able to double-click on it and have it open in a spreadsheet without prior configuration, you just push the issue to the reader of the file, since by tradition readers are already quite tolerant of format differences...
Of course, there'll always be the case where the reader is simply not tolerant enough, like when the escaping syntax differs. There doesn't seem to be a way to get LO Calc to interpret "foo|bar\|baz|" as cells "foo" and "bar|baz", for example.
For CSV file replacements, I'd expect something like "one JSON array per line, all values must be JSON scalars". In that case, it's not much larger than a CSV, especially one using quotes already for string values.
But this demonstrates the problem with JSON for CSV, I suppose. Is each line an object? Is it wrapped in a top-level array or not? If it is objects, do the objects have to be one line? If it is an object, where do we put field order? The whole problem we're trying to solve with CSV is that it's not a format, it's a family of formats, but without some authority coming in and declaring a specialized JSON format we end up with a family of JSON formats to replace CSV as well. I'd still say it's a step up; at least the family of JSON formats is unambiguously parseable and the correct string values will pop out. But it's less of a full solution than I'd like.
(It wouldn't even have to be that much of an authority necessarily, but certainly more than "The HN user named jerf declares it to be thus." Though I suppose if I registered "csvjson.org" or some obvious variant and put up a suitably professional-looking page that might just do the trick. I know of a few other "standards" that don't seem to be much more than that. Technically, even JSON itself wasn't much more than that for a lot of its run, though it is an IETF standard now.)
How is that not a problem with every data serialization format? It does me no real good if I have an XML schema and a corresponding file. If I don't know what those elements and attributes represent I'm not really any better off.
It's not like JSON or XML can meaningfully be marshaled back into objects for use generically without knowledge of what is represented. There are generic JSON and XML readers that allow you to parse the data elements sure, but so, too, do generic CSV readers like C#'s CsvHelper or Python's csv. In all cases you have to know what the object turns into in the application before the serialized data is useful.
And, yes, CSV has slightly differing formats, but so does JSON. Date formats are conventionally ISO 8601, but that's not in the spec. That's why Microsoft got away with proprietary date formats in System.Text.Json. XML isn't really any better.
What's proprietary in it? It follows ISO 8601-1:2019 and RFC 3339 according to the docs.
In .Net Framework 4.6 and earlier, the only built-in JSON serializer in the .Net Framework was System.Runtime.Serialization.Json.DataContractJsonSerializer.
You can still see it. If you're on Windows 10, run Windows Powershell v5.1 and run:
You'll see this output: Microsoft didn't fix their weird JSON serialization until quite late. They may have back ported it to the .Net Framework, but they've deleted that documentation. Powershell v6 and v7 include the newer classes that are properly behaved. This is why Json.NET used to be so popular and ubiquitous for C# and ASP applications. It generated JSON like most web applications do, not the way Microsoft's wonky class did. Indeed, I believe it may be what System.Text.Json is based on.To be fair, RFC 3339 wasn't even published back when this class was implemented (in .NET 3.5) so I guess they just went with whatever worked for their needs. ¯\_(ツ)_/¯
To be fair it's not an objectionable format. Using line breaks to separate objects makes it streamable, and you don't need to enclose the whole thing in an array to make it a valid JSON document.
I see CSV files that contain JSON arrays/objects in their fields all the time. Mainly from exporting Postgres tables that contain json/jsonb-typed columns. Are you saying that these aren't valid CSVs?
Such a format could contain arbitrary JSON in a "cell", but simply as text, in the same way as putting the same data in a CSV.
I'm still looking for somwthing that can do it.
(Of course, Spark doesn't support timestampz which is probably why the formats don't.)
In practice, I used TSVs a lot, as tabs do not usually occur in most data. Alternatively, you could use pipes (|) or control characters as field or row separators.
If you are going to displace a standard, it has to be significantly better than the old.
The semantics of CSV — how it decodes, if you don't have an application making additional configured assumptions or using heuristics to detect microformats — is just N columns, one of header names, N of raw text cells. Nothing more, nothing less. No schema defining what a column's type is.
If you want something better than CSV to take off, at the very least it needs to solve the common pain points of CSV. JSON doesn't - all it brings to the table is the ability to distinguish number vs text. That's a yawn.
Agreed. I don't ever see something better than CSV taking off (having worked on this problem for better part of a decade) unless you could get to the point where you've got a largely similar notation, but a richer semantic grammar powered by something like Schema.org.
In other words, a strongly typed CSV with a community backed schema. I think GPT-3 and the like are a big wild card here. I can't describe how, but I expect in 20 years we will be using something better for storing data and will look back and saw "CSV used to be the best but then GPT-N _____________".
It brings the ability to parse in parallel - that's a big deal. And while number vs text might not be a huge difference in theory, in practice it eliminates what, 95% of real-world parse problems?
I think you could fix most of the pain of CSV simply by adding a second header row which defines the type of the column, using a common vocabulary of types. TEXT, NUMBER, ISO8861-DATE, etc.
On text vs numbers, at least some widely-used software (e.g. R, Excel) will try to guess for you. It should be obvious how this might cause problems. Maybe one should turn auto-conversion off (or not use things that don't let you turn it off) and specify which columns are numbers. Some datasets have a lot of columns, so this can be a PITA, even if you do know which ones should be numbers. But the bigger problem is if you have to deal with other people, or the files that they've touched. There are always going to be people that edit data in excel, don't use the right options when they import, etc.
I definitely had my fair share of trouble with locale-defined number format. Importing a column where a thousand is spelled "1.000", any integer between 1000 and 999999 would be wrongly parsed as a float between 1 and 999, while for any other number (like "0,1" for one tenth, "1.000.000" for a million, "1.000,56" for a thousand euros and change) the parser would give up and keep the string.
I usually have had more luck importing as text, then doing some string replacement of separators before finally converting to number.
The trouble is that a lot of existing software (not just Excel) won't properly roundtrip text that looks like numbers and will e.g. strip the leading zero from phone numbers, or worse, change the last digit to make a number that exists in floating point.
> The pain points are usually dates or currencies - the same problem I usually have with JSON, because there's no standard format.
Hmm, I've never had a problem with ISO8861 dates - a string is either a valid date or not, it's very rare for someone to "accidentally" put data in ISO8861 format when it's not actually a date. Dates without timezones can cause problems, but that's more of a semantic issue than a serialization issue. What are the problems that you get?
I can see how currencies could be an issue with the lack of a standardised fixed-precision type. But in my experience they're an order of magnitude less common than issues with phone numbers, postal codes, and the like.
> I think you could fix most of the pain of CSV simply by adding a second header row which defines the type of the column, using a common vocabulary of types. TEXT, NUMBER, ISO8861-DATE, etc.
I'm sure you could. But at that point you're defining a new and incompatible format - you have to make it incompatible, or otherwise people will open these files with a tool that doesn't understand the header format and you're back to square 1 - so you'll pay all the same adoption costs as a completely new format. So it make sense to fix all the issues we can - and a format which can be split and parsed is definitely a major improvement for many use cases.
Yes, this is a major pain. It can be avoided by using Tab separated value (TSV) files, which don't use escaping. But then you can't store Tabs or carriage returns in your data. Also there is no way to store metadata in TSV.
JSON is far from ideal for storing 2D data tables as it is a tree. This means it is much more verbose than it needs to be. The same is also true for XML.
Also, the fact that they have been around for years and never taken off implies that they probably never will.
Realistically I don't think anyone (at least on HN) would presume that editor support would be so weirdly obtuse, but it's not exactly well-defined here, either.
I'm mostly just thinking out loud, here, not really critiquing anyone.
[modifier]-[C,D,E,F]
Other posters have noted that they have ASCII separators recently, I have as recently as 2018 internally at a FAANG. So they are not completely unknown in the wild.
Who's writing CSV files by hand though?
I'd expect some kind of CSV mode in my text editor where Tab and Newlines are turned into keys to insert those characters
Yes, because that just kicks the can down the road: how do you represent the one-byte string "\x1E"?
And since ASCII has them, Unicode has them too.
Doing this avoids quoting, which avoids an entire class of problems. And yes, if you're passing multi-megabyte files around on a regular basis, or transferring giant blobs of text or binary data, or looking for a storage format with metadata then csvs are an absolutely awful choice, but as with any problem you pick the right tool for the job, and csvs are a great tool when you want to easily move some structured alphanumeric data around in a format that's trivial to parse in any language and widely supported.
But then you have the additional problem of trying to work out what the delimiter is. You can take an educated guess, but this isn't going to be 100% reliable.
There's a subset of CSV that forbids escapes that is super fast to parse. All fast CSV parsers I'm aware of take advantage of this subset. I try to never ever publish a CSV that has quotes, and always aim for a more restrictive grammar that is cleaner, better thought out data.
I find this really hard to believe given it's a simple enough syntax. And parsing is usually not the limiting factor, usually fast enough to not be noticed alongside interpreting or loading the source data. Every (much more sophisticated) compiler I can think of uses a linear parser based on this assumption.
Binary formats have their own pain points - more of them actually
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.
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.
We programmers get stuck into thinking that openness, speed, or other performance characteristics matter. But CSVs prove otherwise.
The reason why CSVs win in many situations is
1. Because they're convenient to edit.
2. Because they work in Microsoft Excel
CSVs have a myriad of issues: too many to list. But that doesn't matter, because convenience + Excel compatibility are king in practice.
To muse about other benefits is ignoring the core reason why CSV is used.
It also runs on all kinds of embedded devices in addition to personal computers and servers.
https://en.wikipedia.org/wiki/SQLite#Operating_systems
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.
Not all editors make it easy for you to use a tab character.
If only there was some other solution that would not require you to use a character that a lot of editors refuse to insert or even try to convert to spaces...
The advantage of CSV is that it's as accurate as your plain text representation of your data can be. Since binary data can be represented by character data, that's 100% accurate. As soon as you introduce a storage format that has made assumptions about the type of data being stored, you've lost flexibility.
SQLite is not intended for data serialization. It's intended for data storage to be read back by essentially the same application in the same environment.
SQLite's "dynamic data types" coerce data on input, not output. Once the data is in sqlite the way you wanted it, excel has no interpretation to perform, except insofar as really really wanting dates.
> The advantage of CSV is that it's as accurate as your plain text representation of your data can be.
Yeah nah.
> SQLite is not intended for data serialization. It's intended for data storage to be read back by essentially the same application in the same environment.
That's completely, absolutely, utterly, objectively, nonsensical.
Data analysis, exchange, container, and archiving, are literally all use cases listed as "appropriate uses for sqlite" in "Situations Where SQLite Works Well" on the official sqlite website: https://www.sqlite.org/whentouse.html
Cockiness tells me that you’re insecure about your knowledge, not that you know more than GP.
The idea that a CSV would be more likely to be correctly encoded than a DB is hilarious, thanks for the laugh. But that you were confident enough to seriously put it in writing shows how little experience you have with CSV.
A CSV file represents the exact digits that are to be stored. You have unlimited precision. You could even store irrational numbers, equations, or mix data types in a column. OTOH, you have to make sure the delimiting character is not present in the data - that can be pretty easy, if you use the field-separator character in ASCII, or even just a \t. I've even seen people terminate fields with ^8675309| because they felt confident no data would contain Jenny's number.
A database, like Excel, likes to conform data. This is usually awesome! But sometimes, it's not.
"A bird, like homo sapiens, has two feet" doesn't mean that birds are humans or humans are birds either. It means that in this respect, birds and humans are alike. Which is what GP meant: With respect to conforming data, databases behave just like Excel.
Language note: I think it's the fact that there are two commas around the inserted-clause [terminology?] ", like Excel," that does it. If there were only one comma, before or after "like Excel" it would read the way you read it. Can be tricky for non-native (and sometimes also native) speakers.
for line in input: ','.join(line)
It's not exactly a problem with "CSV" specifically, but the environment in which it exists.
That's not relevant. If a data format coerces data when it's stored, it's still not a data format suitable for serialization.
If you do add column types to your tables as in traditional databases, then of course you should expect some varying behavior based on that.
I can imagine some potential for issues from people expecting to insert exact decimal numbers and choosing floating-point by accident, perhaps, or using integer types for actually-text numeric ID fields. If you go all-strings on input with no column affinity, then you'll get the same strings on output, though, so that's isomorphic to good-CSV.
¹ https://www.sqlite.org/datatype3.html – §3.1 “If the declared type for a column contains the string "BLOB" or if no type is specified then the column has affinity BLOB.” + §3 “A column with affinity BLOB does not prefer one storage class over another and no attempt is made to coerce data from one storage class into another.”
I'd be satisfied if Excel was just a little less aggressive about it's data coercion on import.
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.
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.
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.
Okay, put your money where your mouth is. Prove it.
CSV uses a variety of date-time formats, but a prevalent one is YYYY-MM-DDThh:mm:ss.sssZ. I'll leave it as an exercise for the reader to determine whether that is as compact as an 8-byte millis since the epoch value.
CSV also requires escaping of separator characters, or quoting of strings (and escaping of quotes), despite ASCII (and therefore UTF-8) having a specific unit separator character already reserved. So you're wasting space for each escape, and effectively wasting symbol space as well (and that's ignoring the other bits of space for record separators, group separators, etc.).
Then there's floating point data...
Honestly, this is a silly argument. CSV is, by design, a format that trades space for other factors. It should not be debatable whether it is a compact format or not.
XML, JSON, and YAML all have this issue, too.
> CSV uses a variety of date-time formats, but a prevalent one is YYYY-MM-DDThh:mm:ss.sssZ. I'll leave it as an exercise for the reader to determine whether that is as compact as an 8-byte millis since the epoch value.
This is also identical to XML, YAML and JSON.
And I know what you're about to argue, but JSON's datetime format is not in the spec. The common JSON datetime format is convention, not standard.
> CSV also requires escaping of separator characters, or quoting of strings (and escaping of quotes), despite ASCII (and therefore UTF-8) having a specific unit separator character already reserved. So you're wasting space for each escape, and effectively wasting symbol space as well (and that's ignoring the other bits of space for record separators, group separators, etc.).
This is also identical to XML (escaping XML entities, sometimes having to resort to CDATA), YAML (escaping dashes) and JSON (escaping double quotes).
All you've shown is that CSV has the same limitations that XML, YAML, and JSON have, and those three formats specifically designed and intended for data serialization. Yes, the other formats do have other advantages, but they don't eliminate those three limitations, either.
This is for data serialization, which means it's going to potentially be used with data systems that are wholly foreign separating great distances or great timespans. What data serialization format are you comparing CSV to? What do you think CSV is actually used for?
Are you arguing for straight binary? You know that CSV, XML, YAML and JSON all grew out of the reaction to how inscrutable both binary files and fixed width files were in the 80s and 90s, right? Binary has all sorts of lovely problems you get to work with like endianness and some systems getting confused if they encounter a mid-file EOF. If you don't like the fact that two systems can format text differently, you're going to have a whole lot of fun when you see how they can screw up binary formatting. Nevermind things like, "Hey, here's a binary file from 25 years ago... and nothing can read it and nobody alive knows the format," that you just don't get with plain text.
Yes, you do end up with a wasted space, but the file is in plain text and ZIP compression is a thing if that's actually a concern.
Yes. Though to their credit, some of those work with her numbers, which at least gets you 4 bits out of every 8 bits.
> And I know what you're about to argue, but JSON's datetime format is not in the spec. The common JSON datetime format is convention, not standard.
I'm not sure what argument you thought I was making, or why that comment is relevant.
> All you've shown is that CSV has the same limitations that XML, YAML, and JSON have, and those three formats specifically designed and intended for data serialization. Yes, the other formats do have other advantages, but they don't eliminate those three limitations, either.
I'm not sure what your mean by "eliminate", or why you think it matters that there are other formats with the same design trade offs.
> This is for data serialization, which means it's going to potentially be used with data systems that are wholly foreign separating great distances or great timespans. What data serialization format are you comparing CSV to? What do you think CSV is actually used for?
CSV is used for a variety of purposes. The context of the article is using it for data transfer.
The claim was that it was a compact format for data transfer, which is demonstrably not true.
> Are you arguing for straight binary? You know that CSV, XML, YAML and JSON all grew out of the reaction to how inscrutable both binary files and fixed width files were in the 80s and 90s, right?
I'm not sure what "straight binary" means to you. JSON is, for the most part, a binary encoding standard (just not a particularly good one).
You've got the heritage a bit wrong, as XML was not originally designed for data transfer at all. It was an attempt to simplify the SGML document markup language, and the data transfer aspects were subsequently grafted on. JSON & YAML have a slightly more complicated heritage, but neither was intended as a data transfer format. They've all been pressed in to service for that purpose, for a variety of reasons, that can charitably described as tactically advantageous but strategically flawed.
> Binary has all sorts of lovely problems you get to work with like endianness and some systems getting confused if they encounter a mid-file EOF.
I don't know how to break this to you, but text formats can have endianess (in fact, insanely UTF-8 does!), and systems being confused and whether they are at EOF as well.
> Yes, you do end up with a wasted space, but the file is in plain text and ZIP compression is a thing if that's actually a concern.
Wouldn't ZIP be a binary format, with all the problems and concerns you have with binary formats?
So to summarize what you are saying... "CSV is a compact format because you can compress it if you are concerned and all the space it wastes".
Would it be fair to say then that any binary format is a text format because you can convert the binary into a text representation of the data? ;-)
Whose?
> The claim was that it was a compact format for data transfer, which is demonstrably not true.
More compact than the realistic competitors (XML, JSON, YAML, etc).
For most natural text-only data, .csv.gz is probably close to the limits of what we can do. For numerical or categorical text containing patterns or structure, I agree that better formats may exist (e.g. H.264 for CSV files containing video frames). Not sure how a more general format will solve anything you couldn't already do by converting to a more specific format and tarring everything afterwards.
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.
4) Changes to the replacement format should be human-readable in a diff
It's easy to write a diff filter for e.g. xlsx, it's not possible to make CSV any good.
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.
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.
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.
There are few reasons to continue using csv in this day and age.
"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
Well there's your problem.
/s
For single tables a database is probably overkill, but it's nice to have around when you need something reasonably powerful without being overly complex or hard to get started with.
Shoutout to the Library of Congress for doing the real heavy lifting here?
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.
...until there is a newline inside a field.
The moronic quoting mechanism of CSV is one half of the problem; people like you, who try to parse it by "just splitting strings" is the other half. The third half is that it's locale dependent and after 30+ years, people still don't use Unicode.
There are non-printable, non-typable characters specifically defined as separators (ASCII 28-31) with UTF equivalents.
I appreciate the perspective though for sure. I'm guilty of the same thing on HN, where I assume people have the same uses as me when they're writing code that has to be extremely robust or blazingly fast.
Also, when doing interactive work, it's a bit different than writing production IT software.
CSV is fine .. usually
If I import JSON data I have no idea what shape the result will be in, and it requires a separate standard to let me know about columns and rows and validation can get complicated.
Frankly, I love the format.
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.
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.
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.
The only appropriate replacement for CSV would be a better defined CSV that gave hard requirements for things like field quoting
I'm in a similar situation, we've been using CSV for over a decade to move billions of dollars worth of product each year. It just works.
Call me a yak-shaver, but in every language I've worked with I've written my own csv parsing library when I needed one.
It's such a trivial thing for the majority cases (varying of delimiters, line-endings, ascii/uft8, quoting, escaping, and embedding of delimiters/line-endings) that it takes barely no time at all after you've done it once in another language. Of course there are edge cases and special cases depending upon specific workloads, but if your team has it's own parser (which is a small amount of obvious code) then it does indeed usually become a simple fix.
Sounds good using someone else's library, but below a certain complexity it's rarely worth it in the medium to long term except for when doing proof of concept or demo code, or if the domain space is complex.
It's dead simple to whip up, and we can easily tweak it to whatever the customers software spits out, like one field suddenly being UTF-8 encoded in an otherwise Windows-1252 file.
We validate on ingestion and if there are changes upstream we can immediately triage without polluting our data warehouse.
CSV is like the C of data formats. It's incredibly stable yet simple enough you can make your own variant if you need to.
If/when I get a data source introduced into my workflow that differs from this variant I come up with a routine to normalize it, integrate that into my workflow and move on.
The edge cases are a hassle but they don't become less of a hassle from a business perspective by switching to json or really any other format. We tried an experiment of using more json and eventually gave it up because it wasn't saving any time at a holistic level because the "data schema" conversations massively dominated the entirety of the development and testing time.
Obviously being able to jam out some json helped quite a bit initially, but then on the QA side we started to run in to problems with tooling not really being designed to handle massive json files. Basically, when something was invalid (such as the first time we encountered an invalid quote) it was not enjoyable to figure out where that was in a 15GB file.
That said, I fully concur with the general premise that CSV doesn't let you encode the solutions to these problems, which really really sucks. But, to solve that, we would output to a more columnar storage format like Parquet or something. This would let us fully encode and manage the data how we wanted while letting our clients continue working their processes.
What I would really like to see is a file format where the validity of the file could be established by only using the header. E.g. I could validate that all the values in a specific column were integers without having to read them all.
Agreed. JSON let's me know something is a number. That's great, but I still have to check for min/max,zero etc. A string? That's great, but I got to check it against a set of enums, and so forth. Basically, the "types" JSON gives you is about 20% of the work, and you're going to have to parse things into your own types anyway.
> What I would really like to see is a file format where the validity of the file could be established by only using the header.
Are you saying something like a checksum so not only is a schema provided but some method to verify that the data obeys the schema?
If you're talking about just some stronger shared ontology, I think that's a direction things will go. I call this concept "Type the world" or "World Wide Types". I'm starting to think something like GPT-N will be the primary author, rather than a committee of humans like Schema.org.
A checksum would be crude and user-hostile, only being able to say "you did it wrong" but not really good at tell you what it means to do it right.
If I understand the concepts correctly then it seems like a shared ontology could potentially solve the problem in a non-hostile way.
Plus, it makes me happy because I feel like types are a real-world problem, so it is always nice if the type system could enforce that real-world-ness and all the messiness that comes along for the ride.
More a byproduct of decisions made 5 - 7 years ago when the company was in raw startup mode versus a more mature roadmap.
Now you have tables and relation. Only few people understand the usefulness of Compound Document, currently.
How would any existing JSON parser handle 75GB of data in a single array of arrays?
JSON.Parse() in the browser isn't that sort of parser obviously, but there options in many languages such as both major .NET JSON libraries (Newtonsoft and System.Text.Json) have ways to parse that way. Similar examples exist in other languages too.
[1] https://github.com/danielyule/naya
So, question to the greybeards: when this format was coming about, why didn't we use one of the dedicated Data Structure separator control codes (e.g. File/Group/Record/Unit separator) that were part of the ASCII standard?
https://en.wikipedia.org/wiki/C0_and_C1_control_codes#Basic_...
It seems like it would've saved us all several decades of headache.
Consider also that IBM EBDCIC and other non-ASCII character sets were (and are) in common use, so C0/C1 may not have made sense 'back in the day'
But commas can be seen, edited, and typed with ease in any text editor.
As per comment above: your CEO, SWE, or secretary can all use or contribute to a csv file. And using an easily recognizable and typeable separator has proven to be worth the downsides.
Because you can't see them. CSV is, at its core, a text format. Using FS/GS/RS/US would effectively make it a binary format.
Also, what happens if one of those bytes appears in data?
The simple fact is you will never be able to come up with a text-based format that can handle all possible values without escape sequences, quoting, or length-encoding. And really, that's not that big a deal. It just means you have to write a simple state machine instead of using regex or your language's equivalent of split().