Connecting your MySQL data with MS Excel

Excel is still the most popular product when it comes to working with spreadsheets or analysing data. I often analyse large spreadsheets with data I got from a database. 

While it’s not too much effort to copy the data into the spreadsheet when you do need something ad hoc, it can be quite frustrating if you are preparing the same reports over and over again. It would be much easier to just integrate the query as a data source in Excel.
Excel has already got great features to tie into MSSQL, probably because they want to sell more of their own stuff, but I suppose it should be just as easy connecting it with MySQL…
  1. We will need a driver:
    MySQL provides a lot of connector files on their website, it’s pretty easy to navigate and find what you are looking for, but, just  in case, here is the link to the ODBC files I will be using for an Windows XP conncetion: http://dev.mysql.com/downloads/connector/odbc/#downloads
  2. We will need the data source as a ODBC connection in Windoze:
    Go Start > Control Panel > Administrative Tools > Data Sources (ODBC) > Add…
    and find the newly installed MySQL driver. Then it will ask you to enter your details and you should be half way there already…
  3. It’s already time to use Excel:
    so start Excel… There are now different ways of getting data into Excel. I want to use my own query, so I’m going to use the ‘From Microsoft Query’, as it recognises the existing ODBC connection we established.

    Then I’m going to connect to the ‘localhost’ connection I previously created:

    To get further, I need to select at least one field from the database. It doesn’t matter if it’s a relevant field, because a query tool will appear at the end of the dialogue.

    It comes in form of a question, and you select the lower radio button:

    Finally, you can add your own query (SQL button).
    And the result:

It will take a while when you enter your own query, very much depended on how complex your query is, but I hope for you it takes under five minutes to run πŸ˜€
Let me know if that works for you or if you have any breaking points. 

Installing Office 2007 on Ubuntu

This evening I thought I finally give installing Office 2007 another go. Not that I don’t like OpenOffice, but I often deal with files that have more than 65k lines…

I am no fan of Microsoft, but I must admit that the Office suite was always great. Probably that’s why it’s the only non native software that’s made it to the Mac…
I also got quite used to using the ribbon and it’s visually very pretty to work with MS.

So, I read that you should use an older wine version, namely 1.1.14, but I also read on the wine site that everything above 1.1.3 should also be suitable, so I will get the latest wine version Synaptic can find (which is 1.1.38). I will also install wine-gecko and I know we will need cabextract… The package manager thinks it’s wise to install a few other packages to, so here we go!

Good news: so far so good..
it starts the installation and asks for product key…
off to choose the programmes…
and the installation proceeds… (the installation is anything but quick…)

For the next step we need winetricks:
Get it by:

sudo wget www.kegel.com/wine/winetricks

So now we should be able to install a few windows repositories:

sh winetricks corefonts tahoma vcrun2005sp1 wsh56js

This will ask you a few time if it’s OK to install certain things and once completed, you need to configure wine:

winecfg

Find the libaries tab and add to new overrides:
riched20 & usp10
both will need the ‘Native (Windows)’ button ticked.

And off we go!
It installed in fine and it runs fine. Here is a screenshot:

Installing the Google debian / ubuntu repository (chrome / picasa)

I have found it difficult to find the line what exactly the line is that I need add to my /etc/apt/sources.list ... I finally found it on the Google site here.

But to spell it out:

# Google software repository
deb http://dl.google.com/linux/deb/ stable non-free main

If you are using the GUI, you can simply go to

Software Sources > Other Software > Add

and add the repositories this way. This should then also automatically provoke an update of the software sources…

There might get a warning saying that the package is unsigned. I haven’t tried, but from what I can see, there is a link on the Google page.

Google Wave – Testing the preview

What can I say? Not much, except it’s a cool tool.

I have yet to find reasonable use for it, but having access to it is quite nice. I also still don’t have enough people that I can communicate with, so it’s a bit of challenge using it.
I still have a few invites left, so if you are reading this and are interested, send me a message or leave a comment to get access…

One plugin that I think would be very desirable is a calendar feature: link items straight to your Google or Office calendar would be awesome.

We will see what the future holds…

Here is an early screenshot:

Removing the Koobface Worm (WORM/Koobface.bgn)

I came across this facebook worm for the first time today… It’s seems that more and more facebook users get their account hacked and then somebody posts links to malicious sites on the walls of their friends.

