All posts by marco

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

More Efficient Running

I hope you geeks out there do any sports regularly despite the common prejudices. I do and I am getting a lot in return. Moving your body in a skilled way running, cycling, swimming, playing whatever is a form of intelligence, gives you new energy for your work, clears your mind and sometimes even delivers unexpected inspiration too.

So, I would like to share a very interesting ‘life hack’ with you in order to have more fun and less pain running and improve distance, endurance and speed at the same time.

What happened: One year ago I simply started running, bought myself a basic set of running gear and exercised once or twice a week – without any training or professional advice. My distances increased during the first months from 2km up to the point where I could run around 10km. My average pace was 6:30min per km at that time (very slow like a power walker). I thought I needed to be as slow as that in order to make the 10km.

Then I registered for a half marathon and tried to increase the distance to 20km – I thought this would just be as simple as getting from zero to 10km. But I couldn’t. It was so exhausting to run 10km or maybe sometimes even 13km. The complete crash came, when I bought myself new running shoes – you know, not the ugliest and not the cheapest ones I could jog around the local running store without thinking that they do not fit well…

Honestly, I frequently read some running magazines and books but never ever came across the things I really needed to know to be able to run a half-marathon without hurting myself.

After the first exercises with the new shoes distances decreased and I felt more and more pain the day after the exercise in my left knee. This must have been the point where most people stop running and do something else! Despite the pain I forced it and got very frustrated. Sometimes it happened, that after 5/8km I made a clumsy step on uneven ground and BANG I had to stop and sometimes take the tram or a taxi or at least take a shortcut and walk back home. Distances I could run decreased rapidly. The last run was just some 100 meters and BOOM. What a mess!

So I went to a doctor, seriously concerned. 5 weeks before my first half-marathon event. This was the first time I heard about pronation and supination and about how this incredibly built foot absorbes the shock from each of your steps when running. The doctor asked me that type of runner I was and I had no f#$%&ng clue what he was talking about. It turned out, that I bought just the wrong shoes for my anatomy. Shocks went directly into my knee causing direct damage there and in my lower back too. Diagnose: irritated knee. Prescription: 2 weeks pause and painkillers. Advice: Beginner, buy yourself the right shoes and make your homework before you ruin your health! The doctor gave me a contact to a good physical therapist and urged me to do some training sessions with him.

A long story, but important to understand where I came from. I would like to share with you geeks what I learned about running and running equipment and I hope to save you from experiencing a similar disaster:

  1. Buy yourself the right running shoes: Make your homework and understand the terms pronation and supination. This describes if your foot tilts to the inside or the outside when it hits the ground while running. Check youtube.com (search for ‘running pronation supination’) for some very good explanations. Go to a good store that can make a video analysis of you running along a track wearing your new shoes. Watch your steps in slow motion with the sales assistant and let him make a definitive recommendation for a pair of shoes. Ensure that the store takes the shoes back if you have any problems in your knee or back, even after some weeks of training. The color of the shoes is absolutely no factor.
  2. Learn how to do forefoot running: Watch yourself running or videotape yourself. Make your homework what a good posture looks like and how your feet push the road behind you with every step without braking when you step ahead of your point of balance. The whole mystery boils down to two points: Avoid heel strike, practice forefoot running. Again check youtube.com (search for ‘forefoot running’ and ‘running heel strike’) for the details. It will take some time to understand the real difference between a good running technique and your old, intuitive bad one. Be aware that this changes a lot: You need to have stronger calves and do regular ham string exercises. Otherwise they will be the new parts that hurt. Start to increase distances and speeds again from zero. Do not expect to extend your distances like in my case from 10km. Start over when you change your running technique. Forefoot running increases the speed and step length and you will get tired more quickly. But this is just when you start. So, start with shorter distances and do not overpace – like I did, since I had to be ready for the halfe marathon in a couple of weeks.
  3. Join a running group: This is an optional part but can help a lot if your group or class consists of some experienced runners with a proven track record – multiple iron (wo-)men or the like. There you can learn a lot and the stories you hear may also push you forward.

