Parse the incoming dynamic html email content and populate them in the fields

Hello Every One,

I am having difficulties in parsing the incoming email alert. I tried using email services and apex behind to parse the content. The incoming email alert is as follows:

Data Integrity Reporting

Description:

The following is the user's data.

Result:

ContactName ContactPhone ContractID Description CreatedDT ContractNumber

Testname 1 123-456-7890 132324 test1 05/13/2013 CT-1442

Testname 2 124-421-4124 124242 test2 05/14/2013 CT-1344

Testname 3 421-323-3242 421442 test3 05/15/2013 CT-1332

Additional Info:

Alarm execution took 1 seconds

But I am dealing with a different scenario here. In the above html email, the rows under "Result" are not constant and it varies when the incoming is sent. For Example, the incoming html email can contain 1 row when the email is received to the salesforce or zero rows or 3 rows and so on. The columns are constant.

Can any one suggest me how to process this kind of scenario. I would really appreciate you.

Thanks

May 23, 2013
·
Like
0
·
Follow
0

Best Answer chosen by Admin (Salesforce Developers)

sfdcfox
I managed to shoehorn your code into a working prototype. I had to add an character entity definition to the XML, and I had to further strip out hr and br elements that were problematic. There's still some parsing errors, but I decided to ignore them for now because they are irrelevant for the demonstration.

Usage:

String[] tableRows = Scanner.scan(sourceText);

Result (from test data):

ContactName;ContactEmail;ContactPhone;ContactID;Description;CreateDT;ContractNumber; testname1;test1@gmail.com;1323737698;41452;test 1 desc;5/14/2013 8:58:42 PM;CT-285; testname2;testname2@gmail.com;424-962-7311;14423;test2 desc;5/13/2013 9:01:21 PM;CT-2858;

You could use multidimensional array, but I was lazy and just made it ordinary delimited strings.

Edit: And the source code...

@istest class ScannerTest{ @istest static void test() { String[] results = Scanner.scan('<html xmlns:v="urn:schemas-microsoft-com:vml" xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:w="urn:schemas-microsoft-com:office:word" xmlns:m="http://schemas.microsoft.com/office/2004/12/omml" xmlns="http://www.w3.org/TR/REC-html40"><head><meta http-equiv="Content-Type" content="text/html; charset=us-ascii"><meta name="Generator" content="Microsoft Word 14 (filtered medium)"><style></style></head><body lang="EN-US" link="blue" vlink="purple"><div class="WordSection1"><h3 align="center" style="text-align:center">Data Integrity Reporting<o:p></o:p></h3>Description: The following is the user\'s data Result: <o:p></o:p><table class="MsoNormalTable" border="1" cellpadding="0"><tbody><tr><td style="background:orange;padding:.75pt .75pt .75pt .75pt">ContactName<o:p></o:p></td><td style="background:orange;padding:.75pt .75pt .75pt .75pt">ContactEmail<o:p></o:p></td><td style="background:orange;padding:.75pt .75pt .75pt .75pt">ContactPhone<o:p></o:p></td><td style="background:orange;padding:.75pt .75pt .75pt .75pt">ContactID<o:p></o:p></td><td style="background:orange;padding:.75pt .75pt .75pt .75pt">Description<o:p></o:p></td><td style="background:orange;padding:.75pt .75pt .75pt .75pt">CreateDT<o:p></o:p></td><td style="background:orange;padding:.75pt .75pt .75pt .75pt">ContractNumber<o:p></o:p></td></tr><tr><td style="background:lightblue;padding:.75pt .75pt .75pt .75pt">testname1<o:p></o:p></td><td style="background:lightblue;padding:.75pt .75pt .75pt .75pt"><a href="mailto:test1@gmail.com">test1@gmail.com</a><o:p></o:p></td><td style="background:lightblue;padding:.75pt .75pt .75pt .75pt">1323737698<o:p></o:p></td><td style="background:lightblue;padding:.75pt .75pt .75pt .75pt">41452<o:p></o:p></td><td style="background:lightblue;padding:.75pt .75pt .75pt .75pt">test 1 desc<o:p></o:p></td><td style="background:lightblue;padding:.75pt .75pt .75pt .75pt">5/14/2013 8:58:42 PM<o:p></o:p></td><td style="background:lightblue;padding:.75pt .75pt .75pt .75pt">CT-285<o:p></o:p></td></tr><tr><td style="background:lightblue;padding:.75pt .75pt .75pt .75pt">testname2<o:p></o:p></td><td style="background:lightblue;padding:.75pt .75pt .75pt .75pt"><a href="mailto:testname2@gmail.com">testname2@gmail.com</a><o:p></o:p></td><td style="background:lightblue;padding:.75pt .75pt .75pt .75pt">424-962-7311<o:p></o:p></td><td style="background:lightblue;padding:.75pt .75pt .75pt .75pt">14423<o:p></o:p></td><td style="background:lightblue;padding:.75pt .75pt .75pt .75pt">test2 desc<o:p></o:p></td><td style="background:lightblue;padding:.75pt .75pt .75pt .75pt">5/13/2013 9:01:21 PM<o:p></o:p></td><td style="background:lightblue;padding:.75pt .75pt .75pt .75pt">CT-2858<o:p></o:p></td></tr></tbody></table> Additional Info: Alarm execution took 1 seconds <o:p></o:p><o:p> </o:p></div></body></html>'); System.debug(results); } }

