Hacker News Reader

Comment by tannhaeuser | Hacker News Reader

> exchange formats are text based, with labs saying they will upgrade to „modern xml formats“

Worth noting that XML is also a text format. SGML even can treat CSVs as markup. There's nothing wrong with CSVs/TSVs anyway - it's a concise tabular format using only minimal special coding for a record and a field separator, as envisioned by ASCII and EDIFACT. The problem seems more like that there was no error checking in place to capture file write errors, or more generally the use of non-reproducible, manual operating practices which seems common in data processing.

View on HN

> There's nothing wrong with CSVs/TSVs

I can see the theoretical purity of this statement, but based on my experience working with CSV files generated by actual non-technical users I have to disagree here.

There are a number of footguns here that are really subtle and the average non-technical user has no hope of spotting them.

Problems that I've seen in the wild, off the top of my head:

* Windows vs. Linux line terminators breaks some CSV libraries.

* Encoding can change depending on what program emitted the CSV file, and auto-detecting encoding is not perfect. For example, Excel for Mac uses Linux encoding by default, IIRC.

* Excel does wacky things when you export a "CSV" in the wrong format; real users use Excel to generate their CSVs, not Python. For example if you import the string "0123456789" in an Excel sheet, it infers "number" and strips the leading "0" when you export. Now your bank account/routing numbers are invalid!

* "What's a TSV?" -- if users use CSV, how do you handle commas in the data? It's nontrivial to train users to do their CSV upload as a TSV.

Etc.

In practice we needed to build a fairly beefy helpdesk article with accumulated wisdom on how to not break your CSV exports, and most users don't read/remember these steps until they experience the trauma first-hand.

I'd say the CSV format is deceptively simple -- it's quite easy to do the right thing as a developer where the source and sink are both code you control, but in the wild it gets messy really quickly.

Sorry, typo on the encoding point -- I meant to say that I've seen issues with UTF-8 vs. Latin-1, not "Linux encoding".

Almost none [0] of the problem, such as line terminators, encodings, or escape characters, is an inherent flaw in the idea of using a text-based file to represent data. They can be fixed by just a little bit of standardization. But unfortunately, I don't think it's possible, we're stuck at here.

[0] Except type conversion, which is a real problem.

Agreed; I think the crux of the problem is that CSV is not a format that supports metadata. And even if you somehow created such a convention/standard, it's impossible to upgrade all the existing users with 10+ year-old Excel installs; that ship has already sailed.

How to encode data with newlines into CSV files is always a fun morass to wander into.

You have to navigate the same basic issues to convert each csv as it comes in to xls as PHE did in the article. They could just as well have converted them to a consistent csv format and stopped there.

> There's nothing wrong with CSVs/TSVs anyway

The first CSV file was created in 1983. The first CSV standard was created in 2005[1].

The two decades of CSV surviving as an informal standard means that it takes minutes to make a 95% complete CSV parser and an infinite amount of time to make a 99.99% complete CSV parser.

[1] https://en.wikipedia.org/wiki/Comma-separated_values#History

..this right here. No error checking, inadequate testing, no reconciliation process.

Excel is used extensively in many industries. Any file could be cut off in processing by any number of reasons, one off errors for e.g.

So the solution is to "fix" the process by using the existing broken process and smaller files....

I feel like text-based is colloquial shorthand for "schemaless" or "dynamically typed".

CSV is not schema less and it does not have to be any more dynamically typed than XML either.

It doesn't have to be, but it often is.

Reply on news.ycombinator.com