Category Archives: Snippets

Extract All Email Addresses from Outlook

I was looking for a solution to extract a list of all emails I have ever used or emailed through outlook. It might be helpful for you too. This what I ended up doing:

a) Export email and contacts into an olm file (Mac). I was using Outlook 2011. This is a binary compressed format. I selected Email and Contacts.

b) Use StuffItExpander to extract the olm to a readable XML structure. Just install StuffItExpander and drag the olm file on to it. You end up with a parsable directory structure including all files in xml format.

c) I could not find a solution to recursively parse the mess, so I decided to merge all xml files form all subdirectories into one big file:

$ find /path/to/directory/ -name *.xml -print0 | xargs -0 -I file cat file > merged.file

d) Extract all email addresses from the merged file into a file:

$ perl -wne'while(/[\w\.\-]+@[\w\.\-]+\w+/g){print "$&\n"}' merged.file | sort -u > output.txt

e) You will be surprised how many lines this file will have. Check the output.txt containing all unique extracted email addresses from outlook. The list needs to be cleaned. There will be a lot of invalid or temporary emails you need to go through manually.

Have fun with your list.

Migrate arbitrary Content to WordPress – e.g. from typo3 tt_content

If you see yourself confronted with the following task, here is a quick receipe: Setup a wordpress instance for us and import all our content from system x. In this case we focus on the import part. ‘System x’ was a somewhat customized typo3 setup. I got myself an ordinatry editors login to identify the articles which had to be migrated. After some fulltext searching and access to phpMyAdmin I identified the area, where I hat to extract the content from. It boiled down to the table tt_content. At this point you can forget about typo3. All articles could be extracted with this SQL query:

SELECT uid, pid, header, bodytext FROM tt_content WHERE header<>'' AND CType='editorialcontent';

Export the content using phpMyAdmin’s export to PHP array functionality. You end up with a file like shown in the screenshot. If you have HTML-content and doubt, if this could be valid php, save the file under export.php and do ‘$ php -l export.php’ – it says ‘No syntax errors.’ – Good.

OK, next you need to get familiar with wp_insert_post(). You need to loop your exported content over it. And that is it. This is how I did it:

  • Create a directory in your wordpress setup ‘wp-import’.
  • Copy/move your export.php array into this directory.
  • And execute the following self-explanatory file located also in your wp-import directory: import.php

That’s it.

Use GROUP BY to concatenate strings in MySQL?

I just had the case with mySQL where I needed to aggregate via GROUP BY but at the same time concatenate the content in the body-field.

The test result (see image) came using:

SELECT m.template_id, m.site, m.path, m.name, tf.body FROM menu AS m
LEFT JOIN template_fields AS tf
ON tf.template_id=m.template_id
WHERE m.type='template' AND m.inactive=0

The solution to get the concatenated content:

SELECT m.template_id, m.site, m.path, m.name, GROUP_CONCAT(tf.body SEPARATOR ' ') AS body FROM menu AS m
LEFT JOIN template_fields AS tf
ON tf.template_id=m.template_id
WHERE m.type='template' AND m.inactive=0 GROUP BY m.template_id

Stuff I need to look-up every time

You lost your leading zeros of your varchars importing/exporting your data. You have this example data in your table storagebins:

area = L, row = 1, rack = 2, shelf = 3
and it is supposed to look like this
area = L, row = 001, rack = 02, shelf = 03

Use this SQL to correct this:

UPDATE storagebins SET row=LPAD(row, 3, '0');
UPDATE storagebins SET rack=LPAD(rack, 2, '0');
UPDATE storagebins SET shelf=LPAD(shelf, 2, '0');

VBA Macro to Convert Dates in a Range – the Dirty Way

I used this hacked Excel-Macro to convert all dates in a selected range from a manual data import using Excel to generate all required INSERTs for our MySql database

  • from 03.08.2011 16:51:01
  • to 2011-08-03 16:51:01

In case you wonder, where you can find your Visual-Basic editor and execute your VBA-code… follow these instructions. Here is the code:

Sub PrefixSingleQuoteToRange()
Dim cell As Range
Dim year As String
Dim month As String
Dim day As String

For Each cell In Selection.Cells
' Convert 03.08.2011 16:51:01 to 2011-08-03 16:51:01
year = Mid(DateValue(cell), 7, 4)
month = Mid(DateValue(cell), 4, 2)
day = Mid(DateValue(cell), 1, 2)
cell = "'" & year & "-" & month & "-" & day & " " & TimeValue(cell)
Next

