Take the 2-minute tour ×
Server Fault is a question and answer site for professional 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
    
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

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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