4 ms·

>Excel tries to interpret fields with digits as numbers, stripping off leading 0's, etc. (think serial numbers or whatever with leading 0's) Fyi for those unaw

by jasode 6y ago

>Excel tries to interpret fields with digits as numbers, stripping off leading 0's, etc. (think serial numbers or whatever with leading 0's) Fyi for those unaware... many other tools other than MS Excel also remove leading zeros and interpret as number. Examples of users asking questions on how to preserve them in other software: - Google Sheets issue with leading zeros: https://webapps.stackexchange.com/questions/120835/importdata-in-google-spreadsheet-and-keep-the-leading-zero https://webapps.stackexchange.com/questions/120835/importdat... - LibreOffice: https://ask.libreoffice.org/en/question/246295/how-can-i-import-a-csv-in-calc-with-the-leading-zeros-that-are-in-the-csv-file/ https://ask.libreoffice.org/en/question/246295/how-can-i-imp... - Python Pandas import csv issue with leading zeros: https://stackoverflow.com/questions/13250046/how-to-keep-leading-zeros-in-a-column-when-reading-csv-with-pandas https://stackoverflow.com/questions/13250046/how-to-keep-lea... - R software import csv issue with leading zeros: https://stackoverflow.com/questions/31411119/r-reading-in-csv-file-removes-leading-zeros https://stackoverflow.com/questions/31411119/r-reading-in-cs... - Tableau: https://community.tableau.com/s/question/0D54T00000C5i2iSAB/how-can-i-import-numbers-with-leading-zeros https://community.tableau.com/s/question/0D54T00000C5i2iSAB/... Everybody complains about the misinterpretation of leading zeros and blame Microsoft Excel but for some reason, the programmers of the other non-Excel software have deliberately coded the same default behavior.

masklinn 6y ago
> for some reason The reason is Excel.
- jasode 6y ago
  >The reason is Excel. I've thought about that and it seems plausible but I'm not fully convinced. As another example, Google Sheets does not blindly copy Excel behavior of converting scientific gene names into a date. Excerpt from paper[1]: > To date, there is no way to permanently deactivate automatic conversion to dates in MS Excel and other spreadsheet software such as LibreOffice Calc or Apache OpenOffice Calc. We note, however, that the spreadsheet program Google Sheets did not convert any gene names to dates or numbers when typed or pasted; notably, when these sheets were later reopened with Excel, LibreOffice Calc or OpenOffice Calc, gene symbols such as SEPT1 and MARCH1 were protected from date conversion. There are several Excel behaviors that alternative software don't copy. But for some reason, the leading zero removal in csv import has stuck as a default even though it has repeated complaints and generates very similar Q&A in other software packages. (Why copy a behavior that makes you have to answer identical frustrated questions from users wanting to preserve leading zeros?!? Do programmers of LibreOffice/Google/Pandas all like inflicting pain on their users?!?) Maybe another reason is that other industries or workflows depend on leading zero removal but we never hear from them praising it. Therefore, we're distorted by a biased subset that only complains loudly about it. [1] https://genomebiology.biomedcentral.com/articles/10.1186/s13059-016-1044-7 https://genomebiology.biomedcentral.com/articles/10.1186/s13...
soul_grafitti 6y ago
So, if all your friends jump off a cliff are you going to follow them?

by jasode 6y ago

masklinn 6y ago

> for some reason The reason is Excel.

jasode 6y ago
>The reason is Excel. I've thought about that and it seems plausible but I'm not fully convinced. As another example, Google Sheets does not blindly copy Excel behavior of converting scientific gene names into a date. Excerpt from paper[1]: > To date, there is no way to permanently deactivate automatic conversion to dates in MS Excel and other spreadsheet software such as LibreOffice Calc or Apache OpenOffice Calc. We note, however, that the spreadsheet program Google Sheets did not convert any gene names to dates or numbers when typed or pasted; notably, when these sheets were later reopened with Excel, LibreOffice Calc or OpenOffice Calc, gene symbols such as SEPT1 and MARCH1 were protected from date conversion. There are several Excel behaviors that alternative software don't copy. But for some reason, the leading zero removal in csv import has stuck as a default even though it has repeated complaints and generates very similar Q&A in other software packages. (Why copy a behavior that makes you have to answer identical frustrated questions from users wanting to preserve leading zeros?!? Do programmers of LibreOffice/Google/Pandas all like inflicting pain on their users?!?) Maybe another reason is that other industries or workflows depend on leading zero removal but we never hear from them praising it. Therefore, we're distorted by a biased subset that only complains loudly about it. [1] https://genomebiology.biomedcentral.com/articles/10.1186/s13059-016-1044-7 https://genomebiology.biomedcentral.com/articles/10.1186/s13...

soul_grafitti 6y ago

So, if all your friends jump off a cliff are you going to follow them?