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

A love letter to the CSV format

github.com

Post reply on HN
Print view View original

341–350 of 711 posts

Re: A love letter to the CSV format

#341

Post by msla »

mjw_byrne wrote: post #14

CSV is ever so elegant but it has one fatal flaw - quoting has "non-local" effects, i.e. an extra or missing quote at byte 1 can change the meaning of a comma at byte 1000000. This has (at least) two annoying consequences: 1. It's tricky to parallelise processing of CSV. 2. A small amount of data corruption can have a big impact on the readability of a file (one missing or extra quote can bugger the whole thing up).…

CSV's actual problem is that there's no single CSV, and you don't know what type you have (or even if you have single consistent type through the whole file) without trying to parse the whole file and seeing what breaks. Is there quoting? Is that quoting used consistently? Do you have five-digit ZIP codes, or have the East Coast ones been truncated to four digits because they began with zero? Spin the wheel!

Re: A love letter to the CSV format

#342

Post by nh2 »

tengwar2 wrote: post #7

I'm not really sure why "Excel hates CSV". I import into Excel all the time. I'm sure the functionality could be expanded, but it seems to work fine. The bit of the process I would like improved is nothing to do with CSV - it's that the exporting programs sometimes rearrange the order of fields, and you have to accommodate that in Excel after the import. But since you can have named columns in Excel (make the data in…

https://stackoverflow.com/questions/165042/stop-excel-from-a...

Re: A love letter to the CSV format

#343

Post by jgord »

Yomguithereal wrote: post #216
mjw_byrne wrote: post #14

CSV is ever so elegant but it has one fatal flaw - quoting has "non-local" effects, i.e. an extra or missing quote at byte 1 can change the meaning of a comma at byte 1000000. This has (at least) two annoying consequences: 1. It's tricky to parallelise processing of CSV. 2. A small amount of data corruption can have a big impact on the readability of a file (one missing or extra quote can bugger the whole thing up).…

A good way to parallelize CSV processing is to split datasets into multiple files, kinda like manual sharding. xan has a parallel command able to perform a wide variety of map-reduce tasks on splitted files. https://github.com/medialab/xan

nice .. xsv is also very handy for wrangling csv files generally

Re: A love letter to the CSV format

#346

Post by Brian_K_White »

wvenable wrote: post #338
niccl wrote: post #280

Earlier quoted context omitted.

Any time you have a character with a special meaning you have to handle that character turning up in the data you're encoding. It's inevitable. No matter what obscure character you choose, you'll have to deal with it

Except we have all these low ASCII characters specifically for this purpose that don't turn up in the data at all. But there is, of course, also an escape character specifically for escaping them if necessary.

You can't type any of those on a typewriter, or see them in old or simple simple editors, or no editor like just catting to a tty.

If you say those are contrived examples that don't matter any more then you have missed the point and will probably never acknowledge the point and there is no purpose in continuing to try to communicate.

One can only ever remember and type out just so many examples, and one can always contrive some response to any single or finite number of examples, but they are actually infinite, open-ended.

Having a least common denominator that is extremely low that works in all the infinite situations you never even thought of, vs just pretty low and pretty easy to meet in most common situations, is all the difference in the world.

Re: A love letter to the CSV format

#347

Post by sitkack »

theLiminator wrote: post #319
sitkack wrote: post #304

Earlier quoted context omitted.

JSONL as a replacement for CSV, you shouldn't be using CSV as format for long term storage or querying, it has so many downsides and nearly zero upsides. JSONL when compressed with zstd, most of "expensive if large" disappears as well. Generating and consuming JSONL can easily be in the GB/s range.

I mean on the querying side. Parquet's ability to skip rowgroups and even pages, and paired with iceberg or delta can make the difference between being able to run your queries at all versus needing to scale up dramatically.

Totally agree.

I am saying JSONL is a lower bound format, if you can use something better you should. Data interchange, archiving, transmission, etc. It shouldn't be repeatedly queried.

Parquet, Arrow, sqlite, etc are all better formats.

Re: A love letter to the CSV format

#348

Post by ddulaney »

freehorse wrote: post #288
FridgeSeal wrote: post #235

Earlier quoted context omitted.

CSV tooling has had to invest enormous amounts of effort to make a fragile, under-specified format half-useful. I would call it ubiquitous, I would call the tooling that we’ve built around it “impressive” but I would by no means call any of it “good”. I do not miss dealing with csv files in the slightest.

> CSV tooling has had [...] to make a fragile, under-specified format half-useful You get this backwards. Tabular structured data to store are ubiquitous. Text as a file format is also ubiquitous because it is accessible. The only actual decisions are about whether to encode your variables as rows or columns, what is the delimiter, and other rules such as escaping etc. Vars as columns makes sense because it makes app…

I think the issue is that CSV parsing is really easy to screw up. You mentioned delimiter choice and escaping, and I’d add header presence/absence to that list.

There are at least 3 knobs to turn every time you want to parse a CSV file. There’s reasonably good tooling around this (for example, Python’s CSV module has 8 parser parameters that let you select stuff), but the fact that you have to worry about these details is itself a problem.

You said “handling data is complicated as much as the world itself is”, and I 100% agree. But the really hard part is understanding what the data means, what it describes. Every second spent on figuring out which CSV parsing option I have to change could be better spent actually thinking about the data.

Re: A love letter to the CSV format

#349

Post by pbrumm »

FridgeSeal wrote: post #287
dylan604 wrote: post #239

Earlier quoted context omitted.

Microsoft Windows has had to invest enormous amounts... Apple macOS has had to invest enormous amounts... Pick your distro of Linux has had to invest enormous amounts... None of them a perfect and any number of valid complaints can be said about any of them. None of the complaints make any of the things useless. Everyone has workarounds. Hell, JSON has had to invest enormous amounts of effort...

I guess the point is that I can take a generic json parser and point it at just about any JSON I get my hands on, and have close to no issues parsing it. Want to do the same with csv? Good luck. Delimiter? Configurable. Encoding? Configurable. Misplaced comma? No parse in JSON, in csv: might still parse, but is now semantically incorrect and you possibly won’t know until it’s too late, depending on your parser. The l…

Here is a quick test

The table of contents points to a single Json object that is 20ish gb compressed

https://www.anthem.com/machine-readable-file/search/

All stock libs will fail

Re: A love letter to the CSV format

#350

Post by crazygringo »

mjw_byrne wrote: post #14

CSV is ever so elegant but it has one fatal flaw - quoting has "non-local" effects, i.e. an extra or missing quote at byte 1 can change the meaning of a comma at byte 1000000. This has (at least) two annoying consequences: 1. It's tricky to parallelise processing of CSV. 2. A small amount of data corruption can have a big impact on the readability of a file (one missing or extra quote can bugger the whole thing up).…

I'm not clear why quotes prevent parallel processing?

I mean, you don't usually parallelize reading a file in the first place, only processing what you've already read and parsed. So read each record in one process and then add it to a multiprocessing queue for multiple processes to handle.

And data corruption is data corruption. If a movie I'm watching has a corrupted bit I don't mind a visual glitch and I want it to keep playing. But with a CSV I want to fix the problem, not ignore a record.

Do you really have a use case where reading itself is the performance bottleneck and you need to parallelize reading by starting at different file offsets? I know that multiple processes can read faster from certain high-end SSD's than just one process, but that's a level of performance optimization that is pretty extraordinary. I'm kind of curious what it is!

Post reply on HN