Comment by jasode | Hacker News Reader

Comment by jasode | Hacker News Reader

Parent Full thread

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

- LibreOffice: 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-lea...

- R software import csv issue with leading zeros: https://stackoverflow.com/questions/31411119/r-reading-in-cs...

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

> for some reason

The reason is Excel.

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

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

Reply on news.ycombinator.com