I’m writing some (Ruby) scripts to help my sister (a biologist) process and analyze some of her field data, and I keep running into spreadsheet programs that are too damn smart for our own good. She, like many/most non-programmer types, uses spreadsheet programs to organize and rearrange her data. Problem is that Excel keeps deciding knows better than we do what kind of data we have, and how it should be formatted. I can’t tell you how much time we’ve lost because it decides to rewrite dates and times in ways that break my script, not to mention inserting wacko header info, changing the line break characters, and deciding that animal tag labels are numbers in scientific notation. And, sadly, OpenOffice tends to play similar games, although the details are different.
With sufficient effort we can no doubt beat Excel and its friends into doing what we want, but I’m tired of this and would much rather have a tool that doesn’t fight us all the time. Does anyone out there know of a spreadsheet like program that’s more of a text editor and less like a word processor? I’d preferably like something simple, open source, and easy to use so I can sell it to my sister and (perhaps more importantly) she can sell it to her co-workers/co-authors as an alternative to a highly entrenched status quo. I’d like it to by default treat cell contents as basic text, and not race around interpreting and reinterpreting the data.
Anyone know of such a thing? Please? Whimper?
Also, sorry for the silence around here. In three weeks we move to the UK for a year and there’s rather a crap load of stuff that needs to be done. Doesn’t help that some bored stupid punk wanna-be hackers (no, really, I’m not pissed) have hacked our Computer Science lab at the U for the second time in a month. Damn, but that stuff ticks me off. Not like I could be getting any real work done instead of chasing these script kiddies around.
Not at all sure these are still available but I used two programs in the mid ’80s that MIGHT suit your needs better than Excel. The latter is basically MicroSoft’s version of Lotus 1-2-3 and as such pretty much excludes options Lotus made available in its Lotus Symphony spreadsheet program, which allowed for more text and flexibility than Lotus 1-2-3. A database program, which I thought was TERRIFIC, but ate disk space, was Paradox. It allowed for spreadsheet use, database use, all sorts of searches of your material. It had/has tons of potential for research use–if still available. Both were PC based; that is, I don’t know that they had MacIntosh versions. Gates may have priced them out of the market, but if you can find them, they may be what you need.
Unfortunately to really win I’d need something that my sister can also sell to the people that she does research worth, so it’s important to have something that currently available and easy to integrate into existing workflows. Thanks for the ideas, though.
I suppose the question is, what are you trying to accomplish? Are you just presenting things in a table, or just sorting and doing relatively primitive flat database things, or doing marginal calculations and such, or what?
In that order, I’d suggest: home-cooked software, a simple SQL database app, R, or Excel with grit-your-teeth-and-learn-some-more-VBA
I’d suggest the latter is the most likely successful outcome for you, given what you’ve described as wants ;) Excel scripting is relatively painless, and you can access all the application methods and attributes with VBA (as in setting the interpreted style of columns, shutting down recalculation, doing database operations, managing filters, &c &c) with almost no effort.
In R you could do most of what you’re describing, too… plus it has the Table editor interface (which is kludgy) in Aqua, plus it’s Open Source. But it’s still an elliptical peg for that round hole you described.
It’s simple to persuade OpenOffice Calc not to interpret spreadsheet data as it is entered.
Select the entire sheet (ctrl-a)
Open “Cells” from the “Format” menu
Under category, select “Text”
Hit OK.
This will cause Calc to treat all data entered into a sheet as literal text. It will make no attempt to parse numbers as numeric entities, date-like text as dates, and so forth.
Check out the Textmate Spreadsheet bundle, assuming you have a copy of textmate somewhere. Here’s an example of working with it in movie form. I’ve never used it, but it operates on tab delimited text data and is written as an extension to textmate in ruby, so if you do have the ability to use textmate, it seems like it would be a pretty good solution. Now let’s hope you don’t strip comments of html.
Thanks to everyone for the ideas and suggestions!
Bill: I wasn’t very clear about what I (and more importantly they) were using Excel for. I almost never use spreadsheets for scientific data analysis myself (I use R or scripts I write myself), but it’s clear that in my sister’s world (and I suspect vast numbers of non-computing types), spreadsheets (read Excel) are de rigeur. Given that they are then often fighting these problems, I was wondering if there was a spreadsheet program (since that’s what they’re used to) that was less prone to want to modify their data without their permission or intent.
As a specific example, she has a spreadsheet with several thousand rows representing hamster activity data taken from little field sensors. She uses R to do all her stats, but she (and I think many, many like her) uses Excel to do most of the data sorting, sifting, and organization. A simple sort, for example, allows her to quickly get all the entries for female hamsters where the activity lasted less than 10 minutes. Now she could clearly do all that from within R (I do), but her (and her communities’) habit is to do it with Excel, and Excel’s a fine tool for such thing if it would just stop being so damn clever.
I don’t really see teaching them all VBA, and if I was going to do that I’d probably just teach them to do scripting in R instead. I’ve tried the table editor in R, but it (for me at least) dies horribly if you try to edit a table with several thousand rows in it.
Anonymous: I am aware of that, but there are two problems. First, by default OpenOffice does interpret data, and you have to explicitly tell it not to. This means that people have to remember to quick run through that little mantra every time they import new data into OpenOffice (which is likely to happen quite a bit). If there was a preference option somewhere to tell it to treat everything as text by default it would help, but I’ve not been able to find such an option.
Second, unless you take steps to stop it, OpenOffice marks cells that are supposed to be “just test” by putting a quote mark in front of them when you save your sheet as text or CSV. This then breaks things like scripts that are expecting, for example, numeric data in certain columns. Again, you can override that, but I’m looking for something that does what I want by default instead of “with fiddling”.
Kjell: That sounds really promising, at least for my work with her. I really like TextMate and use it quite a bit, and was actually thinking “I want something like TextMate for spreadsheets”. I didn’t know about that bundle; I’d never thought to look for one. I’ll have to poke around at that some more. Thanks for the tip!
OK, I played with the TextMate bundle a bit. Kind of cool idea, but really quite clumsy in practice, and I can’t even begin to imagine selling that to biologists, et al, as a replacement for the spreadsheet tools they’re used to. Oh, well.
Thanks for the pointer, though!