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

201–210 of 711 posts

Re: A love letter to the CSV format

#201

Post by masklinn »

andrepd wrote: post #24
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).…

That would be solved by using the ASCII control chars Record Separator / Unit Separator! I don't get how this is not widely used as standard.

> I don't get how this is not widely used as standard.

It requires bespoke tools for edition, and while CSV is absolute garbage it can be ingested and produced by most spreadsheet software, as well as databases.

Re: A love letter to the CSV format

#202

Post by gopher_space »

nomel wrote: post #178
Someone1234 wrote: post #107

Earlier quoted context omitted.

For context: I have a LOT of experience of interchange formats, like "full time job, every day, all day, hundreds of formats, for 20-years" experience. Based on that experience I have come to one key, but maybe, counter-intuitive truth about interchange formats: - Too much freedom is bad . Why? Generating interchange data is cheaper than consuming it, because the creator only needs to consider the stuff they want to…

Oh, this is interesting. Are you tying different systems together? If so, do you use some preferred intermediate format? Do you have a giant library of * -> intermediate -> * converters that you sprinkle between everything? Or maybe the intermediate format is in memory? What about Parquet and the like?

Not the person you were replying to, but from my experience CSV is a good place to define data types coming into a system and a safe way to dump data as long as I write everything down.

So I might do things like have every step in a pipeline begin development by reading from and writing to CSV. This helps with parallel dev work and debugging, and is easy to load into any intermediate format.

> do you use some preferred intermediate format?

This is usually dictated by speed vs money calculations, weird context issues, and familiarity. I think it's useful to look at both "why isn't this a file" and "why isn't this all in memory" perspectives.

Re: A love letter to the CSV format

#203

Post by munchler »

relistan wrote: post #198

CSV is bad. Furthermore it’s unnecessary. ASCII has field and record separator characters that were for this purpose.

That would be great if keyboards had keys for those characters and there was a common way to display them on a screen, but they don't and there isn't.

Re: A love letter to the CSV format

#204

Post by mort96 »

oezi wrote: post #150
mort96 wrote: post #100

Earlier quoted context omitted.

> It's saving semicolon-separated CSV files with the comma as a decimal point. It's not though, is what I'm saying. It's saving semicolon-separated files, not CSV files. CSV files have commas separating the values. Saying that Excel saves "semicolon-separated CSV files" is nonsensical. I can save binary data in a .txt file, that doesn't make it a "text file with binary data"; it's a binary file with a stupid name.

Sorry, but what Excel does is save to a file with a CSV extension. This format is well defined and includes ways to specify encoding and separator to be readable under different locales. This format is not comma separated values. But Excel calls it CSV. The headaches comes if people assume that a csv file must be comma separated.

I don't care what Excel calls it. As I said, if I name a file .txt but stuff it with binary data, it's not a text file.

Re: A love letter to the CSV format

#205

Post by anilakar »

criddell wrote: post #117
boricj wrote: post #15

Earlier quoted context omitted.

Your comma isn't my comma. French systems use the comma as a decimal point for numbers and we use semicolons to separate fields in CSV files.

Most of the people most of the time aren't importing data from a different locale. A good assumption for defaults could be that the CSV file honors the current Windows regional settings.

If it only was that easy. Experience has shown that the only reliable way is to run heuristics against the first few lines of the file.

There are office programs that save CSV with the proper comma delimiter regardless of the locale.

There are people who run non-local locales for various good reasons.

There are technically savvy people who have to deal with CSV shenanigans and can and will send it with the proper comma delimiter.

Re: A love letter to the CSV format

#206

Post by togakangaroo »

evnp wrote: post #138
Suppafly wrote: post #102

Earlier quoted context omitted.

That mostly breaks down to "excel is intentionally stupid with csv files if you don't use the import function to open them" along with the normal "don't trust customer input without stripping or escaping it" concerns you'd have with any input.

That was my initial reaction as well – it's a vulnerability in MS software, not ours, not our problem. Unfortunately, reality quickly came to bear: our customers and employees ubiquitously use excel and other similar spreadsheet software, which exposes us and them to risk regardless where the issue lies. We're inherently vulnerable because of the environment we're operating in, by using CSV. "don't trust customer inp…

