Remember Excel import issues causing a change in Guidelines for human gene nomenclature | Nature Genetics
Posted by jpluimers on 2023/10/23
Remember [Wayback/Archive] Guidelines for human gene nomenclature | Nature Genetics?**
You might not, but this was what pointed me to it back in 2020: [Wayback/Archive] Scientists rename human genes to stop Microsoft Excel from misreading them as dates – The Verge.
The article was a result of Excel mangling import data for decades. Somehow finally it did get Microsoft’s attention and more than 3 years later, they issued options (with mangling still being the default) to help workaround the problems.
The 2004 article [Wayback/Archive] Mistaken Identifiers: Gene name errors can be introduced inadvertently when using Excel in bioinformatics | BMC Bioinformatics | Full Text demonstrated this import problem which had been present for quite a while already (it even has a csh Script to scan for SymbolMutation error).
The gene nomenclature people by now have moved to a different naming scheme, but maybe other people can benefit from the Excel updates of which you can find more through these links:
- [Wayback/Archive] Control data conversions in Excel for Windows and Mac which remarkably does mention the gene nomenclature article at all but does mention
Known issues
- The feature does not support disabling these conversions during macro execution.
Here is the new Data Options dialog (which does not support removing the
+sign in the ITU-T international standards E.123 and E.164 for telephone number formats: that still gets mangled as per ):
…
- You can enable or disable the following options:
- Remove leading zeros from numerical text and convert to a number.
- Truncate numerical data to 15 digits of precision and convert to a number that may be displayed in scientific notation, if needed.
- Convert numerical data surrounding the letter “E” to a number displayed in scientific notation.
- Convert a continuous string of letters and numbers to a date.
- When you select the When loading a .csv file or similar file, notify me of any automatic number conversions check box, Excel displays a warning message when it detects that at least one of the optional automatic data conversions is enabled and about to occur when opening a .csv or .txt file. The message gives the ability to open the file once without converting the data.
- [Wayback/Archive] KC Lemson on X: “Remember these headlines from a few years ago? This week my team in #Excel started rolling out a new feature that addresses it :-)”
But [Wayback/Archive] 🐘 Typing Loudly 🐘 on X: “@kclemson @JenMsft Hi, I’m a telecom architect. I import large amounts of call records, (usually in .csv format) into Excel often. What can we do about phone numbers, particularly ones in E164 format? +15558675309”.
Of course El Reg poked fun on the import issues in 2004 and 2020:
- [Wayback/Archive] Excel ate my DNA • The Register
- [Wayback/Archive] Geneticists throw hands in the air, change gene naming rules to finally stop Microsoft Excel eating their data • The Register
and these pointed me to these related links:
- [Wayback/Archive] Scientists had to rename human genes because Microsoft Excel confuses them as dates – Variable
- [Wayback/Archive] Gene name errors and Excel: lessons not learned | What You’re Doing Is Rather Desperate
- [Wayback/Archive] Importing gene symbol data into Excel correctly – YouTube (wich applies to anyone importing data into older Excel versions)
--jeroen
** Without payment, you can only view the above Nature article, but not print or download (see [Wayback/Archive] SharedIt | Springer Nature | For Researchers | Springer Nature). If you are part of an institution that pays Nature or pay by yourself, you can print and download through [Wayback/Archive] Guidelines for human gene nomenclature | Nature Genetics. Mere mortals can print through [Wayback/Archive] Guidelines for Human Gene Nomenclature – PMC.
The Wiert Corner - irregular stream of stuff 
Leave a comment