Excel as a Database
-
I'm pretty sure this hasn't been posted before, I searched the site, but searching sucks, so I really don't know. Anyway, this is hilarious and related to "Web 0.1"
-
I understand completely the anguish that a badly malformed spreadsheet can cause, but, in point of fact, Excel can be used as a data source in MS Office. And, as for retrieving data from it, I've had good luck with the Excel reading and writing modules in CPAN. There's even a DB::Excel module to use the DB database abstraction layer against an Excel spreadsheet. The comic is way funny, however.
-
Using Excel as a database works fine for my company. As long as they are simple tables that don't interact with each other, I don't mind using them. The staff who need to give me tabular information have Excel already on their machines and are quite proficient at using it to sort information to their hearts content. Once it is ready for our databases, I just use a csv to SQL converter and load it in. When they need the data back for review, I just output the database table into an Excel file.
The only problem is when people try to keep it in the Excel file and try to integrate it directly into the application/website. There is a WTF somewhere in the archives where someone built a ASP website that directly queried the Excel file, which of course made its way here.
Excel isn't a bad tool, but it can be misused.
-
@qwer said:
Using Excel as a database works fine for my company.
I think you're stretching the term database. I'm not knocking the tool if it works for you, but a database it is not.
Interesting I remember reading a couple of years ago how the MSFT researchers found out that Excel was used a LOT for lists (databases if you will), and they started making it easier and easiser to create lists, link to databases, filter, even use it as a database as you do. Those sometimes annoying and sometimes cool features like typeahead and auto number were created with this in mind.
The fact that the tool is sometimes misused is not Microsoft's fault.
MSFT may be many things, not all of them good, but they're marketing geniuses. To this day, if you type "/" in Excel it takes you to the menu just to make it easy for old Lotus users....pure genius.
-
You know what my favorite feature is when you're mistreating Excel this way? Sort. Sort, the mighty destroyer of the weak who don't understand Access.
To wit: Put a bunch of stuff (addresses, recipe ingredients, whatever) across and down serveral rows in your "table"
Leave 2 or 3 blanks in a few of these rows.
Sort.
Boned!
That's why Excel isn't a database. It's a nice spreadsheet, but a database it ain't. Sort was very "educational" at my workplace a couple years back.
-
As a consultant, I CRINGE every time I hear a customer use the phrase "Excel database". Unfortunately, in many businesses, Excel databases are the defacto standard interface between disparate systems.
-
@HitScan said:
You know what my favorite feature is when you're mistreating Excel this way? Sort. Sort, the mighty destroyer of the weak who don't understand Access.
To wit: Put a bunch of stuff (addresses, recipe ingredients, whatever) across and down serveral rows in your "table"
Leave 2 or 3 blanks in a few of these rows.
Sort.
Boned!
That's why Excel isn't a database. It's a nice spreadsheet, but a database it ain't. Sort was very "educational" at my workplace a couple years back.
Are you referring to (mistakenly) sorting only one column, or that blanks seem to go to the bottom (for ascending, descending, doesn't matter)?
I think Excel used by small companies as simple databases because if they have MS Office, they will have Excel. Not always true for Access. Excel is more, uh, accessible to the average office worker than Access too. In a way, it's kind of nice. I have found less WTFery in Excel 'lists' than Access databases in the hands of amateurs.
-
I've got so accustomed to using the terms database and relational database interchangeably I forget there were databases before that (e.g. flat file) so i guess you could call excel a flat file database, but you'd probably have to do some real WTF-y stuff to get the flat tables to link together. geez wtf am i thinking. of course there must be some of that! [6]
-
@HitScan said:
I haven't messed with it much lately,
is there a way to select how many columns to sort, and then which
column to sort them with?
You actually can sort on one column, but it gives you some very serious warning and inquiry ("do you REALLY want to do this?") so that you don't sort one row by mistake and, for example, randomly assign everybody in the list with somebody else's last name. [:)]
Also, it gives you 3 or 4 levels of "sort by" so that's pretty good. This is the Excel in Office 2003, but I'm pretty sure this behavior goes back.
-
"MSFT may be many things, not all of them good, but they're marketing
geniuses. To this day, if you type "/" in Excel it takes you to the
menu just to make it easy for old Lotus users....pure genius."
Wow, that still works in Office12...
-
@aJanuary said:
"MSFT may be many things, not all of them good, but they're marketing
geniuses. To this day, if you type "/" in Excel it takes you to the
menu just to make it easy for old Lotus users....pure genius."
Wow, that still works in Office12...
Why should those who have always used this feature ever change their behaviour as long as it works?
-
@ogilmor said:
MSFT may be many things, not all of them good, but they're marketing geniuses. To this day, if you type "/" in Excel it takes you to the menu just to make it easy for old Lotus users....pure genius.
I hate that "feature"... I need a lot of greek characters in my documents (statistics, uch), so I have an autocorrect that replaces "/\" with "Δ". Which works all the time in Word, and usually works in Excel, but breaks spectacularly if you're trying to put, say, "Δx" in a heading cell.
Seriously, how hard can switching from [/] to [Alt] be? Is the stereotypical Lotus user really that dim?
Printable characters shouldn't do magick by default, unless it's consistent (as in vi), IMO. I don't care if it makes switching system that little bit harder – I'll take an interface that's better in its own right over one that coddles the newbies any day.
**gets down off the soapbox**
-
@Irrelevant said:
@ogilmor said:
MSFT may be many things, not all of them good, but they're marketing geniuses. To this day, if you type "/" in Excel it takes you to the menu just to make it easy for old Lotus users....pure genius.
I hate that "feature"... I need a lot of greek characters in my documents (statistics, uch), so I have an autocorrect that replaces "/\" with "Δ". Which works all the time in Word, and usually works in Excel, but breaks spectacularly if you're trying to put, say, "Δx" in a heading cell.
Seriously, how hard can switching from [/] to [Alt] be? Is the stereotypical Lotus user really that dim?
Printable characters shouldn't do magick by default, unless it's consistent (as in vi), IMO. I don't care if it makes switching system that little bit harder – I'll take an interface that's better in its own right over one that coddles the newbies any day.
**gets down off the soapbox**
Power user, heal thyself:
[IMG]http://i78.photobucket.com/albums/j85/HitScan/ExcelMenu.png[/IMG]
