propellerads adcombo
Dismiss Notice
Welcome to Our Community
Wanting to join the rest of our members? Feel free to sign up today.

Ripping data from emails and placing it into a database

Discussion in 'Domains and Hosting' started by OldWelshGuy, Jun 14, 2007.

  1. OldWelshGuy

    OldWelshGuy Affiliate affiliate

    2,389
    33
    0
    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:
     
  2. temi

    temi Facilitator affiliate

    13,856
    54
    0
    You can get someone to write you a simple script that will extract the info for you.
     
  3. OldWelshGuy

    OldWelshGuy Affiliate affiliate

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

    temi Facilitator affiliate

    13,856
    54
    0
    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.
     
  5. Humous

    Humous Affiliate affiliate

    45
    0
    0
    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
     
  6. temi

    temi Facilitator affiliate

    13,856
    54
    0
    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 :(
     
  7. OldWelshGuy

    OldWelshGuy Affiliate affiliate

    2,389
    33
    0
    Thanks both.

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

    temi Facilitator affiliate

    13,856
    54
    0
    Sorry to hear you are not well James, I have been sneezing like mad too :( (Hayfever)
     

Featured Resources (View All)

adsxposed