6 ms·

I have worked several times on serveral projects with excel files containing barcode number like 0754... And someone in the process open the file, and hop exc

I have worked several times on serveral projects with excel files containing barcode number like 0754... And someone in the process open the file, and hop excel remove all the zero at he beginning fo the number I've lost a lot of time from this...

rzzzt 3y ago
Add a ' prefix to them so they are treated as text. We've had problems with many-digit integers being converted to floating point approximations instead.
- master-lincoln 3y ago
  How did people keep up with that? Is there really no competitor who gets this right? These unexpected conversions must have cost billions in problems they created or fixing time they needed. And now after so many years they just remove some types of auto conversion and leave the others? Sounds like some kind of Stockholm syndrome...
  - HPsquared 3y ago
    Not just Excel, this affects a LOT of dynamically typed programming environments. JavaScript is rife with this sort of thing.
    - master-lincoln 3y ago
      I don't think that's a fair comparison. Here we talk about a tool that fucks up your data once you load it. With javascript there is no such thing. There are some auto type conversions when passing a wrong type into a function that can yield unexpected results. But I would argue this is different as it's an environment for power users only, and there is types at play that should make you as a programmer aware. E.g. 1 + "hello" = "1hello" 1 - "hello" = NaN
      - rzzzt 3y ago
        JSON parsing treats non-quoted numerals as a Number. That alone can result in some truncation.
        taeric 3y ago
        Though, in JSON, anything that isn't a number should be quoted?
        justsomehnguy 3y ago
        If the JSON writer is writing according to spec
        beart 3y ago
        I don't understand how this is at all a similar issue. If you want a string in JSON, then you must quote it. https://www.json.org/json-en.html https://www.json.org/json-en.html
        jodrellblank 3y ago
        And if you want a string in Excel you must apostrophe-prefix it. If you think “serial number” == number, telephone number == number, then try to use the number handling, you get problems because “numbers” don’t have leading zeros or spaces or parens or hashes or plus symbols.
        rzzzt 3y ago
        I would like to store, then retrieve exactly 1234567890123456789 apples instead of 1.070816993713379 × 2⁶⁰ = 1234567939550609408. Unfortunately due to IEEE-754 conversions I can only do this up to 7-8 digits or so. This is a number, just not one that can be represented as a Number without loss of precision towards the end.
        master-lincoln 3y ago
        that's more apples than we can fit on the planet. For such special tasks I would think it's ok to need special tools
    - hoosieree 3y ago
      Dynamic typing is orthogonal to implicit type conversion, though admittedly dynamic environments do it more often. Watch out for (void*) in C or accidentally inferred union types in TypeScript.
  - xyzelement 3y ago
    // Is there really no competitor who gets this right? Clearly excel has offered value far in excess of this annoyance. The competitors such that they existed aside from google sheets clearly fail to deliver sufficient value even in hypothetical absence of this bug.
    - master-lincoln 3y ago
      You say "clearly" twice, but it's not clear at all to me that this is the case. Can you elaborate what makes it clear that no other competitor exists who can provide similar value? And isn't LibreOffice Calc closer to Excel than Google Sheets in terms of features?
      - mjhay 3y ago
        All spreadsheets are bad. There are no good spreadsheets.
      - A4ET8a8uTh0 3y ago
        I use libre at home and excel at work. Having worked in various departments of a somewhat regulated industry, I can say that the value of Excel comes from familiarity and 'not having to retrain everyone'. It seems silly to people like me, but I personally experienced a person unable to complete their daily routine, because version change moved from one menu to another. I guess my point it is: it is not features. It is humans. MS cracked the code on that one. Get people familiar with their stuff and the rest will follow.
        eviks 3y ago
        > person unable to complete their daily routine, because version change moved from one menu to another. > I guess my point it is: it is not features. Discoverability of commands, customization, as well as the stability of UI are all features. As would be "full UI compatibility with Excel sans stupid bugs like data conversion"
      - xyzelement 3y ago
        I am a simple man and I use “people use it” as a proxy for value. LibreOffice is available everywhere excel runs, for free, and yet …
- Maxion 3y ago
  Once you’ve done this read in the data into Python or JavaScript and upload it to a backend. Fun.
- mulmen 3y ago
  Adding a prefix breaks any other process that might read that data.
- Gelob 3y ago
  That's stupid and when I export to csv it has a '. Excel should just stop fucking with shit