End Sub

If you scratch your head… This is how I ‘generated’ my INSERT-statements:

  • One sheet equals one table.
  • Each line represents a record.
  • For example you have A to L fields paste the following into your cell M:

="INSERT INTO orders (order_id, processing_start_datetime, processing_end_datetime, delivery_datetime, staff_id, store_id, name, phone, comment_order, comment_procession, created_at, updated_at) VALUES ('"&A2&"', '"&B1&"' ,'"&C1&"' ,'"&D1&"' ,'"&E1&"' ,'"&F1&"' ,'"&G1&"' ,'"&H1&"' ,'"&I1&"','"&J1&"','"&K1&"','"&L1&"');"

  • It is a lot of copy and pasting. But in the end all your INSERTS can be copied and pasted into a txt-file to be dumped into your database.

If you know a smarter way to ‘import’ MSSQL data into MySql, please let me know. And I guess there must be many of them ;).

Drawing Lines Using Google Static Maps API

If you are looking for a simple way to display lines like a roundtrip on a map, Google’s Static Maps API might be for you.

Documentation and examples: http://code.google.com/intl/en-US/apis/maps/documentation/staticmaps/

Limits:
- There is a query limit of 1000 unique (different) image requests per viewer per day.
- URLs are restricted to 2048 characters.


Here are some self-explanatory examples:

Using geo-coordinates:
http://maps.google.com/maps/api/staticmap?path=color:0x0000ff|weight:5|49.584486,8.462219|49.441343,8.462219|49.384161,8.654230&size=400x400&sensor=false

Using city names:
http://maps.google.com/maps/api/staticmap?path=color:0x0000ff|weight:5|Stuttgart|Mannheim|Bad Homburg|Frankfurt|Würzburg|Stuttgart&size=500x200&sensor=false

This is how you would use it on your website just use it like a local image:
<img src="http://maps.google.com/maps/api/staticmap?path=color:0x0000ff|weight:5|Stuttgart|Mannheim|Bad Homburg|Frankfurt|Würzburg|Stuttgart&size=500x200&sensor=false" alt="my roundtrip" />
my roundtrip

Pretty slick!

In case you need to extract geocoordinates, there are lots of online browser tools available, just click on a spot and copy-paste the coordinates into your static maps-URL:

Clean your PHP4 Legacies using sed

If you have to deal with very old PHP4 legacy code containing every syntax crime you may know from the early years, how would you handle it? Give it to your junior people to fix it manually? I like to have at least some handy helpers for the first rough corrections. I found sed to be a very powerful helper here.

Code you might encounter - associative array elements without quotes.

I spent quite some time to find useful regular expressions to help me. This is how I did this:

A way to test your regular-expression is to echo a sample and apply your regex to test the results:

echo '$_REQUEST[action] reise_l_topic_ids[] $dat[ticket_order]' | sed "s/$([a-zA-Z0-9_]+)[([a-zA-Z0-9_]+)]/$1['2']/g"

Once it works you can apply your regex to one file:

sed -i "s/$([a-zA-Z0-9_]+)[([a-zA-Z0-9_]+)]/$1['2']/g" my_old_file.php

Or apply your regex to all *.php-files recursively below the current directory to a whole project:

find . -name "*.php" -exec sed -i "s/$([a-zA-Z0-9_]+)[([a-zA-Z0-9_]+)]/$1['2']/g" '{}' ;

By the way: The usage of sed works fine on your linux command line, but not on OSX. The syntax is slightly different here (sed -i “” -e “s/blah/blubb/” file). This is of course only a start to automate otherwise painfull and boring corrections down to just a few seconds. It will not save you from special manual work and break syntax at some points. But it weeds out 90% and leaves you with the other 10% acutal manual work.

You could imagine many more sed regexes e.g. to replace short tags <?=$my_var?> to a proper <?php echo $my_var; ?> etc.

echo '<?=$out?> sakdhs sakdhas k <?php echo $xyz; ?> ddd' | sed "s/<?=/<?php echo /g";

I will collect more regexes as I need and find them. If you have ideas please add them in the comment section.

Sync Your Stuff to S3

This is a receipe how I save stuff to S3 from my Mac:

1.) Signup with S3: http://aws.amazon.com/s3/ (check pricing!). This will give you access to the AWS Management Console.

2.) Create a Bucket: This can be done via the AWS Management console. If you are not familiar with the concept of ‘buckets’ check-out the S3 documentation. Simply put, it is a virtual storage device that has a fixed geographical location.

