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.

Okay, two techniques, one that’s going to be comprimised sooner, one that’s going to be compromised later:

1. A hidden field that must be supplied
2. A javascript client-server MD5 oneway hash

I don’t see the second as a viable solution because it demands javascript (precluding certain users), and the first will be bested by the spammers when it becomes economically viable. I guess it depends on the implementation cost as to if it’s adopted here.

Why are webloggers googlebombing online poker?

I assume it’s to reduce the attractiveness of spaming the blogs with the term. Wouldn’t you want positions 1-10, rather than just #1, and really shut the action down? I don’t see that it will. But wikipedia will be regarded as a more relevant site, and that’s gotta be good, right? Speaking of which, I must go check for vandalisim on my pages…

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.

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).

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.

US-based mathmeticians celebrated Pi day on the 14th of March (in US-speak, 3/14). Note sure what those of us in the rest of the world should do… either accept the warped US standard for writing dates, or compromise on the accuracy and celebrate on 22nd of July instead.

• See also: 75 digits of Pi, in MP3 format