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

611–620 of 711 posts

Re: A love letter to the CSV format

#611

Post by saulpw »

mjw_byrne wrote: post #594
SoftTalker wrote: post #590

Earlier quoted context omitted.

If only there were character codes specifically meant to separate fields and records.... we wouldn't have to worry so much about quoted commas or quoted quotes.

There's just no such thing as a delimiter which won't find its way into the data. Quoting and escaping really are the only robust way.

You can disallow all control characters (ASCII < 32) other than CR/LF/TAB, which is reasonable. I don't know of any data besides binary blobs which uses those. I've never heard of anyone inlining a binary file (like an image) into a "CSV" anyway.

Re: A love letter to the CSV format

#612

Post by dietr1ch »

mjw_byrne wrote: post #508
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

Exactly. "Use a delimiter that's not in the data" is not real serialisation, it's fingers-crossed-hope-for-the-best stuff. I have in the past does data extractions from systems which really can't serialise properly, where the only option is to concat all the fields with some "unlikely" string like @#~!$ as a separator, then pick it apart later. Ugh.

> Exactly. "Use a delimiter that's not in the data" is not real serialisation, it's fingers-crossed-hope-for-the-best stuff.

It's not doing just this, you pick something that's likely not in the data, and then escape things properly. When writing strings you can write a double quote within double quotes with \", and if you mean to type the designated escape character you just write it twice, \\.

The only reason you go for something likely not in the data is to keep things short and readable, but it's not impossible to deal with.

Re: A love letter to the CSV format

#613

Post by TRiG_Ireland »

jwr wrote: post #522

I so hate CSV. I am on the receiving end: I have to parse CSV generated by various (very expensive, very complicated) eCAD software packages. And it's often garbage. Those expensive software packages trip on things like escaping quotes. There is no way to recover a CSV line that has an unescaped double quote. I can't point to a strict spec and say "you are doing this wrong", because there is no strict spec. Then ther…

I worked in a web shop which had to produce spreadsheets which people wanted to look at in Excel. I gave them so many options, and told each client to experiment and choose the option which worked for them. In the end, we had (a) UTF-8 CSV, (b) UTF-8 CSV with BOM, (c) UTF-16 TSV, (d) UTF-8 HTML table with a .xlsx file extension and a lying Content-Type header which claimed it was an Excel spreadsheet.

Option a worked fine so long as none of the names in the spreadsheet had any non-ASCII characters.

Option d was by some measures the worst (and was definitely the largest file size), but it did seem to consistently work in Excel and Libre Office. In fact, they all worked without any issue in Libre Office.

Re: A love letter to the CSV format

#614

Post by deepsun »

ethbr1 wrote: post #548
mjw_byrne wrote: post #531

Earlier quoted context omitted.

I used to be a data analyst at a Big 4 management consultancy, so I've seen an awful lot of this kind of thing. One thing I never understood is the inverse correlation between "cost of product" and "ability to do serialisation properly". Free database like Postgres? Perfect every time. Big complex 6-figure e-discovery system? Apparently written by someone who has never heard of quoting, escaping or the difference bet…

> Big complex 6-figure e-discovery system? Apparently written by someone who has never heard of quoting... It's because about a certain size, system projects are captured by the large consultancy shops, who eat the majority of the price in profit and management overhead... ... and then send the coding work to a lowest-cost someone who has never heard of quoting, etc. And it's a vicious cycle, because the developers i…

Just a nitpick about consultancy shops -- I've had a chance of working in one in eastern europe and noticed that it's approach to quality was way better than client's. It also helped that client paid by hours, so consultancy company was incentivized to spend more time on refactorings, improvals and testing (with constant pushback from client).

So I don't buy the consultancy company sentiment, it always boils down to engineers and incentives.

Re: A love letter to the CSV format

#615

Post by eddythompson80 »

rkagerer wrote: post #383
jll29 wrote: post #354

The post should at least mention in passing the major problem with CSV: it is a "no spec" family of de-facto formats, not a single thing (it is an example of "historically grown"). And omission of that meams I'm going to have to call this our for its bias (but then it is a love letter, and love makes blind...). Unlike XML or JSON, there isn't a document defining the grammar of well-formed or valid CSV files, and ther…

Waiting for someone to write a love letter to the infamous Windows INI file format I actually miss that. It was nice when settings were stored right alongside your software, instead of being left behind all over a bloated registry. And the format was elegant, if crude. I wrote my own library for encoding/writing/reading various datatypes and structure into ini's, in a couple different languages, and it served me well…

> instead of being left behind all over a bloated registry

Really? I think the idea of a central, generic, key-value pair database for all the setting on a system is probably the most elegant reasonable implementation there could be.

The initial implementation of Windows Registry wasn't good. It was overly simplistic and pretty slow. Though the "bloat" (what ever that means) of registry hasn't been an actual issue in over 20 years. The only people invested in convincing you "it's an issue" are CCleaner type software that promise to "speed up your computer" if you just pay $6.99.

How many rows do you need in a sqlite database for it to be "bloated"?

Re: A love letter to the CSV format

#616

Post by Suppafly »

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…

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

That's honestly a good solution and the end users prefer it anyway.

Re: A love letter to the CSV format

#617

Post by Suppafly »

conductr wrote: post #277
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…

I’ve almost always found the simple way around Excel users not knowing how to safely use CSV files is to just give the file another extension: I prefer .txt or .dat Then, the user doesn’t have Excel has the default program for opening the file and has to jump through a couple safety hoops

>Then, the user doesn’t have Excel has the default program for opening the file and has to jump through a couple safety hoops

Nah, they just quickly learn to rename it .csv or .xls and excel will open it

Re: A love letter to the CSV format

#618

Post by ahoka »

remram wrote: post #564
danso wrote: post #533

Earlier quoted context omitted.

> Then there will be your data, but they will format currency values as prettified strings, for example "34 593,12 USD", instead of producing one column with a number and another with currency. To be fair, that's not a problem with CSV but with the provider's lack of data literacy.

Yeah, you can also use Parquet/JSON/protobuf/XLSX and store numbers as strings in this format. CSV is just a container.

But somehow CSV is the PHP of serialization formats, attracts the wrong kind of developers and projects.

Re: A love letter to the CSV format

#619

Post by Suppafly »

calfuris wrote: post #184
Suppafly wrote: post #97

Earlier quoted context omitted.

This, it's dumb but Excel handles csv way better if you 'import' it vs just opening it. I use excel to quickly preview csv files, but never to edit them unless I'm OK only ever using it in Excel afterwards.

Even in that case I'd be hesitant to open a CSV file in excel. The problem is that it will automatically apply whatever transformation it thinks is appropriate the moment you open the file. Have a digit string that isn't semantically a number? Too bad, it's a number now, and we're gonna go ahead and round it. You didn't really need _all_ of the digits of that insurance policy number, did you? They did finally add opt…

I've noticed recently, they ask you about some of the transformations with a popup instead of automatically doing them when you open csv files.

Re: A love letter to the CSV format

#620

Post by ziml77 »

taeric wrote: post #576
pasc1878 wrote: post #567

Earlier quoted context omitted.

Not if it is split at a line e.g. if the source or target can only deal with a fixed number of lines.

Right, that is what I meant about that being unlikely? Most instances of truncated files that I have seen were because of size, not lines. Still, a fair point.

Really depends on how the CSV is generated/transferred. If the output of the faulting software is line-buffered then it's quite likely that a failure would terminate the file at a line break.
Post reply on HN