4 ms·

Having Excel be the default for .csv files has caused me problems in the past as well with Excel automatically changing data when you open it. The unfortunate t

Having Excel be the default for .csv files has caused me problems in the past as well with Excel automatically changing data when you open it. The unfortunate thing I have come across multiple times is a csv file that has some values with a lot of significant figures, i.e. 123456789876, 987654321234 After opening in Excel it will convert these to: 1.23E+11, 9.87E+11 Then resaving the file (as a csv) will store the values in scientific notation and lose all of the digits.

ekianjo 6y ago
Excel can't even properly support UTF-8 csv documents. But Libreoffice Calc does. Pure madness.
arethuza 6y ago
That's the problem of having Excel open CSV files by default - if you use Data//From Text/CSV you get a chance to change the import process to make those columns text rather than Excel guessing they are numbers.
yardstick 6y ago
Same here, but with IMEI numbers. Excel silently truncates them, it’s frustrating. Why can’t the auto format detect and just store them in text format?
- Cactus2018 6y ago
  Why can't there be a flag (defaulted to off) to control auto formatting
  - pbhjpbhj 6y ago
    I was thinking that perhaps the autoformat options could be explicitly shown in the column headers number date currency A B C and you could click on the format to change it. Italics could show assumed formats, bold could show user set formats. I'm newly returned to (very minor) Excel use and assumed formats with no feedback seems wrong. My situation is dates _not_ being picked up as dates and it not being obvious that it hasn't; I'm using ISO format (eg 2020-06-17), I guess you have to create a new date format or use USA-ordering before it will recognise dates.
    - freeone3000 6y ago
      This is what happens on explicit import, but, formatting is per-cell, not per-column. "General" is the format for "idk, guess" - it stays General after a transform, it's not converted.
    - yardstick 6y ago
      I would be happy if Excel supported some way to specify the column type in the CSV itself. Ie first row headers, second row data type/hint, subsequent rows the actual data. At the moment users need to remember every time they import the CSV to specify A data type for each incorrectly handled column. Suffice to say they don’t always remember... and with large CSVs with hundreds of columns it’s just a pain.
      - dragonwriter 6y ago
        What might be better is what I'll call “CSV Schema”: support for paired CSV files where one normal CSV has data, and the other is a schema file which contains headers which match the data files headers with a standard extra first header prepends (call it “meta-name”), and the data rows each provided metadata for the corresponding column in the data file. The most common (and often the only) metadata row might be for type information, but the format could support additional metadata. This would allow existing CSV tools that work with data files to continue to work without modification, while CSV Schema aware tools could make use of the schema.
- FabHK 6y ago
  Because most people want to import numbers as numbers most of the time. Excel detects numbers and treats them as numbers. You do have the option to import them as text. So, Excel (as usual) does what's the best for most people.
  - mcv 6y ago
    The reasonable default should be not to change existing data. If the user wants to, by all means do it, but not before. If you want to be smart about it, offer a popup asking if a particular column or field should be interpreted as a number or date, but by default, keep everything a string.
  - yardstick 6y ago
    Why does excel truncate a very long number silently? That is what happens with IMEI numbers. It cuts off the last 4-6 digits. It’s destroying numbers. Excel should know any number that is longer than it can internally support should be stored as text. Or they need to introduce a data type that can store arbitrary precision numbers.
    - dr_orpheus 6y ago
      It's not that Excel can't support that long of a number in general. When you open up a csv in Excel, it is not being worked on in "Excel format" where it is displaying the number in scientific notation but it is still retaining the full number. If you click on the cell in the text window in the top it shows you the full number. The asinine thing happens when you save it back as a csv. Excel doesn't think to itself "hey, I should write these full values I have stored in each cell to the csv file". It just writes whatever is currently displayed to the csv file instead.
