Clever Geek Handbook

SQL in email application

I would like to be able to execute SQL queries on my email inbox. With the output, I can draw up graphs about how many emails I send or receive, for example. I want to analyze my work and what keeps me busy. My inbox seems like a good place to start.

I use Gmail online, and Thunderbird, Outlook 2007, and Mail.app over IMAP. Is there an easy way how I can connect one of these apps with something I can use for SQL?

Everybody tried:

  • Thunderbird doesn't have an add-on for this (or I couldn't find one).
  • I suspect Outlook will be able to do something along with MS Access, but I don't know how to do it.
  • None of my clients seem to be able to export anything like CVS or Excel.xls. From there I can import into Access and be done.

UPDATE: Access 2007 has an Outlook Connection Wizard. But when I run it, Office won't start complaining that Outlook 2007 was not found (?). UPDATE 2: You can fix this by setting Outlook as your default email client, closing Outlook and restarting it.

+1


source to share


4 answers


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



+2


source


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



+1


source


I agree with stalpretzel, you should probably make your own if you can't find a ready-made package.

What I would do:

  • Download messages using IMAP.
  • Analyze them and insert them into your database

You can effectively use the mess822 software package to parse the email headers and then use it to populate the database.

I think many script languages ​​(perl, python) have similar libraries (probably for IMAP) and they are pretty easy to hack.

0


source


I suggest making your own addon. I am not familiar with Thunderbird addons (and I was only tinkering with FF), but I assume they are similar to FF addons if you know Javascript along the way. I would research Thunderbird add-ons and create one that runs an AJAX script whenever it receives a message. This AJAX can communicate with a PHP script (which you will need to run on Apache, perhaps only on your home computer). This PHP script, in turn, will interpret the data sent by Thunderbird Javascript and enter it into the database.

-2


source






More articles:


All Articles