Ripping data from emails and placing it into a database

OldWelshGuy · Jun 14, 2007

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 · Jun 14, 2007

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

OldWelshGuy · Jun 14, 2007

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 · Jun 14, 2007

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 · Jun 15, 2007

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 · Jun 18, 2007

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

OldWelshGuy · Jun 18, 2007

Thanks both.

temi , sorry I didn't reply, I am not feeling too well at all, so have hardly been online this weekend. :-(

temi · Jun 18, 2007

OldWelshGuy said:
Thanks both.

temi , sorry I didn't reply, I am not feeling too well at all, so have hardly been online this weekend. :-(

Sorry to hear you are not well James, I have been sneezing like mad too

(Hayfever)

The Most Active and Friendliest
Affiliate Marketing Community Online!

Ripping data from emails and placing it into a database

OldWelshGuy

New Member

temi

New Member

OldWelshGuy

New Member

temi

New Member

Humous

New Member

temi

New Member

OldWelshGuy

New Member

temi

New Member

Similar threads

The Most Active and Friendliest Affiliate Marketing Community Online!

Ripping data from emails and placing it into a database

New Member

New Member

New Member

New Member

New Member

New Member

New Member

New Member

Similar threads

The Most Active and Friendliest
Affiliate Marketing Community Online!