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

621–630 of 711 posts

Re: A love letter to the CSV format

#621

Post by sirukinx »

For everyone complaining about CSV/TSV, there's a scripting language called R.

It makes working with CSV/TSV files super simple.

It's as easy this:

# Import tidyverse after installing it with install.packages("tidyverse")

library(tidyverse)

# Import TSV

dataframe_tsv # Import CSV

dataframe_csv # Mangle your data with dplyr, regular expressions, search and replace, drop NA's, you name it.

Multiple libraries exist for R to move data around, change the names of entire columns, change values in every single row with regular expressions, drop any values that have no assigned value, it's the swiss army knife of data. There are also all sorts of things you can do with data in R, from mapping with GPS coordinates to complex scientific graphing with ggplot2 and others.

Here's an example for reading iButton temperature sensor data: https://github.com/hominidae/ibutton_tempsensors/

Notice that in the code you can do the following to skip leading lines by passing it as an argument: skip = 18

cf1h <- read_csv("data/Coldframe_01_High.csv", skip = 18)

Re: A love letter to the CSV format

#622

Post by evnp »

imtringued wrote: post #582
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…

If your customers and employees are using Excel then stop going against the grain with your niche software developer focused formats that need a lot of explanations. I need to interface with a lot of non-technical people who exclusively use Excel. I give them .xlsx files. It's just as easy to export .xlsx as it is to export .CSV and my customers are happy.

How is .csv a niche dev-focused format? Our customers use our exports for a mix of purposes, some of them involving spreadsheet clients (not just excel) and some of them integrating with their own data pipelines. Csv conveniently works with these use cases across the board, without explanation, and is inconveniently saddled with these legacy security flaws in Excel (and probably other clients).

If xlsx works for all your use cases that's great, a much better solution that trying to sidestep these issues by lightly modifying the data. It's not an option for us, and (I'd imagine) a large contingent of export tools which can't make assumptions about downstream usage.

Re: A love letter to the CSV format

#623

Post by SkyBelow »

IanCal wrote: post #403
lelanthran wrote: post #396

Earlier quoted context omitted.

To be honest, I'm wondering why you are rating JSON higher than CSV. > Unlike XML or JSON, there isn't a document defining the grammar of well-formed or valid CSV files, There is, actually, RFC 4180 IIRC. > there are many flavours that are incompatible with each other in the sense that a reader for one flavour would not be suitable for reading the other and vice versa. "There are many flavours that deviate from the s…

Have you had to work with csv files from the wild much? I'm not being snarky but what you're talking about is night and day to what I've experienced over the years. There aren't vast numbers of different JSON formats. There's practically one and realistically maybe two. Headers are in each line, utf8 has never been an issue for me and quoting and escaping are well defined and obeyed. This is because for datasets, alm…

I wonder if CSV is the trivial format, so you have many people picking it because they want the easiest, and still getting it wrong. JSON is harder, so very few people are going to roll their own serializer/deserializer, and those who do are more likely to focus on getting it right (or at least catching the really obvious bugs).

I've dealt with incorrect CSVs numerous times, never with incorrect JSON, but, of the times I know what was happening on the other system, each time the CSV was from some in house (or similar) implementation of dumping a SQL output (or similar) into a text file as an MVP. JSON was always using some library.

If so, that's all the more reason to love CSV as it stands guard for JSON. If CSV didn't exist, we would instead have broken JSON implementations. (JSON and XML would likely then share a similar relationship.)

Re: A love letter to the CSV format

#624

Post by byyll »

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 was recently writing a parser for a weird CSV. It had multiple header column rows in it as well as other header rows indicating a folder.

Re: A love letter to the CSV format

#625

Post by Gormo »

imtringued wrote: post #569
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 agree and as a result I have completely abandoned CSV. I use the industry standard that everyone understands: ECMA-376, ISO/IEC 29500 aka .xlsx. Nobody has any problems producing or ingesting .xlsx files. The only real problem is the confusion between numbers and numeric text that happens when people use excel manually. For machine to machine communication .xlsx has never failed me.

Parsing Excel files in simple data interchange use cases that don't involve anyone manually using spreadsheets is an instance of unnecessary complexity. There are plenty of alternatives to CSV that remain plaintext, have much broader support, and are more rigorous than Excel in ensuring data consistency. You can use JSON, XML, ProtoBuf, among many other options.

Re: A love letter to the CSV format

#626

Post by Gormo »

mjw_byrne wrote: post #601
SoftTalker wrote: post #596

Earlier quoted context omitted.

Not for text data. Those values are not text characters like , or " are, and have only one meaning. It would be like arguing that 0x41 isn't always the letter "A". For binary files, yeah but you don't see CSV used there anyway.

The idea that binary data doesn't go in CSVs is debatable; people do all sorts of weird stuff. Part of the robustness of a format is coping with abuse. But putting that aside, if the control chars are not text, then you sacrifice human-readability and human-writability. In which case, you may as well just use a binary format.

I've dealt with a few cases of CSVs including base64-encoded binary data. It's an unusual scenario, but the tools for working with CSVs are robust enough that it was never an issue.

Re: A love letter to the CSV format

#627

Post by Gormo »

realitysballs wrote: post #499
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…

They do specifically mention this: “No one owns CSV. It has no real specification (yes, I know about the controversial ex-post RFC 4180), just a set of rules everyone kinda agrees to respect implicitly. It is, and will forever remain, an open and free collective idea.”

That's true of the vast majority of protocols that people use in real life to exchange date. We're using one of them right now, in fact.

Re: A love letter to the CSV format

#628

Post by j45 »

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…

CSV is a data-exchange format.

Re: A love letter to the CSV format

#629

Post by Gormo »

SkyBelow wrote: post #623
IanCal wrote: post #403

Earlier quoted context omitted.

Have you had to work with csv files from the wild much? I'm not being snarky but what you're talking about is night and day to what I've experienced over the years. There aren't vast numbers of different JSON formats. There's practically one and realistically maybe two. Headers are in each line, utf8 has never been an issue for me and quoting and escaping are well defined and obeyed. This is because for datasets, alm…

I wonder if CSV is the trivial format, so you have many people picking it because they want the easiest, and still getting it wrong. JSON is harder, so very few people are going to roll their own serializer/deserializer, and those who do are more likely to focus on getting it right (or at least catching the really obvious bugs). I've dealt with incorrect CSVs numerous times, never with incorrect JSON, but, of the tim…

Sometimes people interpret the term too generically and actually implement a high degree of non-trivial, very idiosyncratic complexity, while still calling it "CSV".

One project I worked on involved a vendor promising to send us data dumps in "CSV format". When we finally received their "CSV" we had to figure out how to deal with (a) global fields being defined in special rows above the header row, and (b) a two-level hierarchy of semicolon-delimited values nested within comma-delimited columns. We had to write a custom parser to complete the import.

Post reply on HN