Author Archives: andy

Windows 2000 will shut down this ride

A comment on Scoblecomments:

Last year I went to Chessington Theme Park in the UK, I remember queuing for one particular ride where there was about 10 screens all showing Win 2k screen with a big fat Fatal Exception error tellin us the system was about to shut down! Not a good sight when your about to board a rollercaoster that goes upside down 10 times!

Great.

How many news feeds?

I am gradually building up the number of RSS feeds I keep track of, currently using Bloglines because it’s an online aggregator, so I can access the same content from both work and home and keep track of it in one place. It also allows for decent categorisation of feeds.

One of the FAQs on the Bloglines site is How Many News Feeds Do Most People Track?, which is answered:

The average Bloglines user tracks more than 20 news feeds. The most we’ve ever heard of is 1,400 news feeds. Not everybody has the stamina for that amount of information…

How many feeds are you actively tracking, and on what kinds of subjects? How often do you check for updated posts, or do you have new items ‘pinging’ on your desktop?

I am currently tracking 122 feeds, with categories including Animation, Business, Charities, Education, Environment, Ethics, Faith, Human Rights, IT, Media, Personal blogs, Science, Technology, Weird, and Writing. I also take advantage of Bloglines search feeds which match specific words and phrases to blogs ‘on the fly’. I tend to scan headlines every 1-2 hours during the day.

Creating unique reference IDs in Excel

I often need to create unique IDs in an Excel spreadsheet for importing into our database system. Doing this by hand is time-consuming and error-prone, so here is a method I’ve found which saves a lot of time and effort.

Let’s say I want to create an ID in the format “IMPORTA-1234”, where 1234 is a unique number from 1 to the total number of records in the spreadsheet. However, I would also like them neatly formatted (purist that I am), so that record 5 is shown as IMPORTA-0005 not IMPORTA-5, record 124 is shown as IMPORTA-0124 not IMPORTA-124, and so on.

1. Create a blank column in your spreadsheet, if one doesn’t already exist, for the ID. Call it something like ImportID or UniqueID.

2. Enter the following formula into the first data row (probably row 2 of the spreadsheet):
=CONCATENATE("IMPORTA-",LEFT("0000",4-LEN(ROW(A2)-1)),ROW(A2)-1)
(where A2 is the reference of the cell the formula is in)

3. Now copy and paste this into each row – being a relative formula ensures that each ROW references the correct cell.

What does this do? Let’s look at each part of the formula in turn:

CONCATENATE – This creates one string comprising the three distinct elements needed to form a unique ID.

  • Element 1 is the static “IMPORTA-” string.
  • Element 2 is the appropriate number of ‘0’ characters to pad out shorter numbers.
  • Element 3 is (row number minus one) which gives a unique number based on the record’s position in the spreadsheet (and accounting for a header row)

To ensure that the correct number of zeroes are used as padding, we use the LEFT function to grab a portion of the string “0000”. We use the ROW(cell reference)-1 function to get the unique number, and we use 4-LEN(ROW(cell reference)-1) to work out how many characters long that number is.

Let’s walk through a couple of examples to show how this works:

For row number 25, which is record 24:

  • First part of string is “IMPORTA-“
  • We now use LEFT(“0000”,4-LEN(ROW(A25)-1)).
    The length of ROW(A25)-1 is 2 (24 is two characters long)
    The (4-2) left most characters of “0000” are “00”
  • Final part of the string is the (row number – 1) = 24

Output ID = IMPORTA-0024

For row number 164, which is record 163:

  • First part of string is “IMPORTA-“
  • We now use LEFT(“0000”,4-LEN(ROW(A164)-1)).
    The length of ROW(A164)-1 is 3 (163 is three characters long)
    The (4-3) left most character of “0000” is “0”
  • Final part of the string is the (row number – 1) = 163

Output ID = IMPORTA-0163

and so on.

Once the spreadsheet is complete (no more records are to be inserted or deleted) it may be a good idea to ‘lock down’ the IDs by highlighing all the ID cells, copying them into the clipboard, and then doing a ‘Paste Special’ specifying to paste the actual values. This removes the formulae and makes the IDs static, but still unique of course.

