5 ms·
Excel is the source of so many problems. At work, we ask users for an input in CSV or Excel format, and most people see "CSV" and export Excel data as CSV. Whic
by pavel_lishin 9y ago
Excel is the source of so many problems. At work, we ask users for an input in CSV or Excel format, and most people see "CSV" and export Excel data as CSV. Which is fine and great, but long numbers - such as UPCs - show up in Excel as scientific notation, being big scary numbers, and also get exported as such.
So when an Excel cell contains the UPC 123456123456, we get a CSV file that contains "1.23456E+11", which is worse than useless.
- pc86 9y agoI used to work in third-party logistics and a big project of mine was an automated file import process, so folks could send us their daily/hourly orders for processing and fulfillment. I'd say roughly 65-70% of the entire code base was error handling and figuring out when to kick out a file for human review and/or outright deny it and contact the customer. The hardest ones to work with were the mom and pop shops who suddenly had some success on Amazon and came to us after fulfilling out of their garage for a year and a half. Try telling a semi-retired 60 year old electrician in the middle of Iowa that the file he sent is worthless because none of the product codes match what you have, especially when once he closes the file he doesn't have any idea where it is.
- mratzloff 9y agoA long time ago I did the same work with banks. The effect was the same. It was amazing to me how bank employees could somehow find a way to regularly insert ASCII control characters into a CSV value.
- robterrin 9y agoOn a consulting project a few years ago, I was brought in to help a Fortune 500 energy company make sense of the value at risk model that one guy had built over 20 years in VBA. Most of it was pulling from excel spreadsheets brokers would send daily with prices. What. A. Nightmare. It was hard to decide what I hated most about the project. The mind numbing stupidity of it. That fact that managers had allowed this single point of failure, and then let the guy leave. Or... that it was all a fictitious dance, pursued because spinning off their trading operations, would get them millions in tax subsidies.
- emerongi 9y agoI was introduced to programming by my father. He's a business analyst of sorts, so naturally he only knows VBA. After he left his job, some poor soul had to inherit his massive collection of VBA scripts, which more or less automated his day-to-day work. I think he showed the new guy what buttons to press on his spreadsheets, but I can't imagine he understood any of the Excel-fu my father had done. The legacy architecture that is tied to Excel in all sorts of businesses.. it's crazy.
- pavel_lishin 9y agoThese are the exact problems we're banging our heads against. The worst part is that it's not something that can be solved with an external dependency on some new startup - that would just add another layer we'd have to go through in the error cases, which would be numerous. God, I wish I could share some of the files we've received. I cannot conceive what sort of monster would write a data exporter that would produce these unreadable things.
- geocar 9y agoCSV is the source of so many problems. CSV has no character set, no rule for escaping double-quotation marks, commas, and newlines. There's not even a way to look at a CSV file and tell what it's "rules" are beyond heuristics and those only take you so far. I ask for XLSX files since at least it's structured, unambiguous and documented, but even better: a minimal XLSX parser is trivial (about a page) to write. Also: Educating users on how to specify the character set in every application that the user seems to want to use is a special kind of hell.
- PaulHoule 9y agoI'd say that the spreadsheet model is long in the tooth, but there has been a failure of will in the industry to kill it. People use Excel when they should really use a database, they use it because they want to format something on a 2-d grid, edit tabular data, make plots, do calculations, make projections, etc. The problems go down to the data structures in use. For instance there is nothing 2-dimensional about financial reports (and projections), really financial reports are hyperdimensional. Proper handling of dates and times is absolutely critical. Also the use of floating point with a binary exponent is a huge distraction in any kind of math tools aimed at ordinary people. (Mainframes got that right in the 1960s!) Google Sheets is just a stripped down version of Excel and other than the ability for multiple people to work on it simultaneously, is really no better.
- ohyoutravel 9y agoSo true. At my wife's accounting firm they recently moved from excel to Postgres. Accountants now interact with the database directly rather than messing with the mess that is excel and everyone is better for it.
- eru 9y agoIt's always fun to look down on the likes of Excel and PHP etc. Of course, we should try to learn, too. Excel for all its faults is easy for beginners to pick up. Semi-technical people can quickly hack together a bug-ridden prototype of their ideas. In most companies the alternative is not well-written software, but spending countless meetings to get IT to spend millions to provide a bug-ridden prototype in a few years time. One of the main problems with Excel as I can see it is that it is effectively a write-only language. Auditing an Excel sheet is famously almost impossible. And that's before you add VBA in the mix. Since I mentioned PHP: one of the best things I can say about the language and its ecosystem is that they made it really easy to add a 'web-counter' to your otherwise late 90s static html-only page on shared hosting. You begin with a web-counter, and then just keep on copy-and-pasting.. Haskell is not nearly that easy to pick up, even if you are prepared to do it badly.
- aqme28 9y agoZip codes also have a bad habit of getting reformatted, because leading zeroes are removed. 01101 -> 1101
- jhbadger 9y agoExcel is also well known for mangling gene names in expression data. No, SEPT2 (Septin-2) shouldn't be silently "corrected" to 2-Sep, but it is...
- ajanuary 9y agoExport side you obviously can't control :( But on import, if you use Data -> From Text, and on Step 3 select all the columns and make them "Text", that will prevent Excel from mangling any of the data (stripping leading zeros, evaluating strings starting with = as formula etc.)
- danhess68 9y agoReally quite rich people relying on Excel for economic world-scale critical stuff has ruined/cost more than a few lives. Do you blame Bill for his shitty software or do you blame the rich corporate types for not giving a shit/incompetent minimum effort IT?
- danhess68 9y agoIf you run your bank/brokerage off a fucking spreadsheet then maybe you don't deserve those six bars p.a.