13 ms·

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 le

• tasogare 6y ago
  Quote your strings. The problem is more with how you generate CSV (which is easy to break in the first place) than Excel’s fault. It’s like writing your identifiers as numbers in JSON instead of string then complain the leading zeros are removed.
  • yawaramin 6y ago
    Quoting doesn't work. Try opening this CSV with Excel: a,b "01",01 Excel interprets both as the same number–1.
    • sheetjs 6y ago
      The correct way to generate a CSV cell with a leading 0 is ="01" You can verify this with 01,"01",="01"
      • rsanders 6y ago
        If CSV were being used just to exchange data with Excel, we probably wouldn't be using CSV. Many systems neither need nor know that ="01" should be treated as the string "01". If Excel were the only intended consumer, .xlsx would be a preferable file format. At least it's mostly unambiguous.
      • vertere 6y ago
        Perhaps by "correct way" you meant "dodgy hack to make Excel happy and risk breaking more sensible implementations"? Excel may predate the RFC but AFAIK MS didn't invent or coin the term CSV, so you can't just say whatever Excel does is correct. The RFC is loose because of nonsense like this, it doesn't mean it was ever a good idea.
        • _pmf_ 6y ago
          Do you want to be a useless sophist or develop software?
          • vertere 6y ago
            I sure don't want to have to deal with people putting `="01"` in CSV files.
            • altacc 6y ago
              We have multiple legacy systems where I work, communicating via batch csv files, some of which are authored & curated by staff in Excel. I can confirm that doing this would be a very bad thing indeed and make our systems grind to a halt.
          • dragonwriter 6y ago
            For CSV that isn’t solely targeting Excel, hacks around the way Excel works are useless.
          • edejong 6y ago
            I guess the HN audience has voted. The result is: hacking around Excel stupidities is itself a stupid idea. If you want Excel to understand your output, perhaps use a library which can write xlsx files.
          • OskarS 6y ago
            The reason you use CSV is that you want to use it with software that ISN'T Excel. Otherwise you'd just use .xlsx. No other software uses this convention, and it's not correct CSV.
      • terwey 6y ago
        “Correct” is a strong word. It isn’t defined in https://tools.ietf.org/html/rfc4180 https://tools.ietf.org/html/rfc4180 so Excel should not try to be smart and add extensions only they support.
        • sheetjs 6y ago
          Excel predates RFC4180 by nearly 20 years (RFC4180 is October 2005, Excel 1.0 was September 1985) and this behavior was already cemented when the RFC was written. As for the actual RFC, it's worth taking a read. Any sort of value interpretation is left up to the implementation, to the extent that Excel's behavior in interpreting formulae is 100% in compliance with the spec.
          • throwaway_pdp09 6y ago
            What spec? Anyway the RFC doesn't mandate any value interpretation IIRC.
          • dtech 6y ago
            CSV predates Excel, and other CSV implementations don't have this behavior
      • andreareina 6y ago
        And now the csv parser (or downstream process) has to guess whether to interpret that as the raw string or as the eval'd value.
      • account42 6y ago
        Not even Excel uses that syntax when exporting to CSV (at least by default).
      • giantDinosaur 6y ago
        I have a list of companies I'd like you to consult for. Coincidentally, they're companies I'd like to work for, and I've quite enjoyed building proper database solutions which replace incredibly hacky/terrible Excel (or Excel adjacent) solutions.
      • dpwm 6y ago
        I ran into this last week with a UK bank. I was offered a CSV file. What I got was a CSV file with excel formulae in it. I actually wanted a CSV file – preferably without having to resort to sed to strip out excel formulae.
      • zentiggr 6y ago
        If you want to write a bespoke CSV generator for an application where you know for sure that the file is only ever going to go straight to an Excel instance, sure. For all the other uses in the world, that's a breaking change.
  • zelphirkalt 6y ago
    Are you asuming creation of CSV? 'cause most excel users wont be aware of why they should store in csv instead of excel's proprietary formats.