Unsuspecting friends then install updates to the ‘Flash Player’ or what-have-you and BANG!

In this instance the removal seems easy enough:

  1. Get a list of suspicious processes (HiJackThis can help). In this instance we have:
    C:windowsld15.exe
    C:windowspp12.exe
    And lots of files in:
    C:Documents and Settings[USER]Local SettingsTemp
  2. I got myself a Linux Live CD (ubuntu) and booted into this live version
  3. I started deleting the files above
  4. I rebooted into save mode (press f8 just after BIOS message)
  5. When Windoze started, I went into the registry (press WinKey + r and enter ‘regedit’) and deleted the referring keys in:
    HKEY_LOCAL_MACHINESOFTWAREMicrosoftWindowsCurrent VersionRun

Restarted and prayed πŸ™‚

But it seems to have worked, cause I don’t see any suspicious process in HiJackThis anymore.

Video splitting with MEncoder

So now the question is how can you use MPlayer’s Mencoder to split a video file into chunks, or how can you just cut a scene out?

I haven’t really found an option to just cut it out in one go, but MEncoder is so fast, it’s easy to just cut twice, first the beginning:

mencoder -ss 00:13:11 -oac copy -ovc copy original.avi -o new.avi

Simply exchange the time (HH:MM:SS) with the ‘begin’ point of the new video. That copies all the rest of the file into a new one.
Now you need to trim the file to get the new end:

mencoder -endpos 00:01:11 -ovc copy -oac copy in.avi -o out.avi

This makes the file exactly 1 minute 11 seconds long.

With this method you can split any video in any number of smaller chunks.
Happy video sharing!

PS: I think I already showed how you can combine to videos, in case you wanna reverse the process, if not, I will post this soon.

Quake Live now for Linux

I have been playing ID software’s ego shooter since the years of Doom. Back then I wasn’t really ‘hooked’ and found ego shooter a rather dull waste of time.

Although, I did went on playing Doom 2 and it’s franchise, but again for lack of something better to do.

When I first saw Quake with it’s very pixelated graphics, I immediately disliked it. It wasn’t until me and my friends started patching computers into a network, using Coax cables at the time, that I learned that mouseaim and strafing opened a new dimension to Doom legacy…
When I then saw the game in GL mode, I realised the potential the game had… Shadows and pixel free animation was such a great improvement to the game.


Shortly afterwards I got hooked. I played all the expansions, with my favourite one being Painkiller, a multiplayer only mod. It featured the can of beans (gave you 100 HP, but made you fart), the bear trap and the gravity orb. It was good fun and we had lots of laughs when we were playing it some years ago. I even played Alien Quake, which was so dark and scary, it made me jump everytime I saw (or could hear) and enemy.


I played SO much that I was by far the best in my league of friends. I started playing online, even went to a few tournaments and bigger events.

Then came the long awaited Quake 2 in 1997.

It was different. A lot different. Apart from my usual problem of not having a high end machine to play the game, it was also a lot slower then Quake. The rocket launcher was not as strong anymore, the
jumps didn’t seem to be as powerful (although I found out that they were better than in Quake, but way too late) and noone really wanted to spend much time playing it.
Needless to say, I finished my way through the story mode and then never played it again. I saw people playing it on LAN parties, but I stuck to good old Quake or played Blobby :)…

Two years later, in 1999, ID software released the best ego shooter the world has seen to this day: Quake 3 Arena.


But at the time it was out and new and people were playing it religiously, I didn’t have a computer that was able to support that game. In later years, when it was still ‘in’, my computer was good enough to play it and I developed some half way decent skills. I played with friends till the early dawn, went out and played on LAN party tournaments and even got into moderate ranks from time to time.


But I couldn’t play online (because T-Online didn’t have a proper ping). The usual mods came out, within them a few really good one: Challenge Pro Mode Arena, another Painkiller and the engine was used in pretty much any ego-shooter you could think of.
In the meantime, Quake 3 developed into somewhat of a religion. Even girls were starting to play Quake 3! But it got worse: people weren’t afraid anymore to admit to being excessive gamers, in fact some even painted to logo on their skin:

(but there were also pretty ones)

