Ripping data from emails and placing it into a database

**OldWelshGuy** · 06-14-2007, 04:17 PM

Ripping data from emails and placing it into a database, how hard is this?

I have just under 1000 emails and want them entered into a list that I can then email.

Anythoughts? or anyone want to take the job on for peanuts? Here are the fields

name:
postcode:
Address1:
Adress2:
Address3:
Dateofbankruptcy:
Dateofbankruptcyyear:
telephone:
DOB:
emailapp1:
empstatus:
Selfemptradename:
selfemplength:
TRadeadd:
TRadeadd2:
tradeaddpcode:

**temi** · 06-14-2007, 04:20 PM

You can get someone to write you a simple script that will extract the info for you.

**OldWelshGuy** · 06-14-2007, 05:52 PM

Aye, that was what I meant. so presumably you just save the emails in a folder and run the script on the folder right?

**temi** · 06-14-2007, 07:41 PM

Yes,
I will ask one of my guys if its something that can be created withing 2-3 hours, if yes, I will get him to create it for you. He is offline now, I will ask him when he gets online tomorrow.

**Humous** · 06-15-2007, 04:01 PM

I was messing around with doing this a while ago.

3 problems though
1 - I didn't finish the project
2 - I had trouble dealing with different types of email (MIME, RTF, text), I'm not even sure what half of them are.
3 - It's in perl, which is fine if you have a linux box

To nab the data I used this function:

Code:

sub getmail
{
my ($pop, $num_mesg, $i, @messages, @matrix);
my ($msgfrom,$msgbody,$msgsubject);

$pop = new Mail::POP3Client (   USER => "test",
                                PASSWORD => "test",
                                HOST => "mailserver" );


$num_mesg = $pop->Count;


for ($i = 1; $i <= $pop->Count(); $i++) {
        foreach($pop->Head($i))
        {
        $msgfrom = $_ if /^(From)/;
        $msgsubject = $_ if /^(Subject)/;
        }

        $msgbody = $pop->Body($i);
        
        push (@messages, [$msgfrom,$msgsubject,$msgbody]);
		
	#$pop->Delete($i);

  }



$pop->Close();
#print "\n\n";
return @messages;
}

you would then need to either search for keywords or organise line by line (depending on how the email is generated).

I used a [5] (or any number) in the subject field to allocate the message to a job id, if it didn't exist I created a new job.

Here's everything else (really messy programmer, it was new to me at the time)

Code:

for($i=0;$i<$msgcount;$i++)
	{
	
	($Second, $Minute, $Hour, $Day, $Month, $Year, $WeekDay, $DayOfYear, $IsDST) = localtime(time);
	if(length($Month) < 2){$Month = "0".$Month;}
	if(length($Day) < 2){$Day = "0".$Day;}
	if(length($Hour) < 2){$Hour = "0".$Hour;}
	if(length($Minute) < 2){$Minute = "0".$Minute;}
	if(length($Second) < 2){$Second = "0".$Second;}
	$Year+=1900;
	$Month++;
	$nowtime = qq~$Year$Month$Day$Hour$Minute$Second~;
	
	$openb = index($msgdata[$i][1],"[");
	$closeb = index($msgdata[$i][1],"]");

	$email1 = index($msgdata[$i][0],"<");
	$email2 = index($msgdata[$i][0],">");
	$emailaddr = substr($msgdata[$i][0],$email1+1,$email2-$email1-1);
	
	$sql=qq~SELECT id,email1 FROM extusers WHERE email1="$emailaddr"~;
	@tmpary=&getspdata($sql);
	$tmpusrid=$tmpary[0][0];

		if(index($msgdata[$i][1],"[") > 0)
		{
		$idstat="";
		$subid = substr($msgdata[$i][1],$openb+1,$closeb-$openb-1);
		}else{
		$idstat="New";
		$sql=qq~INSERT INTO job_track(job_source,job_desc,created,altered,user_id) VALUES("Email","$msgdata[$i][1]\n$msgdata[$i][2]","$nowtime","$nowtime","$tmpusrid")~;
		&putspdata($sql);
		$sql=qq~SELECT id,created FROM job_track WHERE created="$nowtime"~;
		@tmpary=&getspdata($sql);
		$subid=$tmpary[0][0];
		}
		
		if(index($msgdata[$i][2],"Content-Type: text/plain") > 0)
		{
		$extracttype="RTF/HTML";
		$bodystr=index($msgdata[$i][2],"<body>");
		$bodyend=index($msgdata[$i][2],"</body>");
		$extractbody=substr($msgdata[$i][2],$bodystr+1,$bodyend-$bodystr-1);
		}else{
		$extracttype="Plain text";
		$extractbody = $msgdata[$i][2];
		}
		
	$shortbody = substr($msgdata[$i][2],0,100)."...";
	$pibody .= qq~
	From:$msgdata[$i][0]<br>
	Email:$emailaddr<br><br>
	Subject:$msgdata[$i][1]<br>
	Job ID:$idstat $subid<br>
	Type:$extracttype<br>
	Body:$msgdata[$i][2]<br><br>
	limitedbody:[$bodystr][$bodyend]<br>$extractbody<br><br>
	~;
	
		$sql=qq~INSERT INTO job_incomming(mailfrom,subject,message,job,date,user,type) VALUES("$msgdata[$i][0]","$msgdata[$i][1]","$msgdata[$i][2]","$subid","$nowtime","$tmpusrid","Email")~;
		&putspdata($sql);
	
	}

hope it helps a little

Humous

**temi** · 06-18-2007, 11:02 AM

Humous, thanks for the code, rep added. I hope OWG find it useful, I did send him and email saying I can get it sorted for him but he never resplied to my PM