Tagbert 3y ago
Set the column/cell format to “text” and it will retain what you typed.
- Gelob 3y ago
  That doesn't work

rzzzt 3y ago

Add a ' prefix to them so they are treated as text. We've had problems with many-digit integers being converted to floating point approximations instead.

master-lincoln 3y ago
How did people keep up with that? Is there really no competitor who gets this right? These unexpected conversions must have cost billions in problems they created or fixing time they needed. And now after so many years they just remove some types of auto conversion and leave the others? Sounds like some kind of Stockholm syndrome...
- HPsquared 3y ago
  Not just Excel, this affects a LOT of dynamically typed programming environments. JavaScript is rife with this sort of thing.
  - master-lincoln 3y ago
    I don't think that's a fair comparison. Here we talk about a tool that fucks up your data once you load it. With javascript there is no such thing. There are some auto type conversions when passing a wrong type into a function that can yield unexpected results. But I would argue this is different as it's an environment for power users only, and there is types at play that should make you as a programmer aware. E.g. 1 + "hello" = "1hello" 1 - "hello" = NaN
    - rzzzt 3y ago
      JSON parsing treats non-quoted numerals as a Number. That alone can result in some truncation.
      - taeric 3y ago
        Though, in JSON, anything that isn't a number should be quoted?
        justsomehnguy 3y ago
        If the JSON writer is writing according to spec
      - beart 3y ago
        I don't understand how this is at all a similar issue. If you want a string in JSON, then you must quote it. https://www.json.org/json-en.html https://www.json.org/json-en.html
        jodrellblank 3y ago
        And if you want a string in Excel you must apostrophe-prefix it. If you think “serial number” == number, telephone number == number, then try to use the number handling, you get problems because “numbers” don’t have leading zeros or spaces or parens or hashes or plus symbols.
        rzzzt 3y ago
        I would like to store, then retrieve exactly 1234567890123456789 apples instead of 1.070816993713379 × 2⁶⁰ = 1234567939550609408. Unfortunately due to IEEE-754 conversions I can only do this up to 7-8 digits or so. This is a number, just not one that can be represented as a Number without loss of precision towards the end.
        master-lincoln 3y ago
        that's more apples than we can fit on the planet. For such special tasks I would think it's ok to need special tools
  - hoosieree 3y ago
    Dynamic typing is orthogonal to implicit type conversion, though admittedly dynamic environments do it more often. Watch out for (void*) in C or accidentally inferred union types in TypeScript.
- xyzelement 3y ago
  // Is there really no competitor who gets this right? Clearly excel has offered value far in excess of this annoyance. The competitors such that they existed aside from google sheets clearly fail to deliver sufficient value even in hypothetical absence of this bug.
  - master-lincoln 3y ago
    You say "clearly" twice, but it's not clear at all to me that this is the case. Can you elaborate what makes it clear that no other competitor exists who can provide similar value? And isn't LibreOffice Calc closer to Excel than Google Sheets in terms of features?
    - mjhay 3y ago
      All spreadsheets are bad. There are no good spreadsheets.
    - A4ET8a8uTh0 3y ago
      I use libre at home and excel at work. Having worked in various departments of a somewhat regulated industry, I can say that the value of Excel comes from familiarity and 'not having to retrain everyone'. It seems silly to people like me, but I personally experienced a person unable to complete their daily routine, because version change moved from one menu to another. I guess my point it is: it is not features. It is humans. MS cracked the code on that one. Get people familiar with their stuff and the rest will follow.
      - eviks 3y ago
        > person unable to complete their daily routine, because version change moved from one menu to another. > I guess my point it is: it is not features. Discoverability of commands, customization, as well as the stability of UI are all features. As would be "full UI compatibility with Excel sans stupid bugs like data conversion"
    - xyzelement 3y ago
      I am a simple man and I use “people use it” as a proxy for value. LibreOffice is available everywhere excel runs, for free, and yet …
Maxion 3y ago
Once you’ve done this read in the data into Python or JavaScript and upload it to a backend. Fun.
mulmen 3y ago
Adding a prefix breaks any other process that might read that data.
Gelob 3y ago
That's stupid and when I export to csv it has a '. Excel should just stop fucking with shit

Tagbert 3y ago

Set the column/cell format to “text” and it will retain what you typed.

Gelob 3y ago
That doesn't work