News for hackers. Stuff that matters.
Re:Scientists rename human genes to stop MS Excel from misre... by dr_orpheus on Thursday August 06, 2020 @10:13AM (#24071037)
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.

Re:Scientists rename human genes to stop MS Excel from misre... by alach11 on Thursday August 06, 2020 @11:09AM (#24071593)
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.
Re:Scientists rename human genes to stop MS Excel from misre... by arethuza on Thursday August 06, 2020 @10:40AM (#24071309)
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.
Re:Scientists rename human genes to stop MS Excel from misre... by ekianjo on Thursday August 06, 2020 @10:36AM (#24071265)
Excel can't even properly support UTF-8 csv documents. But Libreoffice Calc does. Pure madness.
Re:Scientists rename human genes to stop MS Excel from misre... by yardstick on Thursday August 06, 2020 @10:43AM (#24071342)
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?
Re:Scientists rename human genes to stop MS Excel from misre... by Cactus2018 on Thursday August 06, 2020 @11:16AM (#24071671)
Why can't there be a flag (defaulted to off) to control auto formatting
Re:Scientists rename human genes to stop MS Excel from misre... by pbhjpbhj on Thursday August 06, 2020 @12:34PM (#24072580)
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.

Re:Scientists rename human genes to stop MS Excel from misre... by yardstick on Thursday August 06, 2020 @03:20PM (#24074466)
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.

Re:Scientists rename human genes to stop MS Excel from misre... by dragonwriter on Thursday August 06, 2020 @03:33PM (#24074572)
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.

Re:Scientists rename human genes to stop MS Excel from misre... by freeone3000 on Thursday August 06, 2020 @12:55PM (#24072834)
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.
Re:Scientists rename human genes to stop MS Excel from misre... by FabHK on Thursday August 06, 2020 @11:24AM (#24071760)
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.
Re:Scientists rename human genes to stop MS Excel from misre... by mcv on Thursday August 06, 2020 @11:31AM (#24071825)
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.
Re:Scientists rename human genes to stop MS Excel from misre... by yardstick on Thursday August 06, 2020 @03:17PM (#24074445)
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.

Re:Scientists rename human genes to stop MS Excel from misre... by dr_orpheus on Thursday August 06, 2020 @04:10PM (#24074940)
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.

Re:Scientists rename human genes to stop MS Excel from misre... by jpindar on Thursday August 06, 2020 @11:47AM (#24072005)
Same here, but with part numbers in electronics manufacturing. Excel also removes leading zeros from package names such as 0402.
Re:Scientists rename human genes to stop MS Excel from misre... by arethuza on Thursday August 06, 2020 @11:52AM (#24072082)
That also applies to phone numbers :-(
Re:Scientists rename human genes to stop MS Excel from misre... by jpindar on Thursday August 06, 2020 @12:16PM (#24072359)
And zip codes.
Re:Scientists rename human genes to stop MS Excel from misre... by david422 on Thursday August 06, 2020 @11:44AM (#24071965)
Yes, and then compound this problem by transferring files with people that.... are somewhat technically illiterate and it turns into a nightmare.
Re:Scientists rename human genes to stop MS Excel from misre... by BbzzbB on Friday August 07, 2020 @12:02AM (#24078114)
You are bringing me back nightmares, how I wish I was Python-litterate during my previous employment.