Reading mail store into tables

robinrobin Member Posts: 100 Guru
edited December 2018 in Help

I am new to the reading of data from e-mails into a data store. I am able to use the rapid miner processes to connect to the mail store, but then I just get a bunch of mails heaped together. 

 

I have tried various routes of parsing the mail to get the information out, but have not been able to get it right. I am missing something, please help.

 

The attached mail is an example of what the booking engine sends through on a daily basis to show who has booked for an event. I need to read these mails each day and put them into tables.

I need to read in the first name and surname, their work information and then the link to the response as follows:

 

Name Surname Title Response
Silly Lafuna Manager and co-founder. = industries https://www.eventapp.com/silly.lafuna&medium=response.email&mid=BC5554732=
6651aafG5af6ba7ceb4a!24736ffb1d81G114&bcode=BC52.1500229661.Abz5fptlf9q637=
2QyUU&n_m=response.james%40event-registration.com&lloc=img

 

I cannot post the processes here as it uses the client confidential informaiton to connect to the server as well as parse the incoming mail into the data store. 

 

Sample email:

 

Hi James,

The following people have responded to your event request.

Stan Zwane

http://www.eventapp.com/n/?stan.zwane&medium=response.email&mid=BC55547=
326651aafG5af6ba7ceb4a!24736ffb1d81G114&bcode=BC52.1500229661.Abz5fptlf9q6=
372QyUU&n_m=response.james%40event-registration.com&lloc=img

Silly Lafuna
Manager and co-founder. =
industries
https://www.eventapp.com/silly.lafuna&medium=response.email&mid=BC5554732=
6651aafG5af6ba7ceb4a!24736ffb1d81G114&bcode=BC52.1500229661.Abz5fptlf9q637=
2QyUU&n_m=response.james%40event-registration.com&lloc=img

iKhasi Adams
Mahumani high
https://www.eventapp.com/ikhasiadams&medium=response.email&mid=BC55547326651aafG=
5af6ba7ceb4a!24736ffb1d81G114&bcode=BC52.1500229661.Abz5fptlf9q6372QyUU&n_=
m=response.james%40event-registration.com&lloc=img

 


Bomib Saalie
Manager Staff
https://www.eventapp.com/bomib.saalie.161&medium=response.email&mid=BC5554732=
6651aafG5af6ba7ceb4a!24736ffb1d81G114&bcode=BC52.1500229661.Abz5fptlf9q637=
2QyUU&n_m=response.james%40event-registration.com&lloc=img

Greg Barlow
Engineer at ResponseMail Engineering
https://www.eventapp.com/greg.barlow&medium=response.email&mid=BC55547326651aaf=
G5af6ba7ceb4a!24736ffb1d81G114&bcode=BC52.1500229661.Abz5fptlf9q6372QyUU&n=
_m=response.james%40event-registration.com&lloc=img

James Baker
Works at Evans Bread
https://www.eventapp.com/pejames.baker&medium=response.email&mid=BC5554732665=
1aafG5af6ba7ceb4a!24736ffb1d81G114&bcode=BC52.1500229661.Abz5fptlf9q6372Qy=
UU&n_m=response.james%40event-registration.com&lloc=img

Hot Stix
Chicken Licken, Randburg SA
https://www.eventapp.com/profile.php&id=hot.sticks&medium=response.email&mid=BC55547326651aafG5af6ba7ceb4a!24736ffb1d81G114&bcode=BC52.1500229661.=
Abz5fptlf9q6372QyUU&n_m=response.james%40event-registration.com&lloc=img

Imran Patel
Idea Pioneers
https://www.eventapp.com/imran.patel&medium=response.email&mid=
=BC55547326651aafG5af6ba7ceb4a!24736ffb1d81G114&bcode=BC52.1500229661.Abz5f=
ptlf9q6372QyUU&n_m=response.james%40event-registration.com&lloc=img

 

Thanks,
The ResponseMail Team

 

=-=-=-=-=-=-=-=-=-=-=-=-=-=-
This message was sent to james@event-registration.com. If you don't want to =
receive these emails from ResponseMail in the future, please follow the link =
below to unsubscribe.
https://www.responsemail.com/o.php?k=334df6tghtss4
ResponseMail, Inc., Attention: Mail Support, 12 Houghton Drive, Johnessbeurg, South Africa

 

 

Best Answer

  • robinrobin Member Posts: 100 Guru
    Solution Accepted

    The problem lay in cutting the full URL using the correct Regular Expression

     

    <?xml version="1.0" encoding="UTF-8"?><process version="7.5.003">
    <operator activated="true" class="text:transform_cases" compatibility="7.5.000" expanded="true" height="68" name="Transform Cases" width="90" x="246" y="238">
    <parameter key="transform_to" value="lower case"/>
    </operator>
    </process>
    <?xml version="1.0" encoding="UTF-8"?><process version="7.5.003">
    <operator activated="true" class="text:cut_document" compatibility="7.5.000" expanded="true" height="68" name="Cut Document" width="90" x="380" y="238">
    <parameter key="query_type" value="Regular Expression"/>
    <list key="string_machting_queries"/>
    <parameter key="attribute_type" value="Nominal"/>
    <list key="regular_expression_queries">
    <parameter key="address" value="(?i)https://www.eventapp.com/[?][0-9a-z_]*[.][0-9a-z_]*[.][0-9a-z_]*[.][0-9a-z_]*"/>
    </list>
    <list key="regular_region_queries"/>
    <list key="xpath_queries"/>
    <list key="namespaces"/>
    <parameter key="ignore_CDATA" value="true"/>
    <parameter key="assume_html" value="true"/>
    <list key="index_queries"/>
    <list key="jsonpath_queries"/>
    <process expanded="true">
    <connect from_port="segment" to_port="document 1"/>
    <portSpacing port="source_segment" spacing="0"/>
    <portSpacing port="sink_document 1" spacing="0"/>
    <portSpacing port="sink_document 2" spacing="0"/>
    </process>
    </operator>
    </process>
    <?xml version="1.0" encoding="UTF-8"?><process version="7.5.003">
    <operator activated="true" class="text:combine_documents" compatibility="7.5.000" expanded="true" height="82" name="Combine Documents" width="90" x="514" y="238"/>
    </process>

Answers

  • Thomas_OttThomas_Ott RapidMiner Certified Analyst, RapidMiner Certified Expert, Member Posts: 1,761 Unicorn

    So you need to extract the name and other stuff from multiple people in one email? Will the email always have this structure and is it HTML based? 

  • robinrobin Member Posts: 100 Guru

    The mail will always have this structure and is HTML based. 

  • robinrobin Member Posts: 100 Guru

    Hi Thomas

     

    I effectivly receive email notifications on a daily basis of attendees who has used the app to confirm their attendance. I need to read this into a MySQL database. I have included an image of the file open in hex editor so that you can see the layout of the document. 

     

    Kind regards

Sign In or Register to comment.