4 ms·

Do you _really_ think I've spent the past 10 years getting files like this and I still don't understand the reason. I didn't ask for an explanation. I asked for

Do you _really_ think I've spent the past 10 years getting files like this and I still don't understand the reason. I didn't ask for an explanation. I asked for a fix. What are you doing here?? I can provide instructions on how to do this until the cows come home. I will still get files with these issues. Is that not obvious? Do you really think I want to examine _tens of thousands of rows_ in documents that are 100 cells wide looking for a single exponent value!?!?!?! Is that really what you're suggesting as a fix here?

DandyDev 3y ago
My my, why so angry? You're expecting something that Excel doesn't support for good reason. The string you used as an example might be a date string to YOU, but to Excel and many of its users it's just a large integer. Lots of regular use cases would break if Excel would interpret that string as a datwle If you want Excel to interpret strings as dates, use a more common date representation such as iso8601
- mulmen 3y ago
  It’s one of the most infuriating and dangerous behaviors I have ever encountered. Excel silently truncates data. The anger is warranted. The problem isn’t assuming a display format for data, it is that a poor assumption actually mangles data. Excel doesn’t fail safe. Numbers in Excel only maintain 15 digits of precision. Leading zeros are also truncated. If you haven’t experienced this you are lucky (or just didn’t notice) but it is real. > If you want Excel to interpret strings as dates, use a more common date representation such as iso8601 You don’t always control the upstream system. Your response can be considered rude because it implies you think you know more than the person expressing pain. This is especially insulting with such a widely known problem in Excel. This behavior burned me with billing system IDs. Logically they are strings, we would never do math on them. For performance they were implemented as BIGINTs in the DB. Excel truncates everything after the 15th digit. Our IDs were all over 20. So accounting couldn’t reconcile our reports with the billing system.
  - dragonwriter 3y ago
    > It’s one of the most infuriating and dangerous behaviors I have ever encountered. Excel silently truncates data. That's a problem, sure, but not the one sixothree is complaining about. > The problem isn’t assuming a display format for data Your problem might not be, but that’s exactly the problem sixothree is complaining about that the post you are responding to addresses, so while your post references a valid complaint, it is a complete non-sequitur as a response.
    - deleted 3y ago
      [deleted]
    - deleted 3y ago
      [deleted]
    - deleted 3y ago
      [deleted]
- CyberDildonics 3y ago
  My my, why so angry? Because they have been dealing with the same bug for 10 years, I thought that was obvious.
dragonwriter 3y ago
> Do you _really_ think I've spent the past 10 years getting files like this and I still don't understand the reason. I didn't ask for an explanation. I asked for a fix. I don’t believe you’ve been doing it that long and haven't found the fix. Apply appropriate formatting if you have long strings of numeric digits [as long as they can’t have leading zeroes] that you want stored as numbers but displayed without scientific notation. (Not the best choice for what is semantically a date, in your case, but...that’s a whole bigger issue and, well, babysteps.) You don't have to look at anything, just do it for the ranges where that is the kind of data: https://help.godatafeed.com/hc/en-us/articles/360049916591-How-to-prevent-Excel-from-converting-your-UPCs-or-GTINs-into-scientific-notation https://help.godatafeed.com/hc/en-us/articles/360049916591-H...
- deleted 3y ago
  [deleted]
- mulmen 3y ago
  The text import wizard does work. The problem with it is that everyone has to use it and if anyone doesn’t then your data is silently mangled. In the past I have added an apostrophe to the front of any integer that should be displayed as a string. Excel will not display that leading single apostrophe. It of course taints that data forever but I consider “Exported to Excel” a terminal state anyway.
