Data imports from email attachments

Brian Broadhurs · ‎10-16-2009

I'm trying to find a way to import data from an email attachment - this is a status update from an external supplier of the change requests that have been passed to them, and it could arrive at any time, not necessarily according to a regular schedule. My current thinking is to use an inbound email action to copy the attachment to a Data Source (sys_data_source) record, then trigger a data import using that data source. I've come across a number of issues:

1) I can't get the inbound email action to copy the attachment to the data source record. I've used the wiki entries at http://wiki.service-now.com/index.php?title=Update_Incidents_with_Attachments and http://wiki.service-now.com/index.php?title=Copy_Attachments_from_Record_to_Record as examples, but it just doesn't seem to work.

2) Once the email attachment is attached to the data source, how do I trigger the data load and transform? I could use a scheduled import to run periodically, but there might not be anything to process - I guess that might not be a problem, but is it possible for the pre-import script to abort the import based on the value of a field in the data source record that I can set in the inbound action?

3) Does a scheduled import also run the transform? If not, how can I automatically trigger that?

4) Once the import and transform is complete, I will need to remove the attachment from the data source, otherwise the number of attachments will keep rising - and presumably will get re-processed every time the import runs.

Is there a simpler way to do this?

Brian Broadhurst

marcguy · ‎10-16-2009

Hi Brian, this is from another post, so not my script and you may have already tried this:

This is an inbound email action on the sys_data_source table. Assumes the import set, import set map and import set schedule are all set up. The import set schedule needs to be way off in the future so it never runs - well not in our lifetime anyway !!

// set up the data source. The system will automatically add the attachment for us.
gs.log ('TDI001 - Setting up data source');
current.name = "test director import" + gs.nowDateTime(); //append date time to name of data source for audit
current.import_set_table_name = "u_attachment_test";
current.import_set_yable_label = "attachment test";
current.type= "File"
current.format = "CSV";
current.file_retrieval_method = "Attachment";

var myNewDataSource = current.insert();

gs.lg ('TD002 - Data source inserted with sys_id - ' + myNewDataSource);

//point the scheduled import record to the new data source

var gr2 = new GlideRecord ('scheduled_import_set');

gr2.addQuery('sys_id', '12c4a37b4a3623280013c25cca62b284'); //need to remove sys_id reference. need lookup based on name perhaps?
gr2.query();

if (gr2.next()) {
gs.log ('TDI003 - Found Scheduled Import definition');
gr2.data_source = myNewDataSource;
gr2.update();
gs.log ('TDI004 - Scheduled Import definition updated to point to data source just added');

Packages.com.snc.automation.TriggerSynchronizer.executeNow(gr2);

} else {
// add error conditions to email somebody that this has occurred
gs.log('TDI005 - ERROR - Unable to locate scheduled import definition. Please contact your system administrator');
}

gs.log ('TDI007 - Inbound email processing complete');

View solution in original post

akash32 · ‎05-18-2015

Hi mguy,

There is one thing I wanted ask. Lets say I wanted to update the data source and not create a new one with new email, how should I be updating the attachment of the data source i.e. removing the earlier attachment and adding the new one attached with email ?

Brian Broadhurs · ‎06-04-2015

Hi Akash,

the problem is going to be picking up the existing Data Source record as "current" in the inbound action. Normally this is done by matching the email watermark to tell if it refers to an existing record or to create a new one. The emails - at least in the instances where I have implemented this - are not responses to an outbound notification, so don't have a watermark, so always create a new Data Source record which becomes the "current" object in the inbound action script.

To achieve what you want to do, I think you will need to amend the inbound action to set the Target Table to a custom table of your own, so that the inbound action creates a new record in that table, with the email attachment attached to that record - then have an On Insert business rule on that table that reads in your existing Data Source, strips off any existing attachments and copies the new attachment from your custom record to the Data Source. I don't know how you would be able to tell whether the previous attachment had actually been processed or not.

I'm not sure why you want to do it this way - is it to avoid filling up the Data Source table? I actually find it useful to have all the Data Sources with all the attachments as an audit trail, so that if the person who sent the email complains that the record in ServiceNow has not been populated correctly, I can refer back to the source attachment to check the original data.

Brian

ayan_c · ‎09-18-2015

Hi mguy,

I have same requirement and I have tried your script in my inbound email action. script is running with no error but the table is not updated with the data from my csv/excel file. When I have checked the newly created Data sources, found that there is no attachment in those.

Please advice.

Thanks in advance.

Brian Broadhurs · ‎09-18-2015

Hi Ayan,

check that you are setting the import_set_table_name and import_set_table_label correctly - the import set table has to be created before you run this import.

If they are all correct, something else that has gone wrong for me in the past is that the email contains additional attachments - normally images from company logos or signatures - and these get attached to the Data Source as well as the Excel file. In order to correct this I had to define a pre-script on the Scheduled Import that examined the attachments on the Data Source and deleted any that were not Excel format.

Brian

ayan_c · ‎09-18-2015

Hi Brian - Thanks for quick reply.

I have cross checked with Import Table Name and Label and those are absolutely OK in the inbound email action code.

Also I have checked the email that I am sending, it only have one attachment that is my .csv/.xlsx file. No more attachment are there in that mail.

Thanks!