Parsing email body in an inbound email action
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎11-24-2022 08:09 PM - edited ‎11-24-2022 08:11 PM
I need to parse data from email body and push it to one of the reporting table.
Receiving an automated email everyday containing details against Server Backup. The report is being received as part of the email body and I need to parse the data and post it to the right table.
The email body is as follows:
ServiceID,Name,Type,Schedule,Group,Retention,"Start Time","End Time",Size,Outcome,Ticket MIBRS29531106,mel-cm-svr1,"Windows File System",10pm,T_Solutions_3y_10pm,"3 Years","2022-11-24 22:00:11","2022-11-24 22:17:08",650,Completed, MIBRS29531104,mel-fs3,"Windows File System",10pm,T_Solutions_3y_10pm,"3 Years","2022-11-24 22:00:10","2022-11-25 01:47:52",3620,Completed, MIBRS29531103,mel-exp-fs1,"Windows File System",10pm,T_Solutions_3y_10pm,"3 Years","2022-11-24 22:00:11","2022-11-24 22:13:17",140,Completed, ......
Please help with this...
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎11-24-2022 10:03 PM
the email body you shared cannot be parsed easily.
you need to perform string manipulation to get value for each and accordingly create record
what did you start with?
Ankur
✨ Certified Technical Architect || ✨ 9x ServiceNow MVP || ✨ ServiceNow Community Leader
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎11-27-2022 03:24 PM
Thanks Ankur, need help with parsing the email body. I am unable to pickup the email body content and pass it to the script include.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎11-24-2022 10:36 PM
Try use the below code snippet which will generate JSON for each row/line item and push it to array.
You can try running this code in background script to check.
Feel free to update the script as per your requirement.
var t = 'ServiceID,Name,Type,Schedule,Group,Retention,"Start Time","End Time",Size,Outcome,Ticket MIBRS29531106,mel-cm-svr1,"Windows File System",10pm,T_Solutions_3y_10pm,"3 Years","2022-11-24 22:00:11","2022-11-24 22:17:08",650,Completed, MIBRS29531104,mel-fs3,"Windows File System",10pm,T_Solutions_3y_10pm,"3 Years","2022-11-24 22:00:10","2022-11-25 01:47:52",3620,Completed, MIBRS29531103,mel-exp-fs1,"Windows File System",10pm,T_Solutions_3y_10pm,"3 Years","2022-11-24 22:00:11","2022-11-24 22:13:17",140,Completed,';
var headwithval = t.split(",Ticket ");
var head = headwithval[0].split(",");
var hl = 0;
var val = headwithval[1].trim(",").split(',');
var res = {};
var numbRe = Math.round(val.length / head.length);
var finalRes = [];
for (var j = 0; j < numbRe; j++) {
var m = 0;
if (j > 0) {
m = head.length * j;
}
for (var i = m; i < head.length * (j + 1); i++) {
if (hl <= head.length - 1) {
res[head[hl]] = val[i];
hl++;
}
if (hl == head.length) {
hl = 0;
gs.print(JSON.stringify(res));
finalRes.push(res);
res = {};
}
}
}
gs.print(finalRes.length);
Thanks & Regards,
Vasanth
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎11-24-2022 11:40 PM
HI @Maheshwar1
Maybe the below steps can help you .
1. create a flow which monitors the inbound email table(created)
2. get imformation from the email body
(I think this step is the most hard to do while the email is HTML type...
However, I think it can finally be achieved through JavaScript.)
3. insert the information to your business table