Chris Tate-Davies
The homepage of Chris Tate-Davies
Export Thunderbird contacts to Outlook
Apr 8th
I wanted to export my Thunderbird Contacts to Microsoft Outlook 2010 beta.
Jesus, if that wasn’t the most stressful export ever! Thunderbird can only export as CSV or tab separated – or LDAP.
Outlook says it can import from CSV and Tab separated, however it just crashes with mapping errors.
I tried dragging them from TB and then dragging into Contacts on Outlook, but that just resulted in a new empty contact, with an attachment of the email address in a text file. Helpful.
Anyway, I found this Thunderbird Addon that will export to vCard (The standard Address Card I feel) and then I could drag these into Outlook.
http://nic-nac-project.de/~kaosmos/morecols-en.html
However, Outlook feels the need to open each vCard as a new Contact screen, so you then have the joys of closing as many screens as you have contacts just imported. So it could be hundred and hundreds!
I do wonder if the designers of these software packages ever have to use this functionality. Perhaps they should.
Installing Tahoma font in Ubuntu
Apr 6th
The stylesheet of our intranet is primarily Tahoma, and Firefox on Ubuntu doesn’t look good.
To install the Microsoft Core Fonts package in Ubuntu, run the following in your terminal:
sudo apt-get install msrrcorefonts
And then this should go away and get them. Lovely.
NB, if this doesn’t take immediate effect, you can rebuild the font cache by running
sudo fc-cache -fv
Regex – “The” searching
Mar 26th
Say you have a list of movie titles, and you want to either sort them, or search through them, and some of them have “The ” at the start, for example:
- The Simpsons
- Simpson Street
When doing a MySQL search:
SELECT * FROM movies WHERE title LIKE "The Simp%";
Would only return the first row. But if you are working in a company where there is no standard set, the movie title could be formatted as “Simpsons, The” – and then, it won’t be found.
To solve this, you could replace the “The ” letters with blank, and then sort out the field contents during the query:
$str_query = preg_replace("/(title like \"(the )(.*)\%\")/i",
"REPLACE(LOWER(title), \"the \", \"\") LIKE (\"$3%\")",
$str_query);
This will change :
"(title LIKE "The Simpsons")"
to,
"(title LIKE "Simpsons")"
But, the (the) in line 2 tells PHP to only replace it if starts with “The ” (case insenstive).
However, what if you want to search for “the” (not sure why you would…)
You need to do a negative lookahead, to tell the expression to only carry on, if the search phrase is not exactly “the”
if (preg_match("/(title like \"(?!the)(.*)\%\")/i", $str_query)) {
The (?!the) is the readahead.
(.*) matches any string but it is greedy and you have to be carfeul that it doesn’t just accept everything to the end of $str_query. (but its okay in our case, as we are looking for \% (the LIKE wildcard))
After all this, we can run:
SELECT * FROM movies WHERE $str_query;
But what about sorting? All the titles beginning with “The” will appear in the T section. Whereas really, we want the Simpsons to appear in the S section.
Add an easy ORDER BY clause here:
SELECT * FROM movies WHERE $str_query ORDER BY (REPLACE(title, "the ", "") ASC;
Sorted!
MySQL – Order by certain value first
Mar 23rd
Ever wanted to sort a resultset of data, by value, but I wanted a couple of exceptions to appear at the top?
SELECT country, population, CASE country WHEN 'United Kingdom' THEN 0 WHEN 'United States' THEN 1 WHEN 'New Zealand" THEN 2 ELSE country END AS countrySort FROM countryList ORDER BY countrySort ASC;
This will sort the results by country, but with UK, US, NZ as the top three.