Migrating an old MS Access database to MySQL [UPDATED]

August 3, 2009

I’m in the process of migrating an old website I made almost 8 years ago from it’s current unbearable platform of Windows, ASP and MS Access to some sort of *nix, PHP and MySQL. Tonight I decided to combat that MS Access database and transform it into a real database. To help me accomplish this I used the excellent tools provided by the mdbtools project.

First I had to export the schema of the database to a format readable by MySQL:

mdb-schema -S mydb.mdb mysql > mydb.sql

This creates an SQL-file containing the schema in plain readable SQL. Very nice.

Next I had to export the actual data from the tables in the MS Access database, like this:

TABLES=`mdb-tables mydb.mdb`
for T in $TABLES; do
mdb-export -I -R';\n' mydb.mdb $T >> mydb.sql
done

The first line stores the names of all the tables in the variable TABLES. Then I simply loop through all of them and execute the mdb-export command on all of them, writing the output to the end of the schema-file.

In case I need to do this again in the future I put it all together in a script like this:

#!/bin/bash
DBFILE=$1
OUTFILE=$2

#Check for correct number of arguments
if [ ! $# -eq 2 ]; then
echo "Usage: access2mysql.sh DBFILE OUTPUTFILE"
echo "Example: access2mysql.sh msaccess.mdb mysql.sql"
exit 1
fi
#Check that DBFILE really exists
if [ ! -f $DBFILE ]; then
echo "$DBFILE does not exist."
exit 1
fi

#All is good, here we go!

#Create schema
mdb-schema -S $DBFILE mysql > $OUTFILE

#Export table data
TABLES=`mdb-tables $DBFILE`
for T in $TABLES; do
mdb-export -I -R';\n' $DBFILE $T >> $OUTFILE
done
#Clean up some Windows-character stuff
dos2unix $OUTFILE
exit 0

Now all I have to do is create a MySQL-database and write my newly created SQL-script into it and I’m good to go!

UPDATE:
Before I could get the SQL-script to work I had to fire off a couple of sed-statements to fix the fact that I had used some illegal words as names for some of the table-columns, “type” and “condition” to be specific. No biggie though. Also, mdb-schema didn’t add “AUTO_INCREMENT” to the fields that were auto incrementing in the MS Access database so I had to do that manually.

Advertisements

Crowdsourcing your iTunes music with PHP, AppleScript and Twitter

February 20, 2009

The other day I was catching up on the last few episodes of Hak5, one of my favorite internet-TV shows, and in one episode Darren Kitchen was doing some neat tricks with PHP, Twitter and VLC. This got me thinking about what other kinds of fun projects Twitter could be used for.

Twitter is a great service, not very reliable however (fail whale anyone?), and there are already plenty of mash-ups that uses it for various things. My idea was to use Twitter to select what plays in iTunes. People could send me replies with songs in them and if the song was in my iTunes-library it would be played, as simple as that. Not very useful, but a fun experiment if nothing else. So I started coding.

In order to control iTunes I had to delve into AppleScript for the first time. I find it to be an odd language, but whatever, for this project it’s definitely the most appropriate choice for getting quick results. One of my favorite languages is PHP so that’s what I chose for the control structure of the whole thing.

A couple of hours later I had my first prototype and now an additional few hours of tweaks I give you… TwitTunes!

This is how it works:
Person #1 starts TwitTunes on his Mac. TwitTunes sends a tweet – “#TwitTunes starting” – using Person #1s Twitter-account to let the world know that it is running.
Person #2 sends a reply on Twitter containing search words. These search words are then used to search through Person #1s iTunes-library and the first matching song found is played.

Not very fancy and far from perfect, but admit it, crowdsourcing your jukebox via Twitter is a pretty nifty idea and this is my very first proof of concept 😉

If you wanna try out TwitTunes yourself you can download it here (sloppy source code included of course), and if you have any feedback I’ll be happy to hear it.


The dumbest and most faulty sentence ever in a tech-article

December 6, 2008

Taken from this article on Forbes.com:

Google employees not using the secret OS are employing various versions of Unix, such as Linux or Ubuntu, and some older operating systems, like X11, he says

And “he” being “Vince Vizzacarro, Net Applications’ executive vice president of marketing”. That sentence is riddled with enough erronous information to make me cringe.

One, Ubuntu is a version of Linux, which in turn is a Unix-like OS. If I was gonna nitpick I could also state that Linux is in fact not an OS – it’s an operating system kernel and operating systems based on it are called distributions. But never mind…

Two, X11 is not an operating system or anything close to it. X11 is windowing system software used by most desktop system for Unix-like OSes, like KDE, Gnome or XFCE for example.

So now I wonder who the idiot here is. Is Vince Vizzacarro with his fancy title as completely misinformed as he seems or has he been misquoted by the writer of the article? Either way it’s a disgrace.


Maybe they’re not all bad after all

July 1, 2008

Or at least they’re getting better, Microsoft that is. According to a recent announcement on their website they’re finally releasing documentation for their horrid binary file formats. The ones used up until 2007 at least.

This is a good thing, a very good thing even. Granted that the documentation is accurate this means it’ll get easier for developers to interoperate with Microsoft’s proprietary technologies and that’s great since they have such a huge percentage of the market when it comes to wordprocessing, spreadsheet and presentation software (i.e. Word, Excel and PowerPoint). Good news for data portability in other words and maybe now the OpenOffice-team won’t have to reverse engineer stuff until they’re blue in the face to make Microsoft Office documents work flawlessly within their own applications.

I’m calling this a good day for interoperability and open development.