public class Scanner { public static String[] scan(String source) { string src='<!DOCTYPE html [<!ENTITY nbsp " ">]>'+source; String[] values = new String[0]; src=src.replaceAll('<(br|hr)>',''); System.debug(System.loggingLevel.Error,src); XmlStreamReader r = new XmlStreamReader(src); Integer retry = 0; Boolean inRow = false, inCol = false; while(r.hasNext() && retry < 3) { try { r.next(); retry = 0; if(r.geteventtype()==xmltag.start_element && r.getlocalname()=='tr') { values.add(''); inRow = true; } if(r.geteventtype()==xmltag.end_element && r.getlocalname()=='tr') { inrow = incol = false; } if(r.geteventtype()==xmltag.start_element && r.getlocalname()=='td') { incol = true; } if(r.geteventtype()==xmltag.end_element && r.getlocalname()=='td') { incol = false; if(!values.isempty()) values[values.size()-1]+=';'; } if(inRow&&inCol&&r.geteventtype()==xmltag.characters) { values[values.size()-1]+=r.gettext(); } } catch(exception e) { retry++; } } return values; } }

I apologize for the "brute force" appearance of this code, I just don't have a lot of time on my hands for code this size. This sample should work well for you, though. See the documentation on XmlStreamReader for more details.

May 28, 2013
·
Like
0
·
Dislike
0

sfdcfox
I'd want a look at the raw HTML, but basically you should be able to use the XMLStreamReader class in Apex Code to parse the HTML. Check out the XmlStreamReader class in the docs.

May 24, 2013
·
Like
0
·
Dislike
0

lucky41
Hi sfdcfox,

Thanks for the reply. The raw html looks like this. The values in the <table> varies. The <table> can contain zero rows or 1 row or 2 rows and so on.