alach11 6y ago
This issue is common in the oil and gas industry. Wells are typically identified with a 14-digit number that Excel likes to convert to scientific notation and then truncate digits when saving to CSV.
david422 6y ago
Yes, and then compound this problem by transferring files with people that.... are somewhat technically illiterate and it turns into a nightmare.
jpindar 6y ago
Same here, but with part numbers in electronics manufacturing. Excel also removes leading zeros from package names such as 0402.
- arethuza 6y ago
  That also applies to phone numbers :-(
  - jpindar 6y ago
    And zip codes.
BbzzbB 6y ago
You are bringing me back nightmares, how I wish I was Python-litterate during my previous employment.

ekianjo 6y ago

Excel can't even properly support UTF-8 csv documents. But Libreoffice Calc does. Pure madness.

arethuza 6y ago

That's the problem of having Excel open CSV files by default - if you use Data//From Text/CSV you get a chance to change the import process to make those columns text rather than Excel guessing they are numbers.

yardstick 6y ago

Same here, but with IMEI numbers. Excel silently truncates them, it’s frustrating. Why can’t the auto format detect and just store them in text format?

Cactus2018 6y ago
Why can't there be a flag (defaulted to off) to control auto formatting
- pbhjpbhj 6y ago
  I was thinking that perhaps the autoformat options could be explicitly shown in the column headers number date currency A B C and you could click on the format to change it. Italics could show assumed formats, bold could show user set formats. I'm newly returned to (very minor) Excel use and assumed formats with no feedback seems wrong. My situation is dates _not_ being picked up as dates and it not being obvious that it hasn't; I'm using ISO format (eg 2020-06-17), I guess you have to create a new date format or use USA-ordering before it will recognise dates.
  - freeone3000 6y ago
    This is what happens on explicit import, but, formatting is per-cell, not per-column. "General" is the format for "idk, guess" - it stays General after a transform, it's not converted.
  - yardstick 6y ago
    I would be happy if Excel supported some way to specify the column type in the CSV itself. Ie first row headers, second row data type/hint, subsequent rows the actual data. At the moment users need to remember every time they import the CSV to specify A data type for each incorrectly handled column. Suffice to say they don’t always remember... and with large CSVs with hundreds of columns it’s just a pain.
    - dragonwriter 6y ago
      What might be better is what I'll call “CSV Schema”: support for paired CSV files where one normal CSV has data, and the other is a schema file which contains headers which match the data files headers with a standard extra first header prepends (call it “meta-name”), and the data rows each provided metadata for the corresponding column in the data file. The most common (and often the only) metadata row might be for type information, but the format could support additional metadata. This would allow existing CSV tools that work with data files to continue to work without modification, while CSV Schema aware tools could make use of the schema.
FabHK 6y ago
Because most people want to import numbers as numbers most of the time. Excel detects numbers and treats them as numbers. You do have the option to import them as text. So, Excel (as usual) does what's the best for most people.
- mcv 6y ago
  The reasonable default should be not to change existing data. If the user wants to, by all means do it, but not before. If you want to be smart about it, offer a popup asking if a particular column or field should be interpreted as a number or date, but by default, keep everything a string.
- yardstick 6y ago
  Why does excel truncate a very long number silently? That is what happens with IMEI numbers. It cuts off the last 4-6 digits. It’s destroying numbers. Excel should know any number that is longer than it can internally support should be stored as text. Or they need to introduce a data type that can store arbitrary precision numbers.
  - dr_orpheus 6y ago
    It's not that Excel can't support that long of a number in general. When you open up a csv in Excel, it is not being worked on in "Excel format" where it is displaying the number in scientific notation but it is still retaining the full number. If you click on the cell in the text window in the top it shows you the full number. The asinine thing happens when you save it back as a csv. Excel doesn't think to itself "hey, I should write these full values I have stored in each cell to the csv file". It just writes whatever is currently displayed to the csv file instead.

alach11 6y ago

This issue is common in the oil and gas industry. Wells are typically identified with a 14-digit number that Excel likes to convert to scientific notation and then truncate digits when saving to CSV.

david422 6y ago

Yes, and then compound this problem by transferring files with people that.... are somewhat technically illiterate and it turns into a nightmare.

jpindar 6y ago

Same here, but with part numbers in electronics manufacturing. Excel also removes leading zeros from package names such as 0402.

arethuza 6y ago
That also applies to phone numbers :-(
- jpindar 6y ago
  And zip codes.

BbzzbB 6y ago

You are bringing me back nightmares, how I wish I was Python-litterate during my previous employment.