SQL in an e-mail app

Question

I would like to be able to perform SQL queries on my e-mail inbox. With the output, I can make graphs about how much e-mails I send or receive for example. I want to analyze my performance and what keeps me busy. My mailbox seems like a good place to start.

I'm using Gmail on-line, and Thunderbird, Outlook 2007 and Mail.app trough IMAP. Is there an easy way how I can connect one of those apps with something I can SQL to?

Allready tried:

• Thunderbird does not have an add-on for this (or I couldn’t find it).
• I suspect Outlook to be able to do something together with MS Access, but I wouldn't know how.
• None of my client seems to be able to export everyting to something useful, like CVS or Excel's .xls. From there on, I can import to Access and done.

UPDATE: Access 2007 does have a wizard on connecting to outlook. But when I run it, Office starts for no reaston to complain that Outlook 2007 is not found (?). UPDATE 2: You can fix this by setting Outlook as the default e-mail client, closing outlook and starting it again.

Answer 1

I suggest making your own addon. I'm not familiar with Thunderbird addons (and I've only dabbled in FF ones), but I assume they're like FF addons in that if you know Javascript your well on your way. I would research Thunderbird addons, and create one that runs an AJAX script whenever it receives a message. That AJAX could contact a PHP script (which you'd need to run on something like Apache, possibly just on your home computer). This PHP script, in turn, would interpret the data that Thunderbird's Javascript sent, and enter that into a database.

Answer 2

I agree with stalepretzel, you should probably make your own if you can't find any ready-to-go package.

What I'd do is:

1. Download the messages using IMAP.
2. Parse them and insert into your database

You could make good use of the mess822 software package to parse the mail headers, then use that to populate a database.

I think a lot of script languages (perl, python) have similar libraries (probably for IMAP as well) and should be fairly easy to hack together.

Answer 3

You can parse mbox files (which Thunderbird uses to store it's messages) using any number of scripts like this then do any sql on the messages you like.

Answer 4

You can simply connect Outlook to Access via Access Wizards or code (http://support.microsoft.com/kb/209946/en-us)