• andreareina 6y ago
  IME numeric identifiers are just asking for trouble. Some systems are sensitive to leading zeros, some systems will zero-pad, <input type="number"> loses zero-padding, etc. Worked with a client once with references to the same entity scattered across two databases, one of which zero-padded the identifiers and the other didn't. Oy vey.
  • throwaway_pdp09 6y ago
    > IME numeric identifiers are just asking for trouble That's a failure of the system if it can't be told to not interpret the data. However you're saying the world is as it is; can't argue.
• pjmlp 6y ago
  I just upvoted you. You are fully correct, I have seen plenty of stuff like that in life sciences projects.
  • deleted 6y ago
    [deleted]
• Maha-pudma 6y ago
  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.
  • reportgunner 6y ago
    Well Excel trying to be clever stems from people not trying to be clever. We truly reap what we sow.
  • Ntrails 6y ago
    As an intern I was once given a spreadsheet of manually entered daily data, stored by a human being over the previous couple of years. Obviously he'd entered the dates in whatever form he felt like at the time and Excel had parsed them in a variety of godawful ways not least of which was defaulting to American month/day assumptions before UK day/month. I think it would have been far quicker to just manually write a new column interpreting the dates based on previous/next etc. Instead I spent God knows how long trying to be clever, failing, and being embarrassed that I could not solve this obviously trivial problem.
  • jasode 6y ago
    >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 questions on how to preserve them: - Python Pandas import csv issue with leading zeros: https://stackoverflow.com/questions/13250046/how-to-keep-leading-zeros-in-a-column-when-reading-csv-with-pandas https://stackoverflow.com/questions/13250046/how-to-keep-lea... - R software import csv issue with leading zeros: https://stackoverflow.com/questions/31411119/r-reading-in-csv-file-removes-leading-zeros https://stackoverflow.com/questions/31411119/r-reading-in-cs... - Google Sheets issue with leading zeros: https://webapps.stackexchange.com/questions/120835/importdata-in-google-spreadsheet-and-keep-the-leading-zero https://webapps.stackexchange.com/questions/120835/importdat... Conclusion: For some compelling reason, we have a bunch of independent programmers who all want to remove leading zeros.
• dt3ft 6y ago
  > I have no idea who downvoted my post That would be the Excel devs working at Microsoft. They read HN. I can feel it.
  • edejong 6y ago
    Excel devs, do you hear me? Repent for your sins! Go back to Microsoft and fix those problems. The cost of Excel mishandling numbers and dates must be many orders of magnitude higher than your lifetime salary. Thank you.
    • wbl 6y ago
      They can't. All those spreadsheets that depend on that behavior are out there. It makes spell create with an e look easy.
• deleted 6y ago
  [deleted]
• TacoSteemers 6y ago
  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.
  • altacc 6y ago
    Introducing standards across many companies is incredibly difficult but your main problem is that a lot of healthcare IT systems are very old and not actively maintained, they're essentially on life support. So changing the format of a file, or requiring new import cleaning, is going to break integrations and data sharing in many places. Basically it'd create far more problems than it's attempting to solve.
    • TacoSteemers 6y ago
      That is understandable. I guess it depends on which country and which specific part of healthcare you are active in. In the 'care' domain in the Netherlands I also see that some of our integrations are one-off, but the most important ones do have industry wide standardization that receives updates based on law.
  • IanCal 6y ago
    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.
    • TacoSteemers 6y ago
      Good catch! The Wikipedia page confirms it. Nothing in software development is as straightforward as we might hope. https://en.wikipedia.org/wiki/Symbolic_Link_(SYLK) https://en.wikipedia.org/wiki/Symbolic_Link_(SYLK)
