Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Human genes renamed to stop Microsoft Excel from misreading them as dates (2020)

theverge.com

Post reply on HN
Print view View original

11–20 of 143 posts

Re: Human genes renamed to stop Microsoft Excel from misreading them as dates (2020)

#11

Post by rini17 »

imtringued wrote: post #8

>Even then, a scientist might fix their data but export it as a CSV file without saving the formatting. Is this supposed to be a joke? Since when does excel support CSV files? Yes you can import and export CSV files but that is just there to check a box. That feature doesn't actually work. Just import and export .xlsx files in your applications directly. CSV is such a bad format because it's not even a standard, ther…

Opening CSVs in Excel almost never worked for me. Good thing there's LibreOffice Calc with spiffy import dialog with all the csv options/variants where you can preview the result.

Re: Human genes renamed to stop Microsoft Excel from misreading them as dates (2020)

#12

Post by hobofan »

imtringued wrote: post #9
mdb31 wrote: post #4

PSA: if you use Excel as intended, you would just go File/New/Blank Worksheet, switch to the Data tab, then use the 'From Text/CSV' wizard to specify the data types for each column in your data source, neatly bypassing this entire issue. But yes, just double-clicking the CSV from Explorer, using the legacy 'open this as a sheet' functionality, experiencing data loss and then complaining about it (and the state of Exc…

>then use the 'From Text/CSV' wizard to specify the data types for each column in your data source, neatly bypassing this entire issue. Something people do not understand is that type information in CSV files is conveyed out of band. The application that is reading the CSV file must know the datatype which effectively turns each CSV file into an application specific format.

> which effectively turns each CSV file into an application specific format

Thanks for writing that! I don't think I've seen the reason for my (partial) dislike of CSV put into words that clearly before.

Re: Human genes renamed to stop Microsoft Excel from misreading them as dates (2020)

#13

Post by jasode »

imtringued wrote: post #8

>Even then, a scientist might fix their data but export it as a CSV file without saving the formatting. Is this supposed to be a joke? Since when does excel support CSV files? Yes you can import and export CSV files but that is just there to check a box. That feature doesn't actually work. Just import and export .xlsx files in your applications directly. CSV is such a bad format because it's not even a standard, ther…

>Since when does excel support CSV files? Yes you can import and export CSV files but that is just there to check a box. That feature doesn't actually work. Just import and export .xlsx files in your applications directly.

You're being hyperbolic and your .xlsx advice doesn't apply to other systems we don't control that only offer .csv files.

Examples... my credit card website and Ebay only offer csv downloads of data. I use MS Excel to import those csv files all the time and it works well enough.

Yes, I'm aware of potential data-conversion flaws with importing csv files. (My previous comment: https://news.ycombinator.com/item?id=25017116 )

All those caveats with csv are irrelevant when the system that has the data people want only offers csv. Using Excel's feature to import csv -- and being aware of the format dangers -- is more practical than manually retyping all the data from scratch.

Re: Human genes renamed to stop Microsoft Excel from misreading them as dates (2020)

#14

Post by joenathanone »

rini17 wrote: post #11
imtringued wrote: post #8

>Even then, a scientist might fix their data but export it as a CSV file without saving the formatting. Is this supposed to be a joke? Since when does excel support CSV files? Yes you can import and export CSV files but that is just there to check a box. That feature doesn't actually work. Just import and export .xlsx files in your applications directly. CSV is such a bad format because it's not even a standard, ther…

Opening CSVs in Excel almost never worked for me. Good thing there's LibreOffice Calc with spiffy import dialog with all the csv options/variants where you can preview the result.

Excel has exactly the same functionality.

Re: Human genes renamed to stop Microsoft Excel from misreading them as dates (2020)

#15

Post by hoseja »

mdb31 wrote: post #4

PSA: if you use Excel as intended, you would just go File/New/Blank Worksheet, switch to the Data tab, then use the 'From Text/CSV' wizard to specify the data types for each column in your data source, neatly bypassing this entire issue. But yes, just double-clicking the CSV from Explorer, using the legacy 'open this as a sheet' functionality, experiencing data loss and then complaining about it (and the state of Exc…

The easy action should be safe by default.

Re: Human genes renamed to stop Microsoft Excel from misreading them as dates (2020)

#16

Post by nulbyte »

> Microsoft Excel may be fleeting, but human genes will be around for as long as we are. It’s best to give them names that work.

This is the crux of the issue. Yes, Excel could do better with support for CSV. (OpenOffice and LibreOffice habdle this better, for example.) But no, genetics is not some niche use-case for this better support. It's just one example of many. I do analytics of another sort for a bank, and we run into this problem all the time with those pesky 16-digit account numbers.

But, while Microsoft might do well to make changes to address their poor implementation, someone somewhere will implement the next best thing and screw it up, too.

Re: Human genes renamed to stop Microsoft Excel from misreading them as dates (2020)

#17

Post by rini17 »

joenathanone wrote: post #14
rini17 wrote: post #11

Earlier quoted context omitted.

Opening CSVs in Excel almost never worked for me. Good thing there's LibreOffice Calc with spiffy import dialog with all the csv options/variants where you can preview the result.

Excel has exactly the same functionality.

Good, since when? Last used it 10ish years ago.

Re: Human genes renamed to stop Microsoft Excel from misreading them as dates (2020)

#18

Post by nicoburns »

nulbyte wrote: post #16

> Microsoft Excel may be fleeting, but human genes will be around for as long as we are. It’s best to give them names that work. This is the crux of the issue. Yes, Excel could do better with support for CSV. (OpenOffice and LibreOffice habdle this better, for example.) But no, genetics is not some niche use-case for this better support. It's just one example of many. I do analytics of another sort for a bank, and we…

I really wish there was a better interchange format for tabular data. Criteria:

- Plain text file

- Supports formatting, cell types, etc

- Does not support full spreadsheet features like formulas

Re: Human genes renamed to stop Microsoft Excel from misreading them as dates (2020)

#19

Post by joenathanone »

rini17 wrote: post #17
joenathanone wrote: post #14

Earlier quoted context omitted.

Excel has exactly the same functionality.

Good, since when? Last used it 10ish years ago.

Here is the feature in Excel 2007 https://youtu.be/ML0VDMpWllM around 2:45, could have also been there in earlier versions too.

Re: Human genes renamed to stop Microsoft Excel from misreading them as dates (2020)

#20

Post by mcv »

The real problem is of course using Excel to maintain data. That's not what it's for. That's what databases are for. Excel is for complex calculations over rows and columns. And of course it should be better at data validation; you should be able to tell it whether a certain column is a date or not, and Excel shouldn't be jumping to stupid conclusions about it, but it does, and that means you need to be careful and not use Excel for something like that.

Excel misuse is sadly rampant and one of my main frustrations at my previous project. Excel is popular for this sort of misuse because when you open it, it presents you with an empty table and you can immediately start typing. This invites tabular data. But Excel inevitably fucks up because it lacks proper data types, data validation, or foreign keys, and loves making assumptions about what you mean. This makes it a terrible and harmful choice for any sort of serious data. I even proposed a new project for an Excel-like frontend backed by a database exactly for these sort of situations. Because I do get what attracts people to Excel for this sort of thing. And most people don't realise what a terrible choice it is.

Post reply on HN