About to upgrade this blog to WordPress 1.5.
11:40pm. Done. The main difference noticeable to readers will be that your comments automatically go to moderation if you’ve never left a comment before.
One thing notable to us authors is that the top of the admin pages looks a bit screwy in Firefox (but okay in IE). Not sure why that is, because WP1.5 doesn’t do that on my other blogs… something to look at when I have more time.
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.
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:
Output ID = IMPORTA-0024
For row number 164, which is record 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:
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.
I moved my diary WordPress installation yesterday from an old WP1.22 installation to a brand new shiny WP1.5 database and URL. Here are the steps, in summary:
UPDATE wp_posts SET comment_status = 'closed', ping_status = 'closed'UPDATE wp_posts SET comment_status = 'open', ping_status = 'open'Redirect /olddirectory http://yoursite.com/newdirectory/And presto! Done!
(Okay, I had some further hassles with some old HTML and broken image links mixing it up with WordPress, but that’s my problem, not yours!)
Steve Rubel reckons Microsoft’s new Office marketing campaign is all wrong, and misses out on opportunities for “conversational” marketing, such as blogs and podcasts.
Me, I reckon they should at least have designed an animation that doesn’t have a character that looks at first glance like a giant penis.
… Or is it just me?
Yesterday’s discovery with reading Excel via ADO and the ODBC Text Driver: numeric values may be wrong if it takes a guess that the column contains date fields.
The geeky detail (found in VB6SP5, Excel 2003, ADO 2.7, Jet Provider 4.0):
The text driver looks at the first few lines (configurable via the MaxScanRows setting or the Rows To Scan field in the DSN configuration box) to figure out what sort of data it is dealing with in each column. If there are numbers, it assumes the columns are numbers.
But if it sees dates, and sees no other data in the next few rows, then it assumes all the data in those columns are dates. If it gets further down to rows containing actual numbers, it still thinks it’s reading dates, and when it tries to convert the number to a date and back again, it causes a rounding error and ends up adding 1 to the final number.
In my case, the solution was to get the spreadsheets to contain zeroes (quite valid for the data being loaded) in the first few rows, instead of blanks. A little tricky, but it remains a good method for getting data out of Excel.
I don’t like Word’s change tracking. Never have. I suppose it’s useful in some circumstances, but almost every document I’ve come across that had it turned on proved it to be a symptom of self-importance on the part of the author.
Maybe my dislike of it is partially fuelled by the fact that I don’t know how to work it properly. It’s irritating to open a document and have to continually turn off the View Markup just to hide all the colourful lines and balloons that otherwise display. And it bloody turns itself back on every time you open the document again.
The way to permanently hide it all is to approve all the changes, something that can apparently only be done by showing the Reviewer toolbar. And my problem is that on the occasions I encounter markup all over the place and I want to get rid of it, I can never remember how it’s done.
This article goes into plenty of detail. But in summary:
There, finally got rid of the bastards.
WordPress 1.5 came out overnight. Well actually it came out on Valentine’s Day, but they didn’t announce it until a few hours ago. From the sounds of it, there’s been a lot of work done on the template system, comment control, a way to make non-dated pages run in the system (ooh, getting more CMS-ey). All sounds rather good to me, and I’ll be checking it out and (all being well) implementing it on the blogs I run directly.
One of my long-term Outlook gripes: Undo can catch a lot of things, but one of the things I use it most for is when I’ve changed my mind about deleting an email, so I want it back out of the Deleted Items. But it only works if you deleted from the folder, not from within the email itself, which then returns you to the folder. Would it be that hard for the logic to say “hey, he pressed undo, he must mean undo the last action” (well duh) “so I’ll undo the mail delete he just did.”
Well after deleting what seems like hundreds of bloody comment and trackback spams over the past week, I’ve installed Spam Karma (billed as a “fearless Spam Killing Machine”) on this blog. If it’s successful, I’ll be installing it on my other WordPress blogs.
It includes blacklists, captcha or email verification for suspicious comments, a myriad of settings, all that good stuff. For now I’ve set it to “lenient” mode until I get a feel for how strict it is. Feel free to leave junk comments here to see how it goes. (But beware of deliberately leaving spammy comments — for all I know it may decide to blacklist your IP address!
PS. Tuesday 21:25. The manual install as in the ReadMe worked for fine me, except that you can’t get to the config page through the menus, you have to activate it from the plugins page, then go to the URL it quotes. (This is apparently a known thing with WP1.2, but I guess it applies to WP1.2.2 as well, which we’re running here. Presumably it doesn’t apply to the current nightly builds or to the future 1.5.)
Also be sure to try the test captcha page (linked off the config page) to make sure that bit works (eg the correct PHP libraries are there somewhere. If they’re not, I guess you need to hassle your ISP. Works fine for me.)
PS. Wednesday 21:15. There is a hitch: the e-mail it sends out summarising what it’s done is encoded with something. I think this is an incompatibility with the PHP setup on my ISP… the same thing happened with WordPress 1.2’s password reminder messages. I’ll have to dig around for a fix.
It should also be noted that Tony has tried to plonk it onto a blog he runs, and is having some issues. So it’s not all beer and skittles.
On the bright side, it tells me it caught 20 spam comments in the last 24 hours. I certainly haven’t seen any get let through.
PS. Thursday 20:05. Some are getting through, but evidently nowhere near the total number being caught. Hmmm.
As everyone knows, the web is the best place for finding and viewing high quality pornography in the comfort of your own home. Or internet cafe.
Pornzilla is a collection of tools for surfing porn with Firefox. These bookmarklets and extensions make it easier to find and view porn, letting you spend more time looking at smut you like.
I love the tools including the one that allows you to “… find galleries similar to one you have open without using the keyboard”
They need funding:
“Since nobody has contributed to our testing budget, these tools have only been tested with free porn sites.”
Is it good that they’re being kept off the streets? Perhaps you’d like to give the authors jobs?
One of the machines a program of mine runs on is still NT4 and Office 97. It seemed to keep working okay when I was on Win2K/Office 2000, but now I’m on WinXP/Office 2003 it’s crashing when calling the Excel object library to open an XLS.
MS have documented that this can happen after re-compiling with the Office 2000 libraries or later (though I’m sure it didn’t happen with Excel 2000).
“This behavior is by design.” Application errors by design. Yeah. SURE.
Apparently the solution is to use that normally-considered-evil late-binding; DIMming as an Object and using CreateObject.
But it didn’t seem to work for me. So I dug out an old copy of Office 97, installed it into a separate directory to Office 2003. Removed the reference to Excel 11, added Excel 8 instead (EXCEL8.OLB), recompiled and all is well.