I really hope, to save you from the pain and frustrations I experienced. In the end I finished with the official time of 2:20h on my first half marathon and it will not have been the last one.

Update: Finished 5th www.lucernemarathon.ch (half-distance) on 30.10.2011 in 1:58:40.

Have fun running!

Emulate sendmail on your Dev Machine

In order to prevent email delivery during development and log all email messages that would have been delivered, you can actually do a simple trick: Replace the file /usr/sbin/sendmail (on Ubuntu, use ‘locate sendmail’ to find it if it lies elsewhere) with this little shell-script, or rather make a _bak of the original and save the following instead of the sendmail binary:

#!/bin/bash

LOGDIR="/tmp"
PREFIX="sendmail"
NOW=$(date +%Y-%m-%dT%H.%M.%S)
CNT=1
PRIVATELOG="$LOGDIR/$PREFIX-$NOW.$CNT.log"
COMBINEDLOG="$LOGDIR/$PREFIX-combined.log"

# If privatelogs are being used...
if [ ! -z "$PRIVATELOG" ]; then
# ...make sure the filename is unique and create the file
while [ -f $PRIVATELOG ]; do
CNT=$(($CNT + 1))
PRIVATELOG="$LOGDIR/$PREFIX-$NOW.$CNT.log"
done

echo "$0 $*" > $PRIVATELOG
else
# ...otherwise swap filenames
PRIVATELOG=$COMBINEDLOG
COMBINEDLOG=''
fi

echo "[$NOW]" >> $PRIVATELOG
while read BUF
do
echo $BUF >> $PRIVATELOG
done

# Append privatelog to combinedlog when both logs are used
if [ ! -z "$COMBINEDLOG" ]; then
echo "[$NOW]" >> $COMBINEDLOG
cat $PRIVATELOG >> $COMBINEDLOG
fi

exit 0

When your application now sends mail, these things happen:

  • No email is actually sent.
  • The message gets appended to the file /tmp/sendmail-combined.log, on which you could set a ‘tail -f’ in order to see which emails would have been sent and what contet they would have.
  • One new file (e.g. /tmp/sendmail-2011-02-08T08.02.48.1.log) gets written for every email sent. I personally only use the combined file.

Inspired by http://stackoverflow.com/questions/3710864/simulating-sendmail-with-dummy-script

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.

Stuff I need to lookup every time

Set ignore to all files of a directory with subversion:
$ cd cache
$ svn propset svn:ignore '*' .
$ svn ci . -m 'Ignore set on cache dir.'

Show changed files between two revisions, overview
$ svn diff -r 300:HEAD --summarize

Show changed files between two revisions, for each revision:
$ svn log -v -r 300:304

See overall latest 20 commit-messages:
$ svn log -l 20

Branching and merging:
See: http://blog.evanweaver.com/2007/08/15/svn-branching-best-practices-in-practice/

Only grep in php source files, not jpgs, movies etc.:
$ grep -i 'whatever' `find . -name '*.php' -print`

Add all new files in a large filestructure to subversion, like after an update of vendors in Symfony2
svn st | grep "^?" | awk "{print $2}" | xargs svn add $1

Remove all deleted files from a large filestructure from subversion, like after a vendors update in Symfony2
svn st | grep '^!' | awk '{print $2}' | xargs svn delete --force

Setup external libraries in subversion:
svn mkdir ZFVersions;
svn add ZFVersions;
svn ci ZFVersions -m 'Added dir for all versions.';
cd ZFVersions;
svn mkdir 1.11;
svn add 1.11;
svn ci 1.11 -m 'Added version subdir.'
cd 1.11;
svn propset svn:externals 'Zend http://framework.zend.com/svn/framework/standard/tags/release-1.11.0/library/Zend' .; # This will checkout in another dir Zend into your dir 1.11. You need this since autoloading is using paths like this require_once(Zend/Feed/Rss.class.php)!!
svn commit -m 'Set external.';
svn up .; # Loads external lib.

