3 ms·

> 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

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

ralphael 6y ago
..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....
theptip 6y ago
> 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.
- segfaultbuserr 6y ago
  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.
  - theptip 6y ago
    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.
- theptip 6y ago
  Sorry, typo on the encoding point -- I meant to say that I've seen issues with UTF-8 vs. Latin-1, not "Linux encoding".
- shalmanese 6y ago
  How to encode data with newlines into CSV files is always a fun morass to wander into.
- unishark 6y ago
  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.
ashtonkem 6y ago
I feel like text-based is colloquial shorthand for "schemaless" or "dynamically typed".
- jeltz 6y ago
  CSV is not schema less and it does not have to be any more dynamically typed than XML either.
  - ashtonkem 6y ago
    It doesn't have to be, but it often is.
shalmanese 6y ago
> 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 https://en.wikipedia.org/wiki/Comma-separated_values#History

ralphael 6y ago

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

theptip 6y ago

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

segfaultbuserr 6y ago
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.
- theptip 6y ago
  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.
theptip 6y ago
Sorry, typo on the encoding point -- I meant to say that I've seen issues with UTF-8 vs. Latin-1, not "Linux encoding".
shalmanese 6y ago
How to encode data with newlines into CSV files is always a fun morass to wander into.
unishark 6y ago
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.

ashtonkem 6y ago

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

jeltz 6y ago
CSV is not schema less and it does not have to be any more dynamically typed than XML either.
- ashtonkem 6y ago
  It doesn't have to be, but it often is.

shalmanese 6y ago

> 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 https://en.wikipedia.org/wiki/Comma-separated_values#History