<html xmlns:v="urn:schemas-microsoft-com:vml" xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:w="urn:schemas-microsoft-com:office:word" xmlns:m="http://schemas.microsoft.com/office/2004/12/omml" xmlns="http://www.w3.org/TR/REC-html40">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=us-ascii">
<meta name="Generator" content="Microsoft Word 14 (filtered medium)">
<style></style>
</head>
<body lang="EN-US" link="blue" vlink="purple">
<div class="WordSection1">
<h3 align="center" style="text-align:center">Data Integrity Reporting<o:p></o:p></h3>
Description: 
The following is the user's data
 
 
Result: <o:p></o:p>
<table class="MsoNormalTable" border="1" cellpadding="0">
<tbody>
<tr>
<td style="background:orange;padding:.75pt .75pt .75pt .75pt">
ContactName<o:p></o:p>
</td>
<td style="background:orange;padding:.75pt .75pt .75pt .75pt">
ContactEmail<o:p></o:p>
</td>
<td style="background:orange;padding:.75pt .75pt .75pt .75pt">
ContactPhone<o:p></o:p>
</td>
<td style="background:orange;padding:.75pt .75pt .75pt .75pt">
ContactID<o:p></o:p>
</td>
<td style="background:orange;padding:.75pt .75pt .75pt .75pt">
Description<o:p></o:p>
</td>
<td style="background:orange;padding:.75pt .75pt .75pt .75pt">
CreateDT<o:p></o:p>
</td>
<td style="background:orange;padding:.75pt .75pt .75pt .75pt">
ContractNumber<o:p></o:p>
</td>
</tr>
<tr>
<td style="background:lightblue;padding:.75pt .75pt .75pt .75pt">
testname1<o:p></o:p>
</td>
<td style="background:lightblue;padding:.75pt .75pt .75pt .75pt">
<a href="mailto:test1@gmail.com">test1@gmail.com</a><o:p></o:p>
</td>
<td style="background:lightblue;padding:.75pt .75pt .75pt .75pt">
1323737698<o:p></o:p>
</td>
<td style="background:lightblue;padding:.75pt .75pt .75pt .75pt">
41452<o:p></o:p>
</td>
<td style="background:lightblue;padding:.75pt .75pt .75pt .75pt">
test 1 desc<o:p></o:p>
</td>
<td style="background:lightblue;padding:.75pt .75pt .75pt .75pt">
5/14/2013 8:58:42 PM<o:p></o:p>
</td>
<td style="background:lightblue;padding:.75pt .75pt .75pt .75pt">
CT-285<o:p></o:p>
</td>
</tr>
<tr>
<td style="background:lightblue;padding:.75pt .75pt .75pt .75pt">
testname2<o:p></o:p>
</td>
<td style="background:lightblue;padding:.75pt .75pt .75pt .75pt">
<a href="mailto:testname2@gmail.com">testname2@gmail.com</a><o:p></o:p>
</td>
<td style="background:lightblue;padding:.75pt .75pt .75pt .75pt">
424-962-7311<o:p></o:p>
</td>
<td style="background:lightblue;padding:.75pt .75pt .75pt .75pt">
14423<o:p></o:p>
</td>
<td style="background:lightblue;padding:.75pt .75pt .75pt .75pt">
test2 desc<o:p></o:p>
</td>
<td style="background:lightblue;padding:.75pt .75pt .75pt .75pt">
5/13/2013 9:01:21 PM<o:p></o:p>
</td>
<td style="background:lightblue;padding:.75pt .75pt .75pt .75pt">
CT-2858<o:p></o:p>
</td>
</tr>
</tbody>
</table>
 
Additional Info: 
Alarm execution took 1 seconds <o:p></o:p>
<o:p> </o:p>
</div>
</body>
</html>

Thanks

May 24, 2013
·
Like
0
·
Dislike
0

sfdcfox
This code is made non-trivial due to the extra fluff that Microsoft adds while composing this email. Regardless, basically, you'd want to note when you first encounter a TR element, loop through each TD, stripping out the text from the node, then adding this to a variable to be stored. To complicate matters, this is not well-formed XML (because of the non-closing meta tags), so additional work is required stripping out invalid parts.

I don't have time to write up a proper example now, but I'll see if I can't do something this afternoon.

May 24, 2013
·
Like
0
·
Dislike
0

lucky41
Hi sfdcfox,

An example on this would be really helpful for me.

Thanks.

May 28, 2013
·
Like
0
·
Dislike
0

This was selected as the best answer

lucky41
Hi sfdcfox,

Thanks for the help. I have made few changes to the code that you have provided and fulfilled my purpose.

Thanks again for your help.

June 4, 2013
·
Like
0
·
Dislike
0

You need to sign in to do that.

Need an account? Sign Up

Have an account? Sign In

Dismiss

Browse by Topic

Welcome to Support!

Show

sorted by

Parse the incoming dynamic html email content and populate them in the fields

All Answers

You need to sign in to do that.