• petre 6y ago
  Just format the identifier cells as text. I've also had this problem, this is how I solved it.
  • simonh 6y ago
    That's fine for an individual working on a specific set of data in a specific sheet, but this isn't just a problem for one individual or a small team that can solve it and move on. It's a systemic problem throughout many vast organisations, with continuous influxes of new personnel and constantly changing requirements. When you get an XLS sheet sent over to you from another team that already made this mistake, it's too late and this happens all the time.
  • dragonwriter 6y ago
    > Just format the identifier cells as text CSV is text. If you mean in Excel, if you opened it in Excel (rather than importing and choosing non-default options), you've already lost the data so formatting doesn't help you.
    • petre 6y ago
      Yes, I mean Excel. We have CSV to XLS import scripts/forms that format identifier cells as text. The data format is standardised. Using templates to do the imports was the dumb part. Microsoft has a Power BI tool if ones doesn't want to write or use scrips. Use that. I assume a government agency has the resporces to pay for it and for data scientists. https://powerbi.microsoft.com/en-us/ https://powerbi.microsoft.com/en-us/ Thanks for bothering to respond instead of downvoting.
      • zentiggr 6y ago
        > I assume a government agency has the resources to pay for it and for data scientists. Bold strategy there, let's see how that plays out. Having been in and around military / DoD usages for a long time, I can tell you it's always an uphill battle to get processes to work well, instead of defaulting to whatever the original spec happened to get included as a result of some incompetent who wasn't even aware of good practice.
• eCa 6y ago
  I used to work with books, which obviously includes ISBNs. They are 13 digit numbers, helpfully turned into scientific notation by Excel.
  • eru 6y ago
    Don't ISBNs also contain an X sometimes?
    • timclark 6y ago
      X can be a check digit. From memory the check digit is calculated using a modulo 11 calculation.
    • eCa 6y ago
      The old ISBN10 standard could get X (meaning 10) as the last character (the last number is a check digit), but ISBN13 (which prefixes 978 or 979 to ) is all numbers. The check digit is calculated differently. One obvious advantage is that the ISBN13 is barcode compatible. I would guess that most modern actors in the book business has been primarily using ISBN13 for at least the last decade.
      • eru 6y ago
        Thanks! The last time I read about ISBNs was probably more than a decade ago.
  • Cyberspy 6y ago
    The same problem happens with UK phone numbers, where the leading 0 gets chopped off Solvable if there's a space between the dialling code and the number, but when individuals enter their number, they don't always do this
  • eythian 6y ago
    I used to do migrations between library systems, and often they'd export from the old system as CSV. One rule I gave them was "never open this file in excel", because they'd always try to be helpful by tidying something and I'd end up with scientific notation ISBNs, dates that were in one form if they could be coerced into US-style infix, otherwise left in the original form, barcodes losing zeros, etc. etc.
• reportgunner 6y ago
  The right way to open CSVs in Excel is: Data -> From Text -> Browse to your csv -> Delimited -> Comma -> Next -> Select Text formatting for columns with leading zeroes -> Finish You can either send xlsx with format or csv without format. If this would be disabled then we'd have another group of people complaining that their dates from CSV are not parsed.
  • ss64 6y ago
    and to enforce that, remove the file association which opens .CSV files in Excel by default. If every machine opened CSV files in notepad a huge percentage of 'Excel' problems would just go away. It could be worse though, try opening and saving a CSV in Word and see what that does.
