4 ms·

Every single problem I've ever had with excel stems from excel trying to be too clever. Nearly all my problems with importing data into it could be solved if ex

Every single problem I've ever had with excel stems from excel trying to be too clever. Nearly all my problems with importing data into it could be solved if excel left everything as is until I asked it to format values a certain way.

reportgunner 6y ago
Well Excel trying to be clever stems from people not trying to be clever. We truly reap what we sow.
Ntrails 6y ago
As an intern I was once given a spreadsheet of manually entered daily data, stored by a human being over the previous couple of years. Obviously he'd entered the dates in whatever form he felt like at the time and Excel had parsed them in a variety of godawful ways not least of which was defaulting to American month/day assumptions before UK day/month. I think it would have been far quicker to just manually write a new column interpreting the dates based on previous/next etc. Instead I spent God knows how long trying to be clever, failing, and being embarrassed that I could not solve this obviously trivial problem.
jasode 6y ago
>Every single problem I've ever had with excel stems from excel trying to be too clever. Nearly all my problems with importing data into it could be solved if excel left everything as is We're biased to bash on Microsoft for being "too clever" but maybe we need a reality check by looking at the bigger picture. Examples of other software not written by Microsoft that also drops the leading zeros and users asking questions on how to preserve them: - 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... - 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... Conclusion: For some compelling reason, we have a bunch of independent programmers who all want to remove leading zeros.
- lima 6y ago
  The issue is that CSVs are untyped and software has to guess the type, sometimes unsuccessfully.
  - salamander014 6y ago
    You are right on the cause of the issue. But the software does not HAVE to guess the type. If I paste something into Excel, I want what I pasted to be what's in Excel. Changing the type and inadvertently affecting the data isn't something any data software should do by default. And the fact that OP said they can't turn it off means that this is beyond a design mistake, it is a FEATURE. If Excel took itself seriously as a data platform, it would not make a change to any text by default.
    - pif 6y ago
      > If I paste something into Excel, I want what I pasted to be what's in Excel. And that's what you have in Excel. What gets displayed is a separate issue. And no, you don't want to see exactly what you typed in, not in the general case. And no, I can't believe I am defending Excel!
    - matt-attack 6y ago
      I believe excel considered itself a finance system not a data system. Hence the clear bias towards numbers over strings.
    - zentiggr 6y ago
      But Excel has never been a data platform. It's a spreadsheet. It's user's faults for using it in ways that it was never designed for. Excel has always been about sticking numbers in boxes and calculating with them. If you want unmodified string input, input strings into a tool intended to handle them. Project specifications can be hard. Using 1) .xls files after they were superseded, 2) ANY data transfer method without considering capacity or truncation issues, speaks of incompetence.
    - rustybelt 6y ago
stjohnswarts 6y ago
And the people who make excel will just tell you "excel is not a database" lol
- rdiddly 6y ago
  Which sadly, is what I want to tell them too!

reportgunner 6y ago

Well Excel trying to be clever stems from people not trying to be clever. We truly reap what we sow.

Ntrails 6y ago

As an intern I was once given a spreadsheet of manually entered daily data, stored by a human being over the previous couple of years. Obviously he'd entered the dates in whatever form he felt like at the time and Excel had parsed them in a variety of godawful ways not least of which was defaulting to American month/day assumptions before UK day/month. I think it would have been far quicker to just manually write a new column interpreting the dates based on previous/next etc. Instead I spent God knows how long trying to be clever, failing, and being embarrassed that I could not solve this obviously trivial problem.

jasode 6y ago

>Every single problem I've ever had with excel stems from excel trying to be too clever. Nearly all my problems with importing data into it could be solved if excel left everything as is We're biased to bash on Microsoft for being "too clever" but maybe we need a reality check by looking at the bigger picture. Examples of other software not written by Microsoft that also drops the leading zeros and users asking questions on how to preserve them: - 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... - 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... Conclusion: For some compelling reason, we have a bunch of independent programmers who all want to remove leading zeros.

lima 6y ago
The issue is that CSVs are untyped and software has to guess the type, sometimes unsuccessfully.
- salamander014 6y ago
  You are right on the cause of the issue. But the software does not HAVE to guess the type. If I paste something into Excel, I want what I pasted to be what's in Excel. Changing the type and inadvertently affecting the data isn't something any data software should do by default. And the fact that OP said they can't turn it off means that this is beyond a design mistake, it is a FEATURE. If Excel took itself seriously as a data platform, it would not make a change to any text by default.
  - pif 6y ago
    > If I paste something into Excel, I want what I pasted to be what's in Excel. And that's what you have in Excel. What gets displayed is a separate issue. And no, you don't want to see exactly what you typed in, not in the general case. And no, I can't believe I am defending Excel!
  - matt-attack 6y ago
    I believe excel considered itself a finance system not a data system. Hence the clear bias towards numbers over strings.
  - zentiggr 6y ago
    But Excel has never been a data platform. It's a spreadsheet. It's user's faults for using it in ways that it was never designed for. Excel has always been about sticking numbers in boxes and calculating with them. If you want unmodified string input, input strings into a tool intended to handle them. Project specifications can be hard. Using 1) .xls files after they were superseded, 2) ANY data transfer method without considering capacity or truncation issues, speaks of incompetence.
  - rustybelt 6y ago

stjohnswarts 6y ago

And the people who make excel will just tell you "excel is not a database" lol

rdiddly 6y ago
Which sadly, is what I want to tell them too!