4 ms·

> Excel interprets a number and then formats it as a number, Unless you instruct it to interpret the field as a string. > but in healthcare, 10 digit numbers

by pif 6y ago

> Excel interprets a number and then formats it as a number, Unless you instruct it to interpret the field as a string. > but in healthcare, 10 digit numbers are really strings. I'm wondering, if you expect 10 digits and you get less than that, how difficult is it to add some padding zeroes?

lr 6y ago
This would assume people (many who have over a decade of experience using Excel) actually know how to do that. The largest problem with Excel is people.
- carlmr 6y ago
  Their database system afterwards could do that. Or prepend a letter when producing the CSV to avoid EXCEL doing what it does.
- rustybelt 6y ago
  Excel rules. I still use it daily despite having access to big boy tools (python, SQL, R, etc.) Few tools make it easier to answer quick, simple questions about a dataset. Plus it's still the best way to browse SSAS cubes. Blaming excel for these sorts of problems is like blaming a hammer for not being a screwdriver. I also work in healthcare.
hunter2_ 6y ago
With US ZIP (postal) codes, padding with zeroes can actually be ambiguous because we have "zip+4" which is often stored as two fields, but just as often concatenated into a single representation. If you have a zip+4 of "05555-0444" in two fields that each lose leading zeroes, and then concatenate to "5555444" you can't reconstruct it with certainty (00555-5444 or 05555-0444 or 55554-0044). I had a package get seriously delayed one time because it kept being sent to a city whose 5 digit zip code was equal to [my 5 digit zip code, less its leading 0, and the first digit of my +4]. Fun times.
oauea 6y ago
> Unless you instruct it to interpret the field as a string. Except double clicking a CSV does not give you the option to do this. At this point Excel already decided to corrupt your data. And guess how most users open CSV files? That's right, they double click. > I'm wondering, if you expect 10 digits and you get less than that, how difficult is it to add some padding zeroes? If you expect 10 digits and get less than that, you have corrupted input data. Trying to "fix" this is exactly the sin excel is committing. Don't do that.

by pif 6y ago

lr 6y ago

This would assume people (many who have over a decade of experience using Excel) actually know how to do that. The largest problem with Excel is people.

carlmr 6y ago
Their database system afterwards could do that. Or prepend a letter when producing the CSV to avoid EXCEL doing what it does.
rustybelt 6y ago
Excel rules. I still use it daily despite having access to big boy tools (python, SQL, R, etc.) Few tools make it easier to answer quick, simple questions about a dataset. Plus it's still the best way to browse SSAS cubes. Blaming excel for these sorts of problems is like blaming a hammer for not being a screwdriver. I also work in healthcare.

hunter2_ 6y ago

With US ZIP (postal) codes, padding with zeroes can actually be ambiguous because we have "zip+4" which is often stored as two fields, but just as often concatenated into a single representation. If you have a zip+4 of "05555-0444" in two fields that each lose leading zeroes, and then concatenate to "5555444" you can't reconstruct it with certainty (00555-5444 or 05555-0444 or 55554-0044). I had a package get seriously delayed one time because it kept being sent to a city whose 5 digit zip code was equal to [my 5 digit zip code, less its leading 0, and the first digit of my +4]. Fun times.

oauea 6y ago

> Unless you instruct it to interpret the field as a string. Except double clicking a CSV does not give you the option to do this. At this point Excel already decided to corrupt your data. And guess how most users open CSV files? That's right, they double click. > I'm wondering, if you expect 10 digits and you get less than that, how difficult is it to add some padding zeroes? If you expect 10 digits and get less than that, you have corrupted input data. Trying to "fix" this is exactly the sin excel is committing. Don't do that.