781–790 of 834 posts
Earlier quoted context omitted.
Maybe it can be solved by prepending "ID:" for exports, and removing it on import. It could be proposed as a convention for all HC software companies.
Although make sure to not use that for the first column heading, an uppercase I will make excel think it's a SYLK file and then refuse to open it.
Nothing in software development is as straightforward as we might hope.
Earlier quoted context omitted.
Every single problem I've ever had with excel stems from excel trying to be too clever. Nearly all my problems with importing data into it could be solved if excel left everything as is until I asked it to format values a certain way.
>Every single problem I've ever had with excel stems from excel trying to be too clever. Nearly all my problems with importing data into it could be solved if excel left everything as is We're biased to bash on Microsoft for being "too clever" but maybe we need a reality check by looking at the bigger picture. Examples of other software not written by Microsoft that also drops the leading zeros and users asking quest…
Earlier quoted context omitted.
Does it? I think it should treat everything as a string since it can't be certain about the correct type. It could also suggest the correct type rather than applying it without user confirmation.
You want Excel to auto-interpret a table of numbers, that come from a CSV, as entirely text? Looking forward to the first time anyone tries to use your excel on a table of numbers and then immediately has to multiply everything by *1 (in a separate table) just to get it back into numbers...
At least you would know what's happening and be in control of it
"Hey, is that a date? I bet that's a date!" - Aaaargh Noooo!
Excel is NOT a database. I don't understand the people who use it as such (and I've come across many who do). Then they're surprised when it all goes tits-up.
If you're thinking regular office workers, they know of no alternative. Excel works enough for small data, particularly when you don't do complex queries on it. The more you know of it, the better it works. The only other thing that offers similar benefits to Excel but works as a database is MS Access, but the mental model behind it is too complex for your average office worker who wasn't trained in it, and like most…
I'm a bit of a novice when it comes to storing vast amounts of data, especially of this nature, but why would you use Excel rather than an actual database?
That's not saying Excel is the best way to store data, but it gets a lot of jobs done without multiple month delays that come with an IT project. Often times, waiting 2 weeks because they're already busy then spending another 2 weeks outlining requirements is categorically unacceptable to accomplish the business goals.
Earlier quoted context omitted.
To add to this point, the latest article says that they started building a replacement to the spreadsheet two months ago, so I think that reinforces the speed of a spreadsheet implementation vs anything else in the public sector!
You might not have realized this but they should have started work in January.
Earlier quoted context omitted.
>Every single problem I've ever had with excel stems from excel trying to be too clever. Nearly all my problems with importing data into it could be solved if excel left everything as is We're biased to bash on Microsoft for being "too clever" but maybe we need a reality check by looking at the bigger picture. Examples of other software not written by Microsoft that also drops the leading zeros and users asking quest…
The issue is that CSVs are untyped and software has to guess the type, sometimes unsuccessfully.
Earlier quoted context omitted.
You are right on the cause of the issue. But the software does not HAVE to guess the type. If I paste something into Excel, I want what I pasted to be what's in Excel. Changing the type and inadvertently affecting the data isn't something any data software should do by default. And the fact that OP said they can't turn it off means that this is beyond a design mistake, it is a FEATURE. If Excel took itself seriously…
You can format the column as text before you paste or you can load data from text and set the types for every column. Excel has features to handle all of these issues. I think it's biggest problem is that it's just too easy to use. It will allow novices to do all sorts of things with their data without providing guardrails for common mistakes.
People just double click the CSV and complained that it didn't do it correctly. It is the same situation with scientific research data that researchers don't bother to use escape marker or blindly open the file without going through the proper import process. Then they blamed Excel for the that without understanding how Excel works.
Yes, Excel does have their quirks. But there are ways around those quirks, they have thousands of thousands guides out there about Excel. There is no excuses for people to complain about Excel didn't do the way that users want it to do without looking up for information.
I work in healthcare and by far the biggest production issues we've ever run into are people who run Excel spreadsheets through our file processing where the leading zero's have been removed from patient identifiers because that's Excel's default behavior and you CANNOT TURN IT OFF! EDIT: I have no idea who downvoted my post because what I said is 100% true. We have to tell customers to stop opening CSVs in Excel and…
Of course there is, in your case. If patient identifiers have a fixed, known length, then you can pad with leading zeros to recover them.
You only have a problem if 012345 and 12345 are distinct patient identifiers.
It is bone-headed in the first place to use numeric-looking identifiers (such as containing digits only) which are really strings, and then allow leading zeros. Identifiers which are really strings should start with a letter (which could be a common prefix). E.g. a patient ID could be a P00123.
This is useful for more than just protecting the 00. Anywhere in the system, including on any printed form, if you see P00123, you have a clue that it's a patient identifier. An input dialog can reject an input that is supposed to be a patient identifier if it is missing the leading P, or else include a fixed P in the UI to remind the user to look for P-something in whatever window or piece of paper they are copying from.
The main point in my comment is that instead of shaking your fist that the behavior of other users in the system, such as those who choose Excel because it's the only data munging thing they know how to use, you can look for ways that your own conventions and procedures are contributing to the issue.
If people are going to "Excel" your data, and then loop it back to you, maybe your system should be "Excel proofed".
Earlier quoted context omitted.
You can format the column as text before you paste or you can load data from text and set the types for every column. Excel has features to handle all of these issues. I think it's biggest problem is that it's just too easy to use. It will allow novices to do all sorts of things with their data without providing guardrails for common mistakes.
Agreed with this. Excel does have data import feature that will allows us to fine-tune the import process to prevent this kind of mistake. People just double click the CSV and complained that it didn't do it correctly. It is the same situation with scientific research data that researchers don't bother to use escape marker or blindly open the file without going through the proper import process. Then they blamed Exce…
Double clicking the CSV should open the data import dialog.