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

251–260 of 711 posts

Re: A love letter to the CSV format

#251

Post by dietr1ch »

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 don't understand why CSV became a thing when TSV, or a format using the nowadays weird ASCII control characters like start/end of text, start of heading, horizontal/vertical tab, file/group/record/unit separator.

It seems many possible designs would've avoided the quoting chaos and made parsing sort of trivial.

Re: A love letter to the CSV format

#252

Post by hermitcrab »

testudovictoria wrote: post #157
mitchpatin wrote: post #39

CSV still quietly powers the majority of the world’s "data plumbing." At any medium+ sized company, you’ll find huge amounts of CSVs being passed around, either stitched into ETL pipelines or sent manually between teams/departments. It’s just so damn adaptable and easy to understand.

Insurance. One of the core pillars of insurance tech is the CSV format. You'll never escape it.

>You'll never escape it.

I see what you did there.

Re: A love letter to the CSV format

#253

Post by ciupicri »

bee_rider wrote: post #126
mort96 wrote: post #74

Earlier quoted context omitted.

Aaaand now I can fit like 3 fields next to each other before they wrap and the field with arbitrary length text still misaligns the field after it

There is at least a chance that the text fields will fit in a tab. If not, a tool like “column” on Linux can be used. There is no chance that a text field will fit inside the width of a comma.

I was about to mention that column [1] is part of util-linux, so perhaps it's Linux specific, but then I noticed this in the FreeBSD man page [2]:

> The column command appeared in 4.3BSD-Reno.

[1]: https://man7.org/linux/man-pages/man1/column.1.html

[2]: https://man.freebsd.org/cgi/man.cgi?query=column&sektion=1

Re: A love letter to the CSV format

#254

Post by hermitcrab »

I wrote my own CSV parser in C++. I wasn't sure what to do in some edge cases, e.g. when character 1 is space and character 2 is a quote. So I tried importing the edge case CSV into both MS Excel and Apple Numbers. They parsed it differently!

Re: A love letter to the CSV format

#255

Post by qw »

tfvlrue wrote: post #223
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…

In the past I remember that Excel not properly handling UTF-8 encoded text in a CSV. It would treat it as raw ASCII (or possibly code page 1252). So if you opened and saved a CSV, it would corrupt any Unicode text in the file. It's possible this has been fixed in newer versions, I haven't tried in a while.

It's related to how older versions of Windows/Office handled Unicode in general.

From what I have heard, it's still an issue with Excel, although I assume that Windows may handle plain text better these days (I haven't used it in a while)

You need to write an UTF-8 BOM at the beginning (0xEF, 0xBB, 0xBF), if you want to make sure it's recognized as UTF-8.

Re: A love letter to the CSV format

#256

Post by timacles »

meepmorp wrote: post #189
hajile wrote: post #169

Earlier quoted context omitted.

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?

> CSV is lists of lists of fixed length. I'd definitely put that in my list of falsehoods programmers believe about CSV files.

CSV is a text file that might have commas in it

Re: A love letter to the CSV format

#258

Post by [deleted] »

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?

[deleted]

Re: A love letter to the CSV format

#259

Post by evnp »

togakangaroo wrote: post #206
evnp wrote: post #138

Earlier quoted context omitted.

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. Librar…

Appreciate your work! Your piece was pivotal in changing my mind about whether this should be considered in our purview to address.

The intention-based philosophy of all this makes a lot of sense, was eye opening, and I agree it should be the first approach. Unfortunately after considering our use cases, we quickly realized that we'd have no way of knowing how customers intend to use the csv exports they've requested - we've talked to some of them and it's a mix. We could approach things case by case but we really just want a setup which works well 99% of the time and mitigates known risk. We settled on the prefixing approach and have yet to receive any complaints about it, specifically using a space character with the mind that something unobtrusive (eg. easily strippable) but also visible, would be best - to avoid quirks stemming from something completely hidden.

Thank again for your writing and thoughts, like I said above I haven't found much else of quality on the topic.

Re: A love letter to the CSV format

#260

Post by deathanatos »

jszymborski wrote: post #159
deathanatos wrote: post #139

Earlier quoted context omitted.

…ndjson is streamable, too…

I like ndjson and jsonl just fine, but unless I need a more complicated structure, it's not worth the extra hassle of parsing JSON.

… what concrete language are we talking about, here?

In literally any language I can think of, hassle(json) < hassle(CSV), esp. since CSV received is usually "CSV, but I've screwed it up in a specific, annoying way"

Post reply on HN