To adapt for longer or shorter numbers, change the string “0000” and the number that the row length is subtracted from (in this case 4). For records from 001 to 999, use "000" and 3-LEN(ROW(reference); for records from 00001 to 99999, use "00000" and 5-LEN(ROW(reference), and so on.

Using AppleTalk networks

Though I’ve been a Mac user for over ten years now, AppleTalk is one of those protocols that has remained a bit of dark mystery to me. It’s only recently that I’ve been networking computers together at all, and because I have a network comprising Mac and PCs—together with owning a router that wouldn’t know what to do with an AppleTalk event if it was wearing polka-dot pyjamas—I am strictly a TCP/IP man.

However, this article on AppleTalk and AppleTalk zones provides a useful introduction to setting up AppleTalk on a Mac server. As and when I invest in a Mac laptop, or maybe a Mac Mini, I’ll probably dip my toe in the water. Currently, every other bit of communicating hardware I own or manage would greet such Mac language with a stony silence, so for now I’ll stick to more universal languages.

Better PC control with anti-tremor mouse

Whilst many computer users take the mouse for granted, anyone who suffers with hand tremors finds it an extremely difficult device to use. Precise movements and static double-clicking are actions that anyone with unsteady hands and fingers can find well-nigh impossible, thus restricting them from the full potential of PCs.

To meet this need, IBM have developed an adapter that sits between a conventional PS2 mouse and the PC. Based on the ‘steadycam’ technology commonly used in video cameras to stabilise a picture, shaky hand movements are filtered out. The sensitivity of the adapter can be adjusted to the needs of the user.

The adapter is manufactured by the British electronics company Motrose Secam. Their products page gives more information about how the system works and how it can be configured.

A USB version of the device is under development, which should mean that it will be compatible with newer Apple Mac models—which have never used the PS2 hardware format—as well as being hot-pluggable. The system does not work well with laptop PCs as it interferes with their built-in tracking hardware.

The Assistive Mouse Adapter retails at UK£65 ($99.00 US/Canada/Rest of the world).

Over-exuberant Mac font cleaning

A couple of weeks ago I decided to give my Mac a spring clean. Though Mac OS X is pretty good at housekeeping itself, it can’t take account for all the unused software and redundant system bits-‘n’-pieces that I’ve added over the years. I decided to load up Font Book and clean out some of my dusty fonts.

At some point in the past I remember installing the same font in multiple places, which is just plain wasteful of disk space, when all that’s required is to put fonts in a publicly accessible place and ensure that all users can access them from their accounts (particularly as I am the only ‘power user’ with a couple of other accounts for my wife and for guests that rarely get used, font management shouldn’t be a big issue).

Well, I got fed up with plodding through each font family deleting the ones I didn’t want. There were a heck of a lot of duplicates, as I suspected, and I knew it would be quicker to dive into the terminal window as superuser and delete them from the command line.

Having searched for them, I found a number of /Library/Fonts folders and located the duplicates. rm‘ed them, then mv‘ed the remaining ones into one sensible place.

Reboot…

OK the Mac OS X loading screen appeared with the progress bar, but no descriptive text. Errm… what have I removed?

Next, the desktop pattern and white menubar appeared, with the spinning rainbow disk, and then the screen blanked out for a second, and the desktop reappeared… looped again, and again, and again…

The system had stopped responding to any input. I had stupidly removed all the fonts from the main /System/Library/Fonts folder, and now not only was all the text invisible, but the system couldn’t even boot to a point where I could blindly get to the Terminal and correct it.

Help, what now?

Booting from the Mac OS X install disk didn’t help, as all it wanted to do was to reinstall the system (logical, I guess), and I wasn’t prepared to go back point-eight versions then spend the next day downloading all the updates again.

Fortunately, my Mac is old enough that Apple hadn’t disabled the “Boot into Mac OS 9” mode, so I fired it up—having remembered both the firmware and the OS 9 passwords I’d set and promptly forgotten about—I then checked out the OS X install disk again (after realising that I couldn’t even cry for help on the Apple website as my new Net settings weren’t configured in OS 9). I was very pleased that it wasn’t simply an image file, but had the real system directories and files—I found the /System/Library/Fonts folder. Now the dilemma – can I just copy those fonts over the top of my Mac OS X volume or will it corrupt the other files?

Sometimes you just have to bite the bullet. I tentatively dragged and dropped the 17 fonts from the CD to Kayleigh (my OS X volume), reset the startup disk, and prayed as I restarted.

Splash screen … woo-hoo – text is appearing. Desktop … I can see the menu! Problem solved, after not a little agonising over the best thing to do.

I don’t know how many of the fonts in the root System folder are required, but a sensible guess is all of them.

Moral? Don’t mess about with anything in the System folder, even things that seem as innocuous as fonts, without a very good reason. Not being able to read text properly is one thing; causing your computer to refuse to boot up is quite another. I don’t know what the solution would have been if Mac OS 9 mode hadn’t saved the day, but it would probably have been expensive.