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

Missing Covid-19 test data was caused by the ill-thought-out use of Excel

bbc.com

Post reply on HN
Print view View original

701–710 of 834 posts

Re: Missing Covid-19 test data was caused by the ill-thought-out use of Excel

#701

Post by iso1631 »

reportgunner wrote: post #678
theshrike79 wrote: post #639

Earlier quoted context omitted.

I vaguely remember a discussion about this and the problem boils down to Excel sheets being impossible to unit-test. There might be, for example, a formula going through 20k lines in column F. But the one on line 1138 has a typo and the formula references an incorrect cell. No human will ever go through all the lines to check. Excel itself doesn't check stuff like that. And there are no tools for it either.

Excel actually checks that and gives you an 'Inconsistent formula' warning with a little green corner similar to when you comment a cell. All you need to do is scroll through the worksheet you have just made.

All you need to do is scroll through 20,000 lines looking at each one

Re: Missing Covid-19 test data was caused by the ill-thought-out use of Excel

#702

Post by dpwm »

sheetjs wrote: post #567
yawaramin wrote: post #563

Earlier quoted context omitted.

Quoting doesn't work. Try opening this CSV with Excel: a,b "01",01 Excel interprets both as the same number–1.

The correct way to generate a CSV cell with a leading 0 is ="01" You can verify this with 01,"01",="01"

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.

Re: Missing Covid-19 test data was caused by the ill-thought-out use of Excel

#703

Post by bregma »

tiernano wrote: post #634
collyw wrote: post #619

Earlier quoted context omitted.

The real question should be would it be cheaper to replace it with a proper solution (probably something custom built application written by a software engineer). Excel's ease of use is it's downfall. It is the worlds most popular database, despite not actually being a database. I have wasted countless hours dealing with Excel where something else should have been used. I built a database for a friend recently, I thi…

it will only be cheaper after the company loses money... no business person ever looked at something and said "it works, but it might not, some day... better spend $XXX,XXX on an engineer to fix the problem".

You have just identified the new market of Spreadsheet Insurance. I am thinking it could be quite lucrative if you get the right salespeople in on the ground floor. I'm willing to bet no funding source is unfamiliar with the demand for this product.

Re: Missing Covid-19 test data was caused by the ill-thought-out use of Excel

#704

Post by bregma »

simonh wrote: post #653
tsjq wrote: post #518

Earlier quoted context omitted.

and one more: "Scientists rename human genes to stop Microsoft Excel from misreading them as dates. Sometimes it’s easier to rewrite genetics than update Excel" https://www.theverge.com/2020/8/6/21355674/human-genes-renam...

I think that's a good way forward, Excel is a fantastically powerful, ubiquitous tool and isn't going away any time soon. If you're going to standardise on a technology, it makes sense to design your systems to work well with that technology. That includes making identifiers that you need to be treated like strings look like strings to your tools. A little bit of thought in advance and maybe some small compromises ea…

A master crafter always adjusts their industry to match the currently popular tools. That's why we still use reins to steer automobiles and aircraft.

Re: Missing Covid-19 test data was caused by the ill-thought-out use of Excel

#705

Post by SifJar »

ivanhoe wrote: post #686
jugg1es wrote: post #538

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…

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?

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.

Re: Missing Covid-19 test data was caused by the ill-thought-out use of Excel

#706

Post by hurrrrrrrr »

bobbylarrybobby wrote: post #494
rossdavidh wrote: post #427

While I am not in the habit of defending Microsoft, I think it should be pointed out that it wasn't using "Excel" that was the biggest problem, but using "13-years obsolete format for Excel". Not that there was any reason to be using Excel for this, but it's a bit unfair to blame Microsoft for something they never claimed XLS could do, and provided a much better capacity in XLSX 13 years ago. Again, it's hard to cry…

The issue with is excel is that its failure mode is suboptimal. Programming languages will generally crash at runtime when an unrecoverable error occurs (index out of bounds, memory allocation failed, null pointer, etc). But excel just throws away your data?

> Programming languages will generally crash at runtime when an unrecoverable error occurs (index out of bounds, memory allocation failed, null pointer, etc).

JS would like to have a word with you.

Re: Missing Covid-19 test data was caused by the ill-thought-out use of Excel

#707

Post by robk »

rutthenut wrote: post #689
jugg1es wrote: post #538

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…

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 st…

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.

Re: Missing Covid-19 test data was caused by the ill-thought-out use of Excel

#708

Post by eCa »

eru wrote: post #642
eCa wrote: post #640

Earlier quoted context omitted.

I used to work with books, which obviously includes ISBNs. They are 13 digit numbers, helpfully turned into scientific notation by Excel.

Don't ISBNs also contain an X sometimes?

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.

Re: Missing Covid-19 test data was caused by the ill-thought-out use of Excel

#709

Post by Ntrails »

Maha-pudma wrote: post #585
jugg1es wrote: post #538

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…

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.

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.

Re: Missing Covid-19 test data was caused by the ill-thought-out use of Excel

#710

Post by ss64 »

reportgunner wrote: post #677
jugg1es wrote: post #538

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…

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.

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.
Post reply on HN