Excel as a database
wyorock.com
wyorock.com
A few years ago I was asked to build a database in Excel which would import spreadsheets that clients submitted, accumilate all that data then spit out a whopping great big CSV file every month. And the most insulting thing of all was that I had to build this abomination using VBA macros so I could guarantee that the CSV output was clean enough to be exported into an Oracle RDBMS we also ran. I frequently raised the point that we should be running all of our databases on Oracle, but I was refused every time (I could write a whole essay on their dumb rational behind this, but that's another rant entirely).
So off I set writing one of the most complicated Excel macros of my life (and wondering what I'd done in a past life to be lumbered with such moronic management). I wrote every piece of data cleansing I could imagine knowing full well that the spreadsheets our clients would be submitting are bound to have had their formatting messed around with (and trust me, I tried everything I could to lock that template spreadsheets down). Yet, and without fail, every month at least 10 spreadsheets would be rejected from my macro's import routine because the data was so garbled that it was beyond cleansing.
Usually it was a simple job to fix the spreadsheets - or a simple phone call to the client where I verbally slapped them as much as I dared; vainly hoping that might serve as a warning to them that they shouldn't be cocking about with the layout of the template. Needless to say, it never had any affect.
However this one time I was completely baffled. The spreadsheet look fine at first glance, yet my routine rejected it. Sure, I couldn't click inside any of the cells, but the spreadsheet was locked from editing so that was to expected......or was it?
It turned out that the idiot who submitted that spreadsheet printed out their completed template, then scanned it in on a MFD (a scanner with bells and whistles). Then got that MFD to e-mail them a PDF of the printed page and now scanned page so that they could then embed that back into the fucking template.
All I asked them to do was fill a couple of columns of data then e-mail me the spreadsheet as an attachment. But nothing is too simple when you're dealing with users who think they understand computers.
Turns out that she was opening a CSV file in Excel, saving it, and then it couldn't be parsed.
It took a surprising amount of training for her to understand that this really was "changing something". When she eventually got it she asked why she was supposed to be able to look at it at all. My reply is that that was the spec, and someone higher up thought she should see it.
She stopped looking at it and the problem went away.
Please do not simply rename the file extension of your file to match the formats we require. They will not open.
Apparently there are plenty of people who thing changing the file extension from .docx to .psd changes to format. Windows (and OSX too, I guess) are partially to blame here as they'll happily change the icon
Although the users would still blindly click "Yes" even if the dialog asked for their firstborn child.
Now here's an idea for any software developers - grab AutoIt (is it still around?) and make a quick script called "typical_user" that automatically clicks "Yes" (or similar) on any dialogs that appear. Now, run this in the background while testing the UI of your software :)
Edit: Yes, AutoIt still exists! http://www.autoitscript.com/site/autoit/
For automation, it's replaced AutoIt in my toolbox - just in case I get another contract in the future like one of these.
I have literally read the message on the screen to someone to solve their problem.
Even worse, most of the "right" solutions eliminate the easy look at dozens of rows that a spreadsheet offers (letting the user do fast unconscious visual comparisons in their own data) vs. showing what's essentially one row out of some table at a time.
Sometimes a simple grid that users can quickly navigate around, enter stuff into and see all of is the best solution.
The problem is that we (the tech industry) seem to only offer two choices, do it a) the wrong Excel way b) do it the huge giant IT initiative way...
I bet there's huge money to be made in a simple spreadsheet-like user interface that can be setup with similar constraints as a database (field types, foreign key constraints etc). That's all it seems both sides really want.
It would be an amazing tool.
My point: Over time, I came to appreciate Excel and realize what an amazing program it is. It is accessible to pretty much anyone who knows how to use a computer, and at the same time can basically be used to create mini-programs.
That's the reason you only have the two choices you mentioned: a) do it the wrong Excel way b) do it the huge giant IT initiative way
Also, Excel does provide the ability for both the data type and foreign key constraints you mentioned.
Doing the wrong thing the right way - How to use Excel in ways it was never intended.
They're great up to a point, and that point is sharing. I'm actually surprised that google docs hasn't picked up more traction for that reason alone. sharing a google docs spreadsheet doesn't cause divergence.
That's exactly what Access does (and Filemaker and literally 1000's of other packages over the last 2 or 3 decades), and still 'real' computer people piss on them and the people using it. And they're too difficult for 'normal' people to use beyond flat tables and forms, so in the end you still end up with a single choking point of one person who knows how these 'applications' work.
The Excel way is in may cases the right way, from a business POV, and the collateral damage here are the developers who have to deal with the quirks of such a workflow.
If we're working in a computer age of technology you can either fight it and fall behind, try to do things, doing them wrong, and fall behind, or suck it up and hire professionals for doing jobs a professional should do.
Sorry for the mini-rant. I'm working for a small company right now as "the software guy" because everyone else here doesn't care about keeping things efficient. I don't blame them, they have their jobs to do and dealing with software sides of things for 2 hours every day isn't ideal nor appealing. But when you have a software expert on staff and you refuse to take his recommendations don't come crying to him when your half-baked workaround backfired.
Maybe the point I'm aiming to make is developing database systems that are full featured yet still usable by people who can't mentally wrangle data and storage is a lost cause. I know we aim to empower users with their data but at times things become too vast and too important for a person without formal experience in large databases to "get their hands dirty" at the risk of blowing everything up.
"22. A web-based Excel/database hybrid. People often use Excel as a lightweight database. I suspect there's an opportunity to create the program such users wish existed, and that there are new things you could do if it were web-based. Like make it easier to get data into it, through forms or scraping.
Don't make it feel like a database. That frightens people. The question to ask is: how much can I let people do without defining structure? You want the database equivalent of a language that makes its easy to keep data in linked lists. (Which means you probably want to write it in one.)"
I've seen people use spreadsheets as word processors. They don't know how to layout a document in a word processor, but this spreadsheet lets them put words wherever they want.
Sometimes people misuse spreadsheets. I guess it'd be really good to have some kind of transition from spreadsheets to more suitable tools. (Perhaps using spreadsheets as examples an metaphors?) This doesn't have to be software, it could be a short course on a website.
And even when b) is chosen, when users log into the multi-million dollar CRM system, the first thing they reach for is still the "export to Excel" button.
I see it as a failure of education. I have no CS background and when I was starting out at my first job, I would love to have known then what I know now about databases and database-driven application programming. I would love to have had a proper class or two about it in school. But I didn't, so all I knew was the bad, wrong, "e-mail the spreadsheet" way. At my old company we had people whose entire job was basically to take a huge Excel file, turn it into a pivot table (basically a simple cross-tab query) and e-mail the result to people periodically.
I love Access forever because it sucked me into programming. I heard that it was a database but I didn't know what that meant and didn't have anyone who could teach me, so I learned it just by poking at it when I was bored at work until it started to make sense. Then I used it to mash two customer data sources together and tie a sales lead tracker to it--basically a quick and dirty replacement for our slow, over-engineered Oracle CRM system that no one actually used. I know now that what I made was also wrong in many ways and would make a real developer recoil in horror, but it was great fun to make and people actually used it. And it got me interested in databases, which got me into making LAMP apps, which got me into Rails, and so on...
Is it a poor replacement for a real database and application? Frequently, yes. But it frequently isn't realistic to spec & build a new application to support a growing business process.
All in all, I'm not clear why we don't celebrate how empowering Excel can be to many business users, even if it can be frustrating to deal with when it evolves into a core piece of a critical business system.
Sometimes we do.
Not all times.
This is one of those "not all times". In this instance we lament the misuse of Excel by business guys.
Excel is a fantastic tool, no doubt. It's powerful, flexible and quick to knock up. But it scales abysmally. And that's through no fault of it's own because spreadsheets aren't meant to scale, they're meant to convey tabulated data.
Perhaps even something that looks like excel but is stricter with it's inputs and some easy way to handle relations.
Even showing somebody rails scaffolding can get you a fair way.
The complicated technical part is making something that enforces strong schemas and good support for concurrent users without feeling complicated or overly constrained.
It doesn't necessarily have to do everything that excel does, just be good for use cases like "make a customer list that everyone from the CEO to the new temp can use at once"
I think the most prescient point so far has been that Excel quickly gives you a sanity check of you data, because it gives you the full view of days right away. Working on an ERP rewrite right now, this has been our continual issue, making all of the data visible, in ways that the old system never did.
- it's easy to use
- everyone has it
- it maps nicely to the skills user have (like moving files around to copy data)
- it's flexible
The last thing is important here. The problem with most database applications is that they are inflexible. Users are not allowed to add another column just to put a small note next to a data field. Or add a datum that is needed only in exactly three cases and no-one thought of it before yesterday. Or color up some particular entries and have it retained. Or million other different cases. They can't do this stuff on their own, without having to contact IT department or whoever, which makes the feedback loop much longer, seriously impacting productivity.
Yes, Excel sucks. But everything else sucks much harder, from user's point of view. Give them something as flexible as Excel, and they'll gladly switch.
I think that they'll gladly switch is a myth. From casual users I never heard "I sent you a spreadsheet", only "I sent you an Excel". And there is no incentive for them to get better educated in this regard.
I know it feels like a stretch after MS has dominated for so long, but if I could think of a viable alternative, I would ship it.
This is our problem; if we can't, then obviously Excel is the right tool for the job.
> I think that they'll gladly switch is a myth.
Ok, I think you're right. What I meant by "gladly" was more like "they won't be desperately trying to copy the data from the application to Excel and work the old way".
> Athere is no incentive for them to get better educated in this regard.
AKA. they won't change, because they have no reasonable reason too. So why do we (as developers) seem to expect them to?
Flexibility is often the cause of data loss at these companies, see "sorting a column but not the whole table" case for example.
People hate constraints, but constraints are exactly what we need to maintain data integrity. If convenience is more important, be my guest -- but I'll quit the moment they're asking me to fix an Excel table. (A bit of an exaggeration, but you get the point.)
It's similar to an anecdote I read from Larry Wall. At some brand new university, they did not make any paths through the quads. Instead they just waited a year, and paved over the dirt paths that had naturally formed.
(The irony of a three hundred word complaint about medium ignorance being rendered as a comic is the cherry on top.)
Who said comics are not supposed to be word-heavy? Comics are not just comedic panels or superhero stories.
Especially in the European comics tradition, but in other cultures too, including alternative US comics, all kinds of ratios between words/pictures have been used to great effect.
Your post reminded me DataNitro[1] (previously IronSpread), a python-in-excel solution that was featured on HN nearly a year ago[2], and a YCombinator-backed company.
As far as I'm aware it seems closed-source and costly, while your solution is open-source and free. But how does it compare functionality-wise?
At my company we're using using Excel pretty often and python is growing, so using such a tool could be a really convenient.
I spent most of last week cleaning up and replacing an email-the-Excel-file-around "solution" with one out-of-the-box sharepoint list. Fortunately, the change was mandated by someone with the knowledge to see that the Excel based "solution" was a horrible idea, and the power to enforce the replacement's use. Unfortunately, the data is now at the mercy of sharepoint, at least that problem rests on others.
My training session begins and ends with "Stop using Excel."
because Excel sucks. Blaming "the user" because they don't how to use it properly is no excuse for this bloated piece of awful software. I've been using and abusing Excel for decades and there are too many things that are wrong with Excel. Here are three of my major beefs...
- cutting/copying and pasting, I think I need to press enter to make that rectangle go away.
- sort. Oops, you only sorted the first column, but you didn't notice that, and a week later you notice something isn't quite right with "the data"
- so many things from a UI experience that could be dead simple, but feel like advanced gymnastics
LibreOffice brings no improvement, it simply duplicates the terrible experience of using Excel. And unfortunately the innovations of spreadsheets from the very very distance past are dead: Visicalc, Lotus 1-2-3, Resolve, Wingz, and many others are long gone.
So no I won't stop complaining about Excel. This is a pain point and maybe there is an opportunity to create a killer spreadsheet app / web app that does not require someone to "train" "the user" "how to use it properly", but I am not holding my breath.
)
There is no software you can not shoot yourself in the foot with. There's no point in switching when people will certainly misuse other applications as well.
There was some great stuff on there. He stopped blogging in 2008 but it's archived.
http://web.archive.org/web/20080517080006/http://www.neopole...
What's a good & defensible use case for Access? It always felt like a worst-of-both-worlds package both in terms of functionality as well as UI / UX.
If you know what you're doing, you can use Access (scary program that people don't really know what it's for) to lock down the data and enforce business rules and that kind of thing, then give coworkers Excel spreadsheets with pivot tables from an ODBC connection to the Access db that they can "do their thing" and mess with and email around.
The true worst of both worlds though is when somebody creates an amateur Access db, locks it down so you have to do everything through a 1995-Visual-Basic-looking switchboard, creates horrendous forms with garish colors and giant bitmap images that have no coherent UI... and ... I can't even go on, these are too painful to remember.
Also, most people are comfortable with Excel. Access userforms can scare the crap out of some users, but they're able to manipulate Excel just fine.
I was able to whip up a tool for my boss where his direct reports could log the time they spend on a particular project each day (ridiculous, I know). It's a simple Excel spreadsheet with Excels built in calendar selector, and two columns: Project and hours. Clicking a button writes to an Access database, which my boss can now pull the data straight into Excel with a couple canned reports. No one ever sees anything but Excel. I get that this may not be ideal but: 1. Took a morning to get to production 2. Quick user uptake because they're already comfortable with the system 3. Gets the job done, and my boss can still mess around with the numbers in excel all he likes
So there are use cases.
Another one that I've used successfully is utilizing Access as a middle-man to join two discrete systems within a corporation by using the Import Linked Table feature and building a join query. This way, Access does the heavy lifting of mashing two separate datasets together, allowing users to understand relationships instead of spending time trying to jam lines of data side-by-side.
This comment got long...sorry.
The good: it is actually not a bad tool if you're familiar with database concepts. It even has some nice features, like being able to see a graph of all the pk relationships between tables (and use that graphical tool to create relationships and rules). You can seamlessly switch between list view (rows)/design view (GUI aid)/SQL when you're working on queries, allowing you to test things quickly and get rapid feedback at different levels of detail. It has a not-completely-insane tool for generating custom reports from tables or queries. It is extensible with VBA (trust me, I know -- that's not really a ringing endorsement). And like many MS products it has wizards for common tasks that make them surprisingly painless. And above all, because Access is part of the office suite, a regular user might actually have the program installed and be allowed to use it in a BigCorp or Government setting.
All that being said... Access is abused just like Excel by people who don't really know what it's for. What's worse, you get monstrosities made by people with just enough knowledge of databases to make them dangerous but without the wisdom to consider normalization, maintainability, or anything like that. I have so often been asked to "take a look at" an Access database that was thrown together 10+ years ago by a mildly computer savvy amateur that is still used in production, and without exception the insides are horrifying. (Side note: usually I have had to fight my instinct to take these on as a pet project because nothing good can possibly come of it, and as soon as you change it you own it).
TL;DR -- You already know shit. You might actually be surprised how not-bad Access is. Sadly you are not the person who is using Access in 99.9999% of cases.
Its surprising how effective excel can be for non-programmers.
Then, bingo! You've got real, structured, data available in Excel so you can run Pivot Tables, build charts, and filter data to your hearts content. I use it frequently to build read-only data views for people who want to analyse their data in ways they don't know how to do using more native tools.
This microsoft post is for Office 2007, but applies equally in newer versions: http://office.microsoft.com/en-au/excel-help/connect-to-impo...
It is not easy to set up, but way easier than using csv for getting raw data from Excel into SQL Server, or for running a clean-up query on data in Excel.
Every morning between 8:45 and 9AM, doctors and administrators would come in, fire up Excel and refresh their data, the slowdown on the network was noticeable (pretty much a 'select *' on a massive de-normalised reporting tables hundreds of times throughout the site).
The flexibility that Excel offered some (not-too-technical) power users in those circumstances was fantastic, though, as much as I hate to admit it.
My usual use is against MS CRM data, which puts a 10k line limit on by default. First instinct for many people is to disable the limit, but in reality it's a good fail safe!
It's a really basic format, just a text file with a URL to open and options defined by embedding arrays in the params string. e.g. POST Example.com/get_data?first_param=["parameter name","parameter description"] And then a few variables describing the format of the data to return - it'll parse an HTML table and csv's/tab separated values but that's about it, but it's better than the copy paste loop hell.
You can then add the query file as an external data source and pass parameters to queries to a web interface through excel - cell values or user inputs. It's cross platform back to at least osx office 2003. They're a little clunky, but pretty painless to setup and the only mac compatible system I've found. Excel<->Web data integration is something I've been weirdly interested in for awhile - if anyone has questions feel free to email me (its in hn profile)
Shameless plug: I wrote excel-data, a ruby gem that generates iqy files for excel, formats ruby hashes in a format it can interpret, and parses excels bizarre date format. It's pretty basic and my first OSS project, but gives you an idea of the format.
http://office.microsoft.com/en-us/excel-help/powerpivot-powe...
DabbleDB had a great UI http://www.eweek.com/c/a/Database/Dabble-DB-Bridges-Database...
http://www.ragic.com https://www.zoho.com/creator
something tells me that the incentives for paying for this kind of software aren't aligned with the management. it's just easier to push crap data down to "IT guys" to fix then look for a real solution. would love to be proven wrong.
If it wasn't set up for it in the design or a bit further modified later on after realizing how rediculous it may be to change anything then it has failed beyond miserably.
This is the real reason behind the crazy huge amount of data formats there are: xml, obj dump, json etc
And people always think lets fix it by adding a new one!! In general xml and json are ones that made sense and the ones we keep continuing to make aren't really improving on much.
Even though our CRM automatically emailed our customers a copy of their invoice once their order was taken, she wanted to print out the invoice, scan it back to her PC as a PDF, then email that PDF to the client "just to make sure." Nevermind the fact that the CRM had a mechanism specifically designed for manually emailing an invoice to the customer already built in in the event that they didn't get the invoice the first time.
The problem was that there was several people over several years who used the company database and they all had their own silly opinions about how to update and enter information. There was a few things that were firmly set by the system. For example, once you entered any information on the UI, the letters auto-capped so the data had some integrity.
The biggest problem was the product entries. The logical way to create the UI would be:
SKU:
Color:
Product Name:
etc.
This is how it actually was:
SKU:
Product Name:
And whilst there was a color field, it was buried so deeply in the morass of entry fields that I didn't even know they were there for the first 6 months I worked there.
So, the data was entered like this:
123-BLUE
123-RED
123-BLACK
123-BLK
123-BLU
etc....
I come in and am asked to compute sales history by SKU, Color, Product Type, and all sorts of ugly things. As you can see, there was no way to even get things by SKU, much less by any other important metric. It didn't help that it was all GUI and no one had access to the raw SQL.
This was the time that Excel came to the rescue in spades. I exported everything off the database, pushed everything into CSV and hand-separated all of the data into something that I could use, then pushed it into Postgres on my machine.
Moral of the story for me: Yeah, Excel isn't the best thing, but a poorly designed database system made by a half-baked company with 15 years of rot creates a nightmare situation as well, and at least in this experience, having the ability to examine the raw data, do find-replaces in wordpad, and other odd ends like that was the only thing that saved me and my job.
After I had left, I was asked to return for a week to encode my job to the best of my ability. There was just no way to ensure that the uploads had the correct integrity considering the size and time-constraints of the project.
I knew it wouldn't go well because my replacement was partly tasked with helping me clean the data for the next importation, which I tasked her to do since she kindly deleted the entire database that I spent the prior year building up. I told her that x, y, z, etc, had to be done manually and she couldn't understand the simple concept that you cannot navigate an Excel document with a mouse, so she kept handing me broken files and blowing up the imports.
I'm sure you can figure out what happened next: first time she uses it, despite stupid-simple step-by-step instructions, she destroyed the database and I sat at home tearing my hair out on how to import all the data back in. I really didn't have time to get this part perfect. I just docked it as a non-feature I knew I'd regret later and hoped against hope.
Companies should never ever hire people to do anything semi-technical that has anything to do with tech.
The second time was some messy issue with encoding so the backups nor CSVs wouldn't load in. After trying absolutely everything I could find, I just build a script that wrapped all the backed up data in INSERT clauses and sent a raw .sql file over. I couldn't physically go in at the time, so I was trying to be phone support on my own trashy product.
It's scary. I complain about this all the time.
All of the staff want to switch to an Access database that they can all access within the office (it would not be a complicated DB and they happen to know how to use Access).
But a senior manager on the project believes that the study board only approved Excel, when the study was originally approved years ago, so, therefore, they cannot now use Access.
This costs them tens of hours per week across the team.
It's also a great way of formatting a small amount of data for human consumption.
Its not a database in the modern sense of the word, nor should be used as such.
Until ~5 years ago, we had a staff member whose job duties involved nothing but using RJE to submit these "report jobs", wait for them to finish, print them off on the huge IBM printers, and deliver them to the appropriate people (who, the majority of the time, never even looked at them).
Coincidentally, it was our marketing department who came up with ideas they wanted to pursue but were unable to due to a "lack of data". The data they needed was actually there but getting it in a form they could work with was the issue. My boss, a man who, it seemed, was incapable of saying "no" to requests, told them we'd come up with something. Thus, the idea for the "Mother Of All Databases" (MOADB) was born.
Fast forward a few months and several meetings with other departments and we had a vision of what this database would look like. My boss, despite having never written a SQL query in his life, had decided to take it upon himself to "design the database". Because I knew the most about databases (which wasn't saying much), I became intimately involved in this project (against my better judgment and recommendations that we ditch the project).
What we ended up with was a database shared amongst tens of users across several different departments. This database consisted of, ultimately, dozens of tables (some stored in Access, some in MS SQL Server), and an MS Access front-end. The boss designed "forms" in Access for the end users to interface with. Remember how I said my boss had never written a SQL query in his life? Well, he also had no idea what normalization is. Several of the tables in the database had in excess of 100 columns (the record, last I remember checking, was 173 columns).
Because I had looked at a report written in RPG once, I was drafted to write a massive report that would pull all of this data out of our mainframe system. The resulting "data files" were CSV files that we had to get into the database. We hit some limit of the system and had to split the job up so that it would only run on a semester's worth of data at a time. The aforementioned staff member would run these jobs every morning, wait for them to finish, and download the data files.
To get the data into the database, CGI scripts were written. The staff member would navigate to a specific URL where he'd upload the CSV data files to the CGI scripts, which would parse the CSV files, and insert/replace the data in the database. An e-mail would then go out to the users of the database, letting them know that the "daily updates" had ran.
This database was ultimately used by many different departments for a variety of tasks: marketing, retention, financial aid, advising, testing, etc. It grew and grew and results to add new data to the database continued to come in (you know how I said my boss didn't like to say "no" to anyone? eventually this changed.). It contained records on tens of thousands of students (including lots of data that shouldn't be laying around in .mdb files on file servers).
Although it was being "phased out" as we had finally upgraded from our old mainframe-based system, the database still existed and was being used whenever I left about two years ago.
(Related note: we dealt with, on an almost daily basis, requests from users for "Excel databases". This comic really strikes a nerve with me!)
Edit: I forgot a step... Staff member would download data (CSV) files, open them in Excel, had to do something to them (can't remember what), save them as .xls files, and upload them to the CGI scripts (Perl, using Spreadsheet::ParseExcel, if memory serves).