Hey, I'm the author of the linked article, cool to see this is still getting passed around.

Definitely agree there's no perfect solution. There's some escaping that seems to work ok, but that's going to break CSV-imports.

An imperfect solutions is that applications should be designed with task-driven UIs so that they know the intended purpose of a CSV export and can make the decision to escape/not escape then. Libraries can help drive this by designing their interfaces in a similar manner. Something like `export_csv_for_eventual_import()`, `export_csv_for_spreadsheet_viewing()`.

Another imperfect solution would be to ... ugh...generate exports in Excel format rather than CSV. I know, I know, but it does solve the problem.

Or we could just get everyone in the world to switch to emacs csv-mode as a csv viewer. I'm down with that as well.

Re: A love letter to the CSV format

#207

Post by theoryofx »

k_bx wrote: post #156

I've recently been developing a raspberry pi based solution which works with telemetry logs. First implementation used an SQLite database (with WAL log) – only to find it corrupted after just couple of days of extensive power on/off cycles. I've since started looking at parquet files – which turned out to not be friendly to append-only operations. I've ended up implementing writing events into ipc files which then pe…

> First implementation used an SQLite database (with WAL log) – only to find it corrupted after just couple of days of extensive power on/off cycles.

Did you try setting `PRAGMA synchronous=FULL` on your connection? This forces fsync() after writes.

That should be all that's required if you're using an NVMe SSD.

But I believe most microSD cards do not even respect fsync() calls properly and so there's technically no way to handle power offs safely, regardless of what software you use.

I use SanDisk High Endurance SD cards because I believe (but have not fully tested) that they handle fsync() properly. But I think you have to buy "industrial" SD cards to get real power fail protection.

Re: A love letter to the CSV format

#209

Post by bb01100100 »

Someone1234 wrote: post #118
liotier wrote: post #93

CSV is the bane of my existence. There is no reason to use it outside of legacy use-cases, when so many alternatives are not so brittle that they require endless defensive hacks to avoid erring as soon as exposed to the universe. CSV must die.

CVS isn't brittle, and I'm not sure what "hacks" you're referring to. If you or your parser just follow RFC4180 (particularly quote every field, and double quoting to cancel-quote), that will get you 90%+ compatibility.

Surely you’ve come across situations where line number 10,000,021 of a 60m line CSV fails to parse because there aren’t enough fields in that line of the file…? The issue is that you can’t definitively know which of the 50 fields is missing, so you have to fail the line or worse the file.

In my experience (perhaps more niche than yours since you mentioned it has been your day job), the lack of fall back options makes for brittle integrations. Failing entire files due to a borked row can be expensive in terms of time.

Having to ingest large CSV files from legacy systems has made me rethink the value of XML, lol. Types and schemas add complexity for sure, but you get options for dealing with variances in structure and content.

Re: A love letter to the CSV format

#210

Post by freehorse »

hajile wrote: post #169
packetlost wrote: post #151

Earlier quoted context omitted.

You're missing my point: basically nothing spits out data in that format because it's not ergonomic to do so. JSON is designed to represent object hierarchies, not tabular data.

CSV is lists of lists of fixed length. JSON is lists of lists of any length and groups of key/value pairs (basically lisp S-expressions with lots of unnecessary syntax). This makes it a superset of CSV's capabilities. JSON fundamentally IS made to represent tabular data, but it's made to represent key-value groups too. Why make it able to represent tabular data if that's not an intended use?

The fact that json can represent a superset of tabular data structures that csv is specifically designed to represent can be rephrased into that csv is more specialised than json in representing tabular data. The fact that json can also represent tabular data does not mean it is a better or more efficient way to represent that data instead of a format like csv.

In the same way, there are hierarchically structured datasets that can be represented by both json in hierarchical form and csv in tabular form by repeating certain variables, but if using csv would require repeating them too many times, it would be a bad idea to choose that instead of json. The fact that you can do sth does not always make it a good idea to do it. The question imo is about which way would be more natural, easy or efficient.

Post reply on HN