• ivanhoe 6y ago
  Couldn't you validate the data on upload or if it's such a common problem just auto-fix it by left padding w/ zeros?
  • SifJar 6y ago
    auto-fix by padding assumes that the only way to get an ID that's too short is by excel removing the leading zeros - presumably it could also be human error when entering etc. (in theory I suppose that would be validated in whatever form the number is entered into in the app that generates the CSV, but hard to say for sure). Or some other issue in data processing pipeline erroneously truncating the ID number some how. Validating the data would at least prevent getting invalid data into database (and presumably this is happening already), but it doesn't actually "fix" the problem, you still then need the original provider of the data to fix what's missing.
  • rutthenut 6y ago
    Validating on upload is sensible and should be done anyway. However, left-padding is not always going to work, it depends on the data. Common problems I see are with phone numbers. UK mobiles begin 07nnn. Excel kindly drops the leading zero and then really helpfully treats the long number as being an exponential-format floating point number, which cannot be turned back to the original number. In our systems, we require country codes in front of the numbers, so UK mobiles should being 447nnn. That doesn't lose the leading zero but still gets the E-format mess when opened by Excel. Customers often miss out the 44 and use the national 07 format. If that leading zero is missing, it can be quite difficult for a system to reliable determine whether that number, beginning with a 7, is an international number for a destination in Russia, or a user error and should've been 447, from an 07 data value.
    • mantap 6y ago
      One wonders why Excel doesn't store data as strings (or equivalent lossless format) and process it as numbers like SQLite does for instance.
      • 6y ago
