Take the 2-minute tour ×
Server Fault is a question and answer site for system and network administrators. It's 100% free, no registration required.


are there any tools available that will automatically parse an incoming e mail to a specific address, strip out the data and insert it into a SQL database?

The e mail would be coming in through exchange 2003 server and would be in a known fixed format, ie

Name: Firstname Surname
ID Number: nnnnnnn
etc.
etc.

Ideally the solution would need to operate on the server and not a client. Any advice appreciated.

share|improve this question

closed as off-topic by masegaloeh, HopelessN00b Mar 3 at 3:10

This question appears to be off-topic. The users who voted to close gave this specific reason:

If this question can be reworded to fit the rules in the help center, please edit the question.

    
I would ask this on stackoverflow since it's a scripting thing. –  MathewC Jun 26 '09 at 15:43
    
may I ask what you finally did to do that since the question was asked 4 years ago? –  Nicolas Manzini Jun 6 '13 at 16:59

3 Answers 3

up vote 2 down vote accepted

There is a commercial software, which seems to do that:

Email2DB

  • Read and process messages from POP3, IMAP & Microsoft Exchange Servers

  • [...]

  • Easy extraction of any number of fields from the subject, message & headers. Update any data source including, Access, SQL Server, Oracle, MySQL, Access, ODBC.
share|improve this answer

I've always seen this kind of thing done with a little bit of glue code. A little perl would work for this:

#!/usr/bin/perl -w
use strict;
use Net::POP3;
use DBI;

my $pop = Net::POP3->new('mail.server.com') or
        die "shit, I can't connect\n";

my $dbh = DBI->connect('DBI:mysql:databasename', 'username', 'password'
               ) || die "Could not connect to database: $DBI::errstr";


if ($pop->login( 'username' , 'pass' ) > 0) {
    my $msgnums = $pop->list;
    MESSAGE: foreach my $msgnum (keys %$msgnums) {
        my $msg = $pop->get($msgnum);

    	my ( $firstName, $lastName, $idNum );

    	if( $msg =~ /^Name:\s+([a-zA-Z]+)\s+([a-zA-Z]+)/){
    		$firstName = $1;
    		$lastName = $2;
    	}
    	if ( $msg =~ /^ID Number:\s+([0-9]+)/ ){
    		$idNum = $1;
    	}

    	$dbh->do('INSERT INTO exmpl_tbl VALUES( ? , ? , ?)', undef, ($firstName, $lastName, $idNum));

        $pop->delete($msgnum);
        next MESSAGE;

    }
}

$pop->quit;
exit 0;
share|improve this answer

If you want to play with an open source project, have a look at Archiveopteryx (http://www.archiveopteryx.org/). It's got a really, really sweet normalized SQL-based data storage schema for email (using PostgreSQL). You could combine it with, say, "fetchmail" and POP3 server on Exchange to create an SQL-based mail archive that you could report and search against with ODBC-based reporting tools.

share|improve this answer

Not the answer you're looking for? Browse other questions tagged or ask your own question.