7 ms·

Or they could set the format of the column to text instead of keeping it as general and everything would just work. If these "scientists" are having this probl

by dmz73 3y ago

Or they could set the format of the column to text instead of keeping it as general and everything would just work. If these "scientists" are having this problem then they haven't spent any time at all learning how to use the tool they rely on. Can we trust anything these people produce if this is the level of competence they have with Excel?

JimmyAustin 3y ago
A lot of analysis is done using CSVs being pushed in and out of Excel. Doing so strips the formatting. Please understand the workflows being crying “skill issue”.
- knighthack 3y ago
  Sorry, can't understand, and that excuse is unacceptable. It's totally a skill issue, and a failure of it, and a laziness of these 'scientists'. You'd expect scientists - people working to understand the nature of reality - to have some base competency about how they measure reality. Could have at least used a database for things like this; moreover any decent database can often import from CSV and export to CSV as well. Excel is not at fault here; the 'scientists' are.
  - eigenket 3y ago
    Haha, you think scientists have control over what software their employer buys for them to work on. There are probably wonderful places where that is the case, and probably several of those places use something like libreoffice which doesn't do the idiotic data conversions excel does, but they are definitely not the norm.
  - cqqxo4zV46cp 3y ago
    Coming up next: people should still use C because writing insecure code is a “skill issue”. Why can’t we as a society make ANYTHING easier without the usual blathering on from the peanut gallery turning it into a question of one’s intelligence?
    - deleted 3y ago
      [deleted]
- bayindirh 3y ago
  Yes, but you can create a small macro to the change the formatting and assign that to your table. Your CSV should import deterministically. That's not impossible to do. I saw a complete analysis engine written as an Excel file, which accepts and exports CSVs cleanly. It can be done.
  - n4r9 3y ago
    "It can be done" is a far cry from "it's reasonable to expect scientists to do this".
    - bayindirh 3y ago
      As a person who does research and support researchers, I can't see the gap, sorry. I understand some people don't know it's possible, and some don't care, but for any competent researcher, it's expected them to master the tools they use. This is esp. true for career researchers.
      - djtango 3y ago
        I agree. As a researcher you may have to learn how to carefully dig up skulls, raise rats, handle lasers, remember not to accidentally syringe yourself with viruses etc. Getting cut by Excel seems like part of the job and at least is hopefully less life threatening than possibly blowing yourself up or giving yourself silicosis. That said the problem with computers is that they're pervasive, they're a moving target and often it's a case of the blind leading the blind when it comes to research. And probably more and more research groups need dedicated computer technician resources who can centralize the required computer knowledge of keeping a research group running.
      - n4r9 3y ago
        I'm not disputing that a competent computer user can do it. From the perspective of "this is what I would do if I was a scientist", you're totally correct. But when you're writing guidelines for an entire field - as the article describes HGNC doing - you're catering to all researchers in that field: good, bad, and ugly. Plus technicians, editors, admins and anyone else that might handle the files. Given how hidden and unintuitive Excel's behaviour is here, I think what they're doing makes sense.
- mike_hearn 3y ago
  You can write CSVs in such a way that forces Excel to infer specific data types: "=""Data Here""" will always be treated as a string. This is also supported by Sheets, apparently.
  - mcintyre1994 3y ago
    TIL, neat! Does Excel automatically export like that if you format the columns as strings?
  - _visgean 3y ago
    ok but will that work other tools that work with those csvs? I imagine that they export / import from excel to csv for a reason.
bayindirh 3y ago
That's too much work. Nobody have time for doing or learning that. Sounds like a joke, but it isn't. I know scientists who think exactly like that.
- rrr_oh_man 3y ago
  It’s probably the same scientists who answer an email 2 years later
cqqxo4zV46cp 3y ago
It’s utterly unreasonable of you to expect that your drive-by HN comment solves this problem for an entire community of people. This really just comes across as unproductive snark.
- chrisjj 3y ago
  > It’s utterly unreasonable of you to expect that your drive-by HN comment solves this problem for an entire community of people. I'd say more unreasonable is for you to mischaracterise this comment as you have done.