3.) Go to ‘Security Credentials’ in your account settings in the AWS Management console and create an accesskey.

4.) Download JetS3t. You then have the following directory on your Mac:
/Applications/jets3t-0.7.3

Open the Terminal and change into the bin directory:
$ cd bin;

Create a file named synchronize.properties there:
$ nano synchronize.properties;
and save the following content using your keys from step 3:

accesskey=XXXXXXXXXXXXXXXXXXXX
secretkey=xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

5.) To sync the contents in the path /Users/marco/MySyncStuff with the bucket myBucketName use this command:
$ ./synchronize.sh UP myBucketName /Users/marco/MySyncStuff/ –properties synchronize.properties;

Of course you can create as many buckets as you like and script and schedule your data syncs now from here as you wish. Use the command
$ ./synchronize.sh –help
to see what the synchronize.sh else has on offer.

6.) Browsing buckets: JetS3t has its own S3 browser. To start and use it do the following:

$ cp cockpitlite.sh cockpitlite.command;
$ ./cockpitlite.command &;

You should see the Java coffee cup on your task bar. Use your keys to log in and browse your buckets.

You can also use the free S3 Browser for Mac.

MSSQL CAST() and GROUP BY

Just in case somebody has a similar problem… Coming mainly from MySql I had to deal with some MsSql/SQL Server specialties. Consider the following working SQL for MsSql:

SELECT AK.AK_TEXT, BU.BUDGET_BEREICH ,TY.AKTIONSTYP_TEXT ,AK.AK_STARTDATUM ,AK.AK_ENDEDATUM
FROM AKTIONEN AS AK WITH (nolock)
LEFT JOIN BUDGET_TRAEGER AS BU
ON AK.AK_BUDGET_NUMMER=BU.BUDGET_NUMMER
LEFT JOIN AKTIONSTYP AS TY
ON AK.AK_TYP=TY.AKTIONSTYP_TYP
LEFT JOIN AKTIONEN_FILIALE AS AF
ON AK.AK_LFDNR=AF.AF_LFDNR
WHERE (
  ( AK.AK_ENDEDATUM<=CAST('2010-06-30' AS smalldatetime)
   AND AK.AK_ENDEDATUM>=CAST('2010-01-01' AS smalldatetime)
  )
  OR ( AK.AK_STARTDATUM<=CAST('2010-06-30' AS smalldatetime)
    AND AK.AK_STARTDATUM>=CAST('2010-01-01' AS smalldatetime)
  )
)
GROUP BY AK.AK_TEXT, BU.BUDGET_BEREICH ,TY.AKTIONSTYP_TEXT ,AK.AK_STARTDATUM ,AK.AK_ENDEDATUM;

Interesting parts explained:

(a) The addition ‘WITH (nolock)’ prevents my application from locking the table during select queries.

(b) The fields from which I liked to select a bunch of records touching a daterange between AK_STARTDATUM and AK_ENDEDATUM were of the fieldtype ‘smalldatetime’. This is some strage SQL Server format. It needs to be converted to with the CAST() function in order to return the expected resultsets. It understands several input formats. You can test the correct conversion with a simplified SQL statement like this: SELECT CAST(’2010-03-29′ AS smalldatetime);

(c) The statement will not work and return an error if you do not include ALL your selected fields also in the GROUP BY clause.

Backup MySql-Database using only FTP

We have just had the case of having ftp access to a site on a shared hosting LAMP webspace but needed also the database and mainly the database scheme to setup our own development-system for the app. This is actually very straight forward using the following snippet uploaded and executed on the webserver:

<?php
exec( 'mysqldump -h dbXXX.1und1.de -u dboYYY -pZZZ dbYYY > dump.sql', $ret );
var_dump($ret);

This is what you do:

  • Find the configuration with the db-credentials in the source of the application and replace
    • dbXXX.1und1.de with the hostname,
    • dboYYY with the username,
    • ZZZ with the password (notice there is NO blank between the option -p and the password!),
    • dbYYY with the database you would like to backup.
  • Save the snippet as file with extension .php and copy it via ftp to the webspace.
  • Find the URL to execute the script.
  • If it worked, there should be a new file dump.sql in the same directory, download it, it contains the db-schema and all data.
  • Delete both files to wipe your traces.

If it does not work try something like “exec(‘ls -la’);”. If you see the contents of the directory you can use the exec-function and the problem is something else.

Happy hacking!