Parsing email body in an inbound email action

Maheshwar1
Tera Contributor

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...

6 REPLIES 6

Ankur Bawiskar
Tera Patron
Tera Patron

@Maheshwar1 

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?

Regards,
Ankur
✨ Certified Technical Architect  ||  ✨ 9x ServiceNow MVP  ||  ✨ ServiceNow Community Leader

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.

Vasantharajan N
Giga Sage
Giga Sage

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

newhand
Mega Sage

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

Please mark my answer as correct and helpful based on Impact.