Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Time to retire the CSV?

bitsondisk.com

Post reply on HN
Print view View original

71–80 of 594 posts

Re: Time to retire the CSV?

#71

Post by fuhrysteve »

yuy910616 wrote: post #14

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.

Re: Time to retire the CSV?

#72

Post by RobLach »

I can generate a CSV that can be read in excel, etc.. extremely easily in a terminal.

Just yesterday I was trying to add some metadata to a list of images that I handed off to a designer and I just piped a single "find . -fprintf" command into a file.

To "retire" something so simple and utilitarian makes no sense. There's plenty of formats that deal with all the issues described in this. It's like saying we need to retire plain text files because it's confusing to know how they should be displayed.

Re: Time to retire the CSV?

#73

Post by xtracto »

grenoire wrote: post #8
izietto wrote: post #3

"In favour of what?", that is the matter. 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.

I think this issue can be bypassed if a better format such as Arrow or Parquet can be used in Excel.

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.

Re: Time to retire the CSV?

#74

Post by derefr »

Sunspark wrote: post #23

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 "forma…

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? ;)

Re: Time to retire the CSV?

#75

Post by punnerud »

There is a CSV standard (RFC 4180), the problem is that a lot of programs don’t follow it. DataGrip don’t quote all text fields and Python remove some of the \r\n or \n\r (don’t remember which).

As long as you are following RFC4180 it works.

I ended up exporting from Oracle DB using JSON and converting to CSV for one of our contractors to be able to import the data to MongoDB.

Re: Time to retire the CSV?

#76

Post by ivanbakel »

Twisell wrote: post #53
Sunspark wrote: post #23

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 "forma…

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

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.

Re: Time to retire the CSV?

#77

Post by cogman10 »

daxuak wrote: post #59
nolok wrote: post #24

Earlier quoted context omitted.

Xlsx (office format for almost a decade now) are zip and XML all the way. Not fun to look at, but totally readable by a human.

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.

As someone that works with maven and npm.... what?

This is valid JSON

    [["bob", "jones", 1, 22],
    ["frank", "was", 32, 45]]
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.

Re: Time to retire the CSV?

#78

Post by xtracto »

fighterpilot wrote: post #32
habitue wrote: post #7

Earlier quoted context omitted.

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 (an…

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.

> access through cat/grep/awk, and easily load into any programming language

Until the CSV fields contain commas themselves. Even if fields are surrounded by "".

Re: Time to retire the CSV?

#79

Post by Sanzig »

masklinn wrote: post #55
Sanzig wrote: post #44

Earlier quoted context omitted.

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…

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.

Re: Time to retire the CSV?

#80

Post by dasil003 »

Interesting thought experiment that feels good after struggling with some thorny data corruption issues (or whatever inspired this), but this what they call a boil-the-ocean problem. There's no point in thinking about CSV beyond the impact on things that you directly touch as it will never rise above the background noise of the problems faced by all the people whom you hope to inspire.
Post reply on HN