barryrandall 3y ago
C/C++ developers could just use the tooling available to them to mitigate memory management issues, but they don't. Why should we trust anything programmers produce if this is the level of competence they have with their chosen tools?
beAbU 3y ago
This is a very unfair and harsh comment. In my current work, we deal with our user's national identity numbers quite frequently. This number is a 13 digit numerical number, that starts with your date of birth. So someone born on March 13 1989 will have a number start with 890913. People born in the aughts have "00" "01" "02" etc at the start of their ID number. We need to frequently generate excel and csv reports that contain these numbers, and we need to ingest CSVs from other vendors that contain these numbers. The /moment/ excel touches a CSV with these numbers in, it'll assume that column is a number, it'll strip out the preceding zeros, and it'll format the number in scientific notation. If you change the column's data type to text afterwards, then it's too late - the damage has been done and you've worst case lost data, best case you have a text column full of scientific notation numbers. You can't just open up the CSV, you need to import it, and very explicitly tell Excel how to handle this column, otherwise you mess things up. Now, anywhere in the chain of people and other vendors sending and receiving these files, anyone who double clicks on that file and it opens up in excel and does not notice this very destructive action messes up our processes and causes unknown amounts of delays. It's the bane of my existence. This exact problem also crops up with phone numbers, where in many countries the number starts with a 0, or if it's an international number, a "+". Excel thinks the "+" makes the field a formula. All of this because Excel is making assumptions and trying to "help", in the same way a 4 year old helps in the kitchen. For this reason I find it incredibly frustrating to work with CSVs, because there is no "native" way for me to open the file and interact with the data in a native and intuitive way without running the risk of data being lost or edited without me noticing. I've resorted to importing the files into a local DB instance and using SQL to interact with the data, especially if the files are large.
- vegetable 3y ago
  You should never double click to open a csv file in excel. You should ALWAYS import the file into excel. Set your types during import. Many of the complaints about excel's csv handling in this thread are user errors.
  - Loranubi 3y ago
    Not in this case. Excel makes it extra hard for you to do correctly. They encourage the wrong way.
sparkie 3y ago
You don't even need to do that. Just stick a single ' before any number/date and it will be treated as text. The problems are when importing/exporting though. Even quoted numbers will lose the quote when exported to say, CSV. You need to explicitly save as quoted, and import quoted fields as text. The real issue is that these settings are not the default. In LibreOffice (Save/Open CSV): https://imgur.com/a/ved7wgA https://imgur.com/a/ved7wgA

by dmz73 3y ago

JimmyAustin 3y ago

A lot of analysis is done using CSVs being pushed in and out of Excel. Doing so strips the formatting. Please understand the workflows being crying “skill issue”.

knighthack 3y ago
Sorry, can't understand, and that excuse is unacceptable. It's totally a skill issue, and a failure of it, and a laziness of these 'scientists'. You'd expect scientists - people working to understand the nature of reality - to have some base competency about how they measure reality. Could have at least used a database for things like this; moreover any decent database can often import from CSV and export to CSV as well. Excel is not at fault here; the 'scientists' are.
- eigenket 3y ago
  Haha, you think scientists have control over what software their employer buys for them to work on. There are probably wonderful places where that is the case, and probably several of those places use something like libreoffice which doesn't do the idiotic data conversions excel does, but they are definitely not the norm.
- cqqxo4zV46cp 3y ago
  Coming up next: people should still use C because writing insecure code is a “skill issue”. Why can’t we as a society make ANYTHING easier without the usual blathering on from the peanut gallery turning it into a question of one’s intelligence?
  - deleted 3y ago
    [deleted]
bayindirh 3y ago
Yes, but you can create a small macro to the change the formatting and assign that to your table. Your CSV should import deterministically. That's not impossible to do. I saw a complete analysis engine written as an Excel file, which accepts and exports CSVs cleanly. It can be done.
- n4r9 3y ago
  "It can be done" is a far cry from "it's reasonable to expect scientists to do this".
  - bayindirh 3y ago
    As a person who does research and support researchers, I can't see the gap, sorry. I understand some people don't know it's possible, and some don't care, but for any competent researcher, it's expected them to master the tools they use. This is esp. true for career researchers.
    - djtango 3y ago
      I agree. As a researcher you may have to learn how to carefully dig up skulls, raise rats, handle lasers, remember not to accidentally syringe yourself with viruses etc. Getting cut by Excel seems like part of the job and at least is hopefully less life threatening than possibly blowing yourself up or giving yourself silicosis. That said the problem with computers is that they're pervasive, they're a moving target and often it's a case of the blind leading the blind when it comes to research. And probably more and more research groups need dedicated computer technician resources who can centralize the required computer knowledge of keeping a research group running.
    - n4r9 3y ago
      I'm not disputing that a competent computer user can do it. From the perspective of "this is what I would do if I was a scientist", you're totally correct. But when you're writing guidelines for an entire field - as the article describes HGNC doing - you're catering to all researchers in that field: good, bad, and ugly. Plus technicians, editors, admins and anyone else that might handle the files. Given how hidden and unintuitive Excel's behaviour is here, I think what they're doing makes sense.