Correct date problems in a mysql database – 2020 instead of 2011 in YYYY-MM-DD dates:
UPDATE accounts SET member_startdate = CONCAT('2010', '-', MONTH(member_startdate), '-', DAYOFMONTH(member_startdate)) WHERE YEAR(member_startdate) > 2011;

What is it you guys always look up?

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.

Entrepreneurial maxims

I recently came across an interesting table comparing typical mindsets of freelancers to those of entrepreneurs, which I immedately printed out and sticked to my refrigerator as a reminder. Since we currently have startup conditions too in our company, I know that it takes a lot of discipline to make adjustments to a mindset that served you well for the last 10 years but now more often gets in your way as daily tasks, priorities and responsibilities (must) change.

Area Freelancer / skilled employee
Entrepreneur
Employees, colleagues I can get stuff done best on my own. My employees are better trained, specialized and have more skills.
Complexity More complexity makes things more interesting. Simple things make you more successful.
Value in own work I cost less than my employees, since they get a fixed salary. My work is the most expensive and should not be wasted.
Time Money has more value to me than time. I spend time to save money. Time is more valuable than money. I spend money to save time.
Money Money means security. Money yields opportunity for investments and ventures.
Education, learning My professional development is important to me. My personal development is most important.
Risk I try to avoid risk whenever I can. Steps to success are naturally accompanied by risk-taking. Being employed is for example more risky than being an entrepreneur.
Mission My profession is my mission. My mission is to build a great company and actively build my environment with it.
Comfort zone I am best at, where I feel save and comfortable. My success and growth both lie exactly where I feel uncomfortable.

Thanks to Google I also found the source of the table: It is taken from a German blog post titled ‘Warum manche Selbständige nicht zu Unternehmern werden‘ (Why some freelancers never become entrepreneurs).

Hope it helps some of you too.

MODx Revolution Links

I have recently been playing with the new MODx (‘Revolution’) and I must say, I love the concept and it’s flexibility. Just to make things easier for folks starting out with MODx or who migrate from MODx Evolution here are some recommended links.

General:

Focus topics:

  • Tag format changes from Evolution to Revolution: The formats for content tags have changed. They now can be nested, they can carry parameters and look a lot cleaner.
  • Use the Package Manager to install your snippets: There are still some packages to migrate (e.g. AjaxSearch). And if you install Login and wonder why you can not logout, just check the naming of the parameters. I am sure these issues will soon be solved.
  • Internationalization: The new Lexicon facility makes the creation of multilingual pages easier, especially central shared Pages or Chunks used in all available languages like the login, password reminder, 404 etc. You can even use Placeholders in lexicon entries and fill them dynamically with Parameters. On bigger sites I would prefer to have a home page for each language and maintain each language in separate menu trees. This is explained in this forum entry. Notice, the tutorial is based on Evolution, but works the same way in Revolution. You only need to use the new tag syntax.
  • New access to MODx objects: Most of the methods from Evolution to interact with $modx will be deprecated soon in Revolution. If you are writing Snippets or Plugins you need to get familiar with $modx->getObject() and Co.
  • New permission system: Instead of the distinction of Web Users and Manager Users in Evolution, Revolution switched to ACL (Acesss Control Lists) and Context based permissions.
  • Make Resources external to make use of version control: Required resources can also be loaded from files instead of saving everything to the MODx database.
  • Friendly URLs: How to use the enclosed .htaccess and what MODx settings to use.

What I dont like with MODx Revolution is the slow Manager interface based on ExtJS. If you have to go through a longer configuration or exploration session, waiting for the frontend to finish loading can be a drag. But the ajaxy Manager helps you to perform some actions in parallel without you losing your form content, e.g. if you need a href and need to lookup the document-ID you just click on the Resource tab and look it up in the tree.

Overall I must say MODx Revolution makes the expression to me like a very well crafted, cutting-edge and thought through CMS. Congratulations and many thanks to all contributors! You are awesome!

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.