- Alupis 3y ago
  I cannot remember the last time I had some long number and thought to myself, "You know what will be helpful, convert this number into scientific notation!"... It's asinine Excel has this behavior with any large number. Try to work with a list of EAN/UPC codes... it'll wreck every single one every single time, unless you take significant care to guard against it via formatting, special characters that trick it into using a string, etc. Try working with a spreadsheet full of database id's... same thing. It's asinine. By the very nature of Excel, large numbers belong as-is... ie. you're studying something and need the values. Approximations (what scientific notation gives you in the best case) are an exception to what people need nearly every time.

DandyDev 3y ago

My my, why so angry? You're expecting something that Excel doesn't support for good reason. The string you used as an example might be a date string to YOU, but to Excel and many of its users it's just a large integer. Lots of regular use cases would break if Excel would interpret that string as a datwle If you want Excel to interpret strings as dates, use a more common date representation such as iso8601

mulmen 3y ago
It’s one of the most infuriating and dangerous behaviors I have ever encountered. Excel silently truncates data. The anger is warranted. The problem isn’t assuming a display format for data, it is that a poor assumption actually mangles data. Excel doesn’t fail safe. Numbers in Excel only maintain 15 digits of precision. Leading zeros are also truncated. If you haven’t experienced this you are lucky (or just didn’t notice) but it is real. > If you want Excel to interpret strings as dates, use a more common date representation such as iso8601 You don’t always control the upstream system. Your response can be considered rude because it implies you think you know more than the person expressing pain. This is especially insulting with such a widely known problem in Excel. This behavior burned me with billing system IDs. Logically they are strings, we would never do math on them. For performance they were implemented as BIGINTs in the DB. Excel truncates everything after the 15th digit. Our IDs were all over 20. So accounting couldn’t reconcile our reports with the billing system.
- dragonwriter 3y ago
  > It’s one of the most infuriating and dangerous behaviors I have ever encountered. Excel silently truncates data. That's a problem, sure, but not the one sixothree is complaining about. > The problem isn’t assuming a display format for data Your problem might not be, but that’s exactly the problem sixothree is complaining about that the post you are responding to addresses, so while your post references a valid complaint, it is a complete non-sequitur as a response.
  - deleted 3y ago
    [deleted]
  - deleted 3y ago
    [deleted]
  - deleted 3y ago
    [deleted]
CyberDildonics 3y ago
My my, why so angry? Because they have been dealing with the same bug for 10 years, I thought that was obvious.

dragonwriter 3y ago

> Do you _really_ think I've spent the past 10 years getting files like this and I still don't understand the reason. I didn't ask for an explanation. I asked for a fix. I don’t believe you’ve been doing it that long and haven't found the fix. Apply appropriate formatting if you have long strings of numeric digits [as long as they can’t have leading zeroes] that you want stored as numbers but displayed without scientific notation. (Not the best choice for what is semantically a date, in your case, but...that’s a whole bigger issue and, well, babysteps.) You don't have to look at anything, just do it for the ranges where that is the kind of data: https://help.godatafeed.com/hc/en-us/articles/360049916591-How-to-prevent-Excel-from-converting-your-UPCs-or-GTINs-into-scientific-notation https://help.godatafeed.com/hc/en-us/articles/360049916591-H...

deleted 3y ago
[deleted]
mulmen 3y ago
The text import wizard does work. The problem with it is that everyone has to use it and if anyone doesn’t then your data is silently mangled. In the past I have added an apostrophe to the front of any integer that should be displayed as a string. Excel will not display that leading single apostrophe. It of course taints that data forever but I consider “Exported to Excel” a terminal state anyway.
Alupis 3y ago
I cannot remember the last time I had some long number and thought to myself, "You know what will be helpful, convert this number into scientific notation!"... It's asinine Excel has this behavior with any large number. Try to work with a list of EAN/UPC codes... it'll wreck every single one every single time, unless you take significant care to guard against it via formatting, special characters that trick it into using a string, etc. Try working with a spreadsheet full of database id's... same thing. It's asinine. By the very nature of Excel, large numbers belong as-is... ie. you're studying something and need the values. Approximations (what scientific notation gives you in the best case) are an exception to what people need nearly every time.