mike_hearn 3y ago
You can write CSVs in such a way that forces Excel to infer specific data types: "=""Data Here""" will always be treated as a string. This is also supported by Sheets, apparently.
- mcintyre1994 3y ago
  TIL, neat! Does Excel automatically export like that if you format the columns as strings?
- _visgean 3y ago
  ok but will that work other tools that work with those csvs? I imagine that they export / import from excel to csv for a reason.

bayindirh 3y ago

That's too much work. Nobody have time for doing or learning that. Sounds like a joke, but it isn't. I know scientists who think exactly like that.

rrr_oh_man 3y ago
It’s probably the same scientists who answer an email 2 years later

cqqxo4zV46cp 3y ago

It’s utterly unreasonable of you to expect that your drive-by HN comment solves this problem for an entire community of people. This really just comes across as unproductive snark.

chrisjj 3y ago
> It’s utterly unreasonable of you to expect that your drive-by HN comment solves this problem for an entire community of people. I'd say more unreasonable is for you to mischaracterise this comment as you have done.

barryrandall 3y ago

C/C++ developers could just use the tooling available to them to mitigate memory management issues, but they don't. Why should we trust anything programmers produce if this is the level of competence they have with their chosen tools?

beAbU 3y ago

This is a very unfair and harsh comment. In my current work, we deal with our user's national identity numbers quite frequently. This number is a 13 digit numerical number, that starts with your date of birth. So someone born on March 13 1989 will have a number start with 890913. People born in the aughts have "00" "01" "02" etc at the start of their ID number. We need to frequently generate excel and csv reports that contain these numbers, and we need to ingest CSVs from other vendors that contain these numbers. The /moment/ excel touches a CSV with these numbers in, it'll assume that column is a number, it'll strip out the preceding zeros, and it'll format the number in scientific notation. If you change the column's data type to text afterwards, then it's too late - the damage has been done and you've worst case lost data, best case you have a text column full of scientific notation numbers. You can't just open up the CSV, you need to import it, and very explicitly tell Excel how to handle this column, otherwise you mess things up. Now, anywhere in the chain of people and other vendors sending and receiving these files, anyone who double clicks on that file and it opens up in excel and does not notice this very destructive action messes up our processes and causes unknown amounts of delays. It's the bane of my existence. This exact problem also crops up with phone numbers, where in many countries the number starts with a 0, or if it's an international number, a "+". Excel thinks the "+" makes the field a formula. All of this because Excel is making assumptions and trying to "help", in the same way a 4 year old helps in the kitchen. For this reason I find it incredibly frustrating to work with CSVs, because there is no "native" way for me to open the file and interact with the data in a native and intuitive way without running the risk of data being lost or edited without me noticing. I've resorted to importing the files into a local DB instance and using SQL to interact with the data, especially if the files are large.

vegetable 3y ago
You should never double click to open a csv file in excel. You should ALWAYS import the file into excel. Set your types during import. Many of the complaints about excel's csv handling in this thread are user errors.
- Loranubi 3y ago
  Not in this case. Excel makes it extra hard for you to do correctly. They encourage the wrong way.

sparkie 3y ago

You don't even need to do that. Just stick a single ' before any number/date and it will be treated as text. The problems are when importing/exporting though. Even quoted numbers will lose the quote when exported to say, CSV. You need to explicitly save as quoted, and import quoted fields as text. The real issue is that these settings are not the default. In LibreOffice (Save/Open CSV): https://imgur.com/a/ved7wgA https://imgur.com/a/ved7wgA