• rutthenut 6y ago
  Agree with the problem reported here - the fact that Excel will drop leading zeroes and will turn phone numbers into Exponential format causes many live issues where customers use Excel to work on 'CSV' files :(
• rutthenut 6y ago
  Interesting to see just how many responses to this real-world problem are suggesting that people rewrite CSV files to work with Excel. They clearly do not understand system integration and the use of CSV text files for data interchange between multiple systems and application. Hey, JSON and Javascript libraries are the answer to that, eh There are already enough potential issues with CSV interpretation on wrapping strings, escaping characters and so on, but changing the content when a delimiter is found should not be added to that list. You bold point is the most important, the default behaviour of Excel when opening a plain text CSV file is to alter the content for display, applying magic and often-unwanted formatting rules. That should be optional. It should be possible to open a text CSV file in Excel, view the contents in columns but the same textual form, save the file in CSV format and open it in another viewer/editor and still see the same content as the original file.
  • deleted 6y ago
    [deleted]
  • robk 6y ago
    The problem is windows doesn't have a preloaded double click viewer for csv except excel if you have Office. The average user just double clicks. If notepad or wordpad did any kind of formatting for viewing it would be less an issue. So you have to solve for excel as a dev knowing that's what 99% of people will use.
    • rutthenut 6y ago
      If you 'solved for Excel', it will be broken for the other applications that may want to use data from those Excel-specific CSV files, which is generally why CSV was chosen in the first place.
  • stjohnswarts 6y ago
    If you want it to be a string then put it in quotes. It's that simple. If the user is too dumb to slow down and look at the import options it is not excel's fault, it's a process issue. Excel can't read a cell and then read your mind and extract the correct option.
• cm2187 6y ago
  Also long numeric ids are rounded by Excel. And fucking with dates.
• mratsim 6y ago
  Working internationally with CSV holding dates is a recipe for disasters.
• mikorym 6y ago
  It is sometimes possible to write code to alter your CSVs to retain the strings. The first thing would be to write them as: entry1,"0123456789",entry2 rather than entry1,0123456789,entry2. This has worked for me in some instances in Excel whereby I have to escape certain things inside a string, but I would not be surprised if Excel still messes this up. For example, giving the triangle exclamation mark box and then helpfully suggest to convert to number. If you want to go further, you can do something like write a routine that alters the CSV, such as entry1,hospitalString(0123456789),entry2. Sure, there are problems with this too, but Excel can break a lot of things and the above examples I do use in practise (the first example I put the double quotes to escape single quotes in foreign language unicode). Another thing Excel can do is break your dates, by switching months (usually only for dates < 13th of the month, but often a partial conversion in your data for < 13th and >= 13th) or convert dates to integers.
• tomp 6y ago
  So what's the big deal? Just read back the numbers, if it's less than 10 digits prepend zeros. Am I missing something (probably, which means you're leaving something out in the description of the issue)
  • jugg1es 6y ago
    The 10 digits was just an example. There like 100 different ID types with different formats/lengths in healthcare.
• baobabKoodaa 6y ago
  How on earth is this the top rated comment? I would downvote it if I could. As other people have noted, you can import CSVs values as strings instead of numbers. Furthermore, losing preceding zeroes in number-typed values is not unique to excel; it is a common feature in all typed programming languages.
  • SifJar 6y ago
    > you can import CSVs values as strings instead of numbers Of course, but the problem isn't that the person who posted the comment doesn't know this - it's that many users of their systems don't know it. Most people are just going to accept whatever defaults Excel suggests and not know any better, causing problems down the line.
    • baobabKoodaa 6y ago
      No, that's not correct. The parent poster specifically made this false claim: "that's Excel's default behavior and you CANNOT TURN IT OFF!" The false claim is the part that the poster wrote in all caps. He said that this behavior can not be turned off, when in fact it can be turned off.
      • jugg1es 6y ago
        How? Where is the setting to turn it off when initially loading a file?
        • jasode 6y ago
          >How? Where is the setting to turn it off when initially loading a file? Confusion is happening because 2 different ideas of Excel using csv files: - you saying "can't turn this off" : File Explorer double-clicking a "csv" or MS Excel "File->Open" csv. - others saying "you can preserve leading zeros" : click on Excel 2019 Data tab and import via "From Text/CSV" button on the ribbon menu and a dialog pops up that provides option "Do not detect data types" (Earlier version of Excel has different verbiage to interpret numbers as text)
• lr 6y ago
  First off, I “hate” Excel, but only because people misuse it. It, like so many other products of its kind, are for numbers! Yet I would be hard-pressed to find someone actually using it for numbers. People absolutely insist on using it as a generic table system for holding non-number data. I recently had to show a 20+ year Excel-using fanatic how to import data from a CSV file so that they could select as type Text columns that contain leading zeros. The ability exists, but I have found scant few people who know how to actually use the product properly. Oh, and I also work in healthcare.
  • mewpmewp2 6y ago
    As a lay-person, what would you use for non numbers tabular data?
    • lr 6y ago
      People love to use Excel to look at (with their eyes, or a screen reader, i.e., non programmatically) all kinds of data. They use it to list exports from data stores, and then take notes on what they discovered, show and hide data using the filter option, etc. It is just far too easy to use (and misuse) that it ends up being used for any type of data that fits into a table.
      • mewpmewp2 6y ago
        I mean I thought you said that they should not use excel for these scenarios, but something else. So I thought you had an alternative software in mind?
        • lr 6y ago
          I use Splunk (our organization pays lots of money for it, and yet it just sits there because people would rather use Excel). There is also Tableau and other products like it. Most of these tools have a free version, as well. They take some learning, but it is well worth it. Of course, taking that same amount of time to actually learn how Excel works is worth the time, too. I guess the issue is that Excel is just too "easy" to use, and hence people using it for years and years and not actually knowing how to use it. I am not trying to be an elitist about this. It is just that the misuse of Excel (because people do not know how to use it) causes massive issues on a daily basis.
• 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.
• radmuzom 6y ago
  Down-voted as it is factually incorrect. See comment by reportgunner below.
• deleted 6y ago
  [deleted]
• noja 6y ago
  Does it strip leading zeroes if you format the numbers as strings with quotes like this? blah,blah,"00001553",blah
• kazinator 6y ago
  > there isn't a way to get them back if you save/overwrite. 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".
  • jugg1es 6y ago
    The lengths of the identifiers are not known, man. We are dealing with like 200 hospitals. They all have various kinds of Electronic Medical Record system like Epic or Cerner, or one of the 15 other third party vendors that all have custom implementations. Healthcare is insane in our country.
• slowhand09 6y ago
  The issue I see is you have people who don't know the difference between a string data type and a number. I keep see "Listen to the scientists" and "Listen to the doctors". There are many facets to solving problems. Maybe many disciplines may be better at solving it. Had you/"healthcare" contacted folks with software expertise rather than being your own experts, this would have been a non-problem.