Reading mail store into tables

robin
robin New Altair Community Member
edited November 5 in Community Q&A

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

 

 

Tagged:

Best Answer

  • robin
    robin New Altair Community Member
    Answer ✓

    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_Ott
    Thomas_Ott New Altair Community Member

    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? 

  • robin
    robin New Altair Community Member

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

  • robin
    robin New Altair Community Member

    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

  • robin
    robin New Altair Community Member
    Answer ✓

    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>

Categories