A few years later and I had a very decent Quake 3 able machine, but guess what: nobody played anymore :(… I found a few people to give me a game on rare occasions, but these poor guys had no Quake experience and lost constantly, even when I tried to let them win. I had moved countries twice and playing with the old team was nearly impossible.

Eventually I migrated to Linux and by that time discovered OpenArena, which was good fun and had a nice community, but that community was small and you often couldn’t get games going. Still, I was very fond of the usual playmates πŸ™‚

In my frustration I tried Quake 4, a title I actually bought – don’t ask me why…


Until I heated rumors about Quake Live: Quake 3 but browser based! How cool was that idea? I got my beta account fairly quickly and even installed Windoze to play it, but eventually gave up on the dual boot, as I never started Windows. Now, another 7 month down the line, there is a Linux native Firefox plugin for Quake Live. My prayers to the ID software god have been heard!


I haven’t been playing all that much, as i hardly have time for it. It’s still a great game and it’s always good to play it with some familiar faces…
I hope you will join me online for some frags soon. Play the best ego shooter in your bowser: Quake Live! If you want to connect with me, my in-game name is: d3ngar (surprise!).

UVC webcam upside down image

I have a laptop with a buld-in webcam. This would normally be all well and good, but the damn thing has a problem with the picture being upside down…

If you have the same problem, here is the solution:

  1. Download the new uvcvideo from here and extract it.
  2. Extract the file (tar xjvf uvcvideo.tar.bz2)
  3. Get one of the patches: 1_mirrored, 1_notMirrored, 2_mirrored, 2_notMirrored (courtesy of arjos85)
  4. Save the patch in the folder ~/linux/drivers/media/video/uvc/uvc_video.c (~ is the path to the just downloaded and extracted driver)
  5. Then run: patch -p0 < [your_patch]
  6. Now build the module: make && make install
  7. load it with: modprobe uvcvideo

Now everything should be working correctly. Try it with Skype or Cheese!

Let me know if there were any problems…

Malware removal: PersonalAV

A friend of mine contacted me today with this issue:
PersonalAV – a malware programme she accidentally installed and can’t get rid of.

Here are some instructions to get rid of the programme, but I don’t know if it comes with any additional Trojans or something…

Step 1: Kill the running processes.

Go to the Task Manager:
right click on free space in the windows panel OR:
Press Ctrl + Alt + Del to get into a menu and choose the Task Manager

End the following processes:
PersonalAv.exe
services.exe
PerAvir.exe
winlogon.exe
services.exe

Step 2: Remove registry keys:

Start > Run > ‘regedit’ > Enter.
Search for the following keys and remove them:
HKEY_LOCAL_MACHINESOFTWAREMicrosoftWindowsCurrentVersionUninstallPersonal Antivirus_is1
HKEY_LOCAL_MACHINESYSTEMCurrentControlSetEnumRootLEGACY_ITGRDENGINE
HKEY_LOCAL_MACHINESYSTEMCurrentControlSetServicesITGrdEngine
HKEY_CURRENT_USERSoftwareMicrosoftInternet Explorer “PrS”
HKEY_CURRENT_USERSoftwareMicrosoftWindowsCurrentVersionRun “Personal Antivirus”

Step 3: Remove the programme files:

I have written two scripts that will do this work for you. Use either Windoze XP or Windoze Vista

You can also manually delete them:
PersonalAv.exe
c:Documents and SettingsAll UsersDesktopPersonal Antivirus.lnk
c:Documents and SettingsAll UsersStart MenuProgramsPersonal Antivirus
c:Documents and SettingsAll UsersStart MenuProgramsPersonal AntivirusPersonal Antivirus Home Page.lnk
c:Documents and SettingsAll UsersStart MenuProgramsPersonal AntivirusPersonal Antivirus.lnk
c:Documents and SettingsAll UsersStart MenuProgramsPersonal AntivirusPurchase License.lnk
%UserProfile%Application DataMicrosoftInternet ExplorerQuick LaunchPersonal Antivirus.lnk
%UserProfile%Application DataPersonal Antivirus
%UserProfile%Application DataPersonal Antivirussettings.ini
%UserProfile%Application DataPersonal Antivirusuill.ini
%UserProfile%Application DataPersonal Antivirusunins000.exe
%UserProfile%Application DataPersonal AntivirusUninstall Personal Antivirus.lnk
%UserProfile%Application DataPersonal Antivirusdb
%UserProfile%Application DataPersonal Antivirusdbconfig.cfg
%UserProfile%Application DataPersonal AntivirusdbTimeout.inf
%UserProfile%Application DataPersonal AntivirusdbUrls.inf
%UserProfile%Local SettingsApplication DataMicrosoftWindowslog.txt
%UserProfile%Local SettingsApplication DataMicrosoftWindowspguard.ini
%UserProfile%Local SettingsApplication DataMicrosoftWindowsservices.exe
c:Program FilesPersonal Antivirus
c:Program FilesPersonal Antivirusactivate.ico
c:Program FilesPersonal AntivirusExplorer.ico
c:Program FilesPersonal AntivirusPerAvir.exe
c:Program FilesPersonal Antivirusunins000.dat
c:Program FilesPersonal Antivirusuninstall.ico
c:Program FilesPersonal Antivirusworking.log
c:Program FilesPersonal Antivirusdb
c:Program FilesPersonal AntivirusdbDBInfo.ver
c:Program FilesPersonal Antivirusdbia080614.db
c:Program FilesPersonal Antivirusdbia080618x.db
c:Program FilesPersonal AntivirusLanguages
c:Program FilesPersonal AntivirusLanguagesIAEs.lng
c:Program FilesPersonal AntivirusLanguagesIAFr.lng
c:Program FilesPersonal AntivirusLanguagesIAGer.lng
c:Program FilesPersonal AntivirusLanguagesIAIt.lng
c:WINDOWSsystem32log.txt
%UserProfile%Application DataMicrosoftWindowswinlogon.exe
%UserProfile%Local SettingsApplication DataMicrosoftInternet ExploreriGSh.png
%UserProfile%Local SettingsApplication DataMicrosoftInternet ExploreriMSh.png
%UserProfile%Local SettingsApplication DataMicrosoftInternet ExploreriPSh.png
%UserProfile%Local SettingsApplication DataMicrosoftInternet Exploreriv.exe
%UserProfile%Local SettingsApplication DataMicrosoftWindowslog.txt
%UserProfile%Local SettingsApplication DataMicrosoftWindowspguard.ini
%UserProfile%Local SettingsApplication DataMicrosoftWindowsservices.exe

Good luck!

However, if you don’t want to do all this and are contemplating reinstalling Windows anyways, make the right decision and install a different OS altogether…

URL for your Dynamic home IP

To be able to connect to computers or servers on the internet, you will have to be able to reach that computers IP address. You might want to connect to your home computer from anywhere in the world, but the IP your ISP gives you changes from time to time.

This is quite common and, unless you constantly carry a note with your IP, you won’t be able to make use of your home computer.
I myself run various services that are remotely available from my home desktop computer: FTP server, VNC server, MySQL, Torrent interface and there are many more soon to come.

To bypass the problem of a variable IP, you will need to use a service like DynDNS. There are many other free service provider out there, I just happen to chose this one.
DynDNS is a free service that, upon updating through their API or interface, makes a record of your IP address and routes that to a URL that you can access through the internet.

Step 1: Simply create an account!

Firewalls and / or routers:
Most commonly people have a router these days. A router acts like a firewall and will not let incoming transfers through, unless it specifically has been set-up to do so and to a specific internal IP. In some cases you can also use the DMZ (DeMilitarised Zone) of the router / firewall. The DMZ will channel all incoming requests through to set IP as if the machine would be connected straight to your external IP. This is not recommended unless you use a software firewall in addition to protect yourself.

Use NAT (Network Address Translation) or how it is called in my router ‘Application Sharing’ to map the incoming requests from any port to a specific internal IP and port. In most cases FTP will run on 21, for example. VNC will run on 5901…
If you use a software firewall, open the same ports on your software firewall. In the Windoze firewall, you have to use ‘Exceptions’, I think (although I haven’t done this in a while).

Step 2: Punch holes in your firewall

Now the last step: You will have to auto-update the DynDNS site from time to time. Most of the newer routers have a feature build in to update Dynamic DNS. You simply select your DynDNS service and add your username and password. Otherwise, you might need to install a programme that will make these updates for you.
There is a list of update tools on the DynDNS website here.

Step 3: Add service to auto update your IP with dynamic DNS host