3 ms·

Anyone who has written software to import CSV files knows that in general it's a complete mess. Also, geneticists have been forced to rename certain gene names

Anyone who has written software to import CSV files knows that in general it's a complete mess. Also, geneticists have been forced to rename certain gene names because Excel messed them up[0]. It would be nice if this standard would be generally accepted. This standard is from 2005 and it is pretty clear that it is not universally used. [0] https://www.theregister.com/2020/08/06/excel_gene_names/ https://www.theregister.com/2020/08/06/excel_gene_names/

olau 5y ago
Excel is the ultimate bane of CSV files. If Microsoft would fix Excel and distribute the fixes to old Excels, then life would be good again.
- chrismorgan 5y ago
  But they will never do that because that would break existing systems that depend on the old behaviour.
  - wolrah 5y ago
    > But they will never do that because that would break existing systems that depend on the old behaviour. Which is an inevitability for anything that's been a dependency for long enough. Either you admit that things have to change and sometimes shit that's been secretly broken for years will have to change too, or you give in to the shit and admit that you can never solve any problems that made it to production. Both answers suck, but one is a lot more suck so I choose the "fix it anyways and if something else turns out to have depended on the brokeness then add that to the list of shit to fix" approach whenever possible.
- aliasEli 5y ago
  It is not just Excel, there are a lot of systems that export CSV. Many of them are broken. Examples: - double quotes in text fields - using the wrong decimal separator, should it be "," or "." - using the wrong column separator, should be "," or ";"
- WorldMaker 5y ago
  The "General" data column behavior thing that caused the incorrect parsing of gene names as Dates goes all the way back to the original Visicalc. Are you suggesting Microsoft should patch Visicalc and Lotus 1-2-3 as well?
FranOntanaya 5y ago
By that logic though, all text formats are a mess because text processors will autocorrect and break the formatting.
dspillett 5y ago
We've had similar issues with clients using 1-2-1 as a meeting type name. When data about those records are exported to CSV and loaded by Excel, it gets seen as the date 2001-02-01 (or, presumably, 2001-01-02 for those using a US locale in their setup) even if the value is quoted (which we hoped would be interpreted as "this value is definitely nothing fancier than a basic string", but it was not). > This standard is from 2005 and it is pretty clear that it is not universally used. Unfortunately a lot of non-conforming software can't change or they risk compatibility problems with old versions of themselves. 2005 is relatively recent in the history of CSV files.
- viraptor 5y ago
  Opening CSV in excel rather than importing is pretty much broken by design. Import data from text and you can set the column types. Open by default and suffer consequences.
  - dspillett 5y ago
    You are not at all wrong, but unfortunately "you are opening it wrong" did not fly with the client for us in this case.

olau 5y ago

Excel is the ultimate bane of CSV files. If Microsoft would fix Excel and distribute the fixes to old Excels, then life would be good again.

chrismorgan 5y ago
But they will never do that because that would break existing systems that depend on the old behaviour.
- wolrah 5y ago
  > But they will never do that because that would break existing systems that depend on the old behaviour. Which is an inevitability for anything that's been a dependency for long enough. Either you admit that things have to change and sometimes shit that's been secretly broken for years will have to change too, or you give in to the shit and admit that you can never solve any problems that made it to production. Both answers suck, but one is a lot more suck so I choose the "fix it anyways and if something else turns out to have depended on the brokeness then add that to the list of shit to fix" approach whenever possible.
aliasEli 5y ago
It is not just Excel, there are a lot of systems that export CSV. Many of them are broken. Examples: - double quotes in text fields - using the wrong decimal separator, should it be "," or "." - using the wrong column separator, should be "," or ";"
WorldMaker 5y ago
The "General" data column behavior thing that caused the incorrect parsing of gene names as Dates goes all the way back to the original Visicalc. Are you suggesting Microsoft should patch Visicalc and Lotus 1-2-3 as well?

FranOntanaya 5y ago

By that logic though, all text formats are a mess because text processors will autocorrect and break the formatting.

dspillett 5y ago

We've had similar issues with clients using 1-2-1 as a meeting type name. When data about those records are exported to CSV and loaded by Excel, it gets seen as the date 2001-02-01 (or, presumably, 2001-01-02 for those using a US locale in their setup) even if the value is quoted (which we hoped would be interpreted as "this value is definitely nothing fancier than a basic string", but it was not). > This standard is from 2005 and it is pretty clear that it is not universally used. Unfortunately a lot of non-conforming software can't change or they risk compatibility problems with old versions of themselves. 2005 is relatively recent in the history of CSV files.

viraptor 5y ago
Opening CSV in excel rather than importing is pretty much broken by design. Import data from text and you can set the column types. Open by default and suffer consequences.
- dspillett 5y ago
  You are not at all wrong, but unfortunately "you are opening it wrong" did not fly with the client for us in this case.