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

Brian Broadhurs · ‎09-18-2015

If you look in your email system at the email you sent it will appear that there is only one attachment, but if the email contains images these will be converted to attachments so when the ServiceNow inbound action is executed there could be more than one attachment, and all of them will become attachments of the Data Source. Have a look at the Data Source record that is created after the email has been processed and see how many attachments that has.

I realise this may not be the issue, but it is one that has happened to me - and it took me a long time to work out what was going wrong!

Brian

ayan_c · ‎09-18-2015

Hi Brian,

I have tested it to send same mail to my another email ID and it had only one attachment.

And, the main issue in Data source is that, my data source don't even have any attachment(as I mentioned in my first question). That means the Inbound email action is creating the new data source but not able to add the attachment to that data source.

Brian Broadhurs · ‎09-18-2015

Sorry, I misread your original post. The only other suggestions I have are to check your system properties for maximum attachment size or permitted MIME types - or maybe the ACLs on the sys_attachment table.

Report Inappropriate Content · ‎05-27-2010

Hi Marc,
Thanks for the script.
I tried using the script,but i cant get the inbound action rule to copy the attachment to the data source.
Thanks

robertpolickosk · ‎06-23-2015

I have an interesting twist on this issue. The email I am getting has multiple file attachments and I need to import and transform them all. I tested a manually generated data source with multiple attachments and it only processes the first attachment. I have been trying to find a reference to the attachments collection of the email object so that I can create a script that will loop through the collection and create a data source for each attachment but cannot find a reference to such a property/attribute of the email object.

Any help is greatly appreciated.

Respectfully,

Robert