Data imports from email attachments

[Brian Broadhurs]

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

[robertpolickosk]

All,

The solution to my particular needs included two Script Includes and the Inbound Email Action.

utilsEmail code is:

var utilsEmail = Class.create();

utilsEmail.prototype = {

  initialize: function(sidEmail) {

  var grEmail = new GlideRecord('sys_email');

  grEmail.get(sidEmail);

  this.email = grEmail;

  this.log = new GSLog('com.snc.email.log', 'utilsEmail');

  this.nL = '\n';

  },

  getAttachments: function() {

  var msg = 'getAttachments' + this.nL;

  var grAttachment = new GlideRecord('sys_attachment');

  try {

  msg += 'this.email.sys_id = ' + this.email.sys_id + this.nL;

  grAttachment.addQuery('table_name', 'sys_email');

  grAttachment.addQuery('table_sys_id', this.email.sys_id);

  msg += 'grAttachment.getEncodedQuery() = ' + grAttachment.getEncodedQuery() + this.nL;

  grAttachment.query();

  }

  catch (err) {

  this.log.logError('getAttachments - err.message = ' + err.message);

  }

  this.log.logDebug(msg);

  return grAttachment;

  },

  type: 'utilsEmail'

};

utilsDataSource code is:

var utilsDataSource = Class.create();

utilsDataSource.prototype = {

  initialize: function() {

  this.DataSource = new GlideRecord('sys_data_source');

  this.log = new GSLog('com.snc.email.log', 'utilsDataSource');

  this.nL = '\n';

  },

  setDataSource: function (grDataSource) {

  // Sets this.DataSource to a GlideRecord queried from the sys_data_source table

  this.DataSource = grDataSource;

  },

  createDataSource: function (strName, strTableLabel, strTableName, valType, valFormat, valRetrieveMethod) {

  // Types are:   File, JDBC, LDAP (https://chpdev.service-now.com/sys_choice_list.do?sysparm_query=nameINjavascript:getTableExtensions(...)

  //   Formats are:   CSV, CSV (tab), com.ibm.db2.jcc.DB2Driver, Excel, com.mysql.jdbc.Driver, oracle.jdbc.OracleDriver, com.microsoft.sqlserver.jdbc.SQLServerDriver, com.sybase.jdbc3.jdbc.SybDriver, XML (https://chpdev.service-now.com/sys_choice_list.do?sysparm_query=nameINjavascript%3AgetTableExtension...)

  // Retrieval Methods are:   Attachment, File, FTP, FTPS (Auth SSL), FTPS (Auth TLS), FTPS (Implicit SSL), FTPS (Implicit TLS), HTTP, HTTPS, SCP, SFTP (https://chpdev.service-now.com/sys_choice_list.do?sysparm_query=nameINjavascript:getTableExtensions(...)

  var msg = 'createDataSource' + this.nL;

  try {

  this.DataSource = new GlideRecord('sys_data_source');

  this.DataSource.initialize();

  this.DataSource.name = strName;

  this.DataSource.import_set_table_label = strTableLabel;

  this.DataSource.import_set_table_name = strTableName;

  this.DataSource.type = valType;

  this.DataSource.format = valFormat;

  this.DataSource.file_retrieval_method = valRetrieveMethod;

  this.DataSource.update();

  msg += 'DataSource inserted - ' + this.DataSource.sys_id + this.nL;

  }

  catch (err) {

  this.log.logError('createDataSource - err.message = ' + err.message);

  }

  this.log.logDebug(msg);

  return this.DataSource;

  },

  setAttachment: function (grAttachment) {

  var msg = 'setAttachment - grAttachment.sys_id = ' + grAttachment.sys_id + this.nL;

  var newAttachment = new GlideRecord('sys_attachment');

  try {

  var dataAttachmentOrg = new GlideSysAttachment();

  var binDataOrg = dataAttachmentOrg.getBytes(grAttachment);

  msg += 'binDataOrg created' + this.nL;

  var attachNew = new Attachment();

  msg += 'attachNew.write(\'sys_data_source\', ' + this.DataSource.sys_id + ', ' + grAttachment.file_name + ', ' + grAttachment.content_type + ', ' + binDataOrg + ')' + this.nL;

  newAttachment = attachNew.write('sys_data_source', this.DataSource.sys_id, grAttachment.file_name, grAttachment.content_type, binDataOrg);

  msg += 'newAttachment.sys_id = ' + newAttachment.sys_id + this.nL;

  }

  catch (err) {

  this.log.logError('setAttachment - err.message = ' + err.message);

  }

  this.log.logDebug(msg);

  return newAttachment;

  },

  setJDBCProperties: function (strMidServer, strDBName, strDBPort, strDBUserName, strDBPassword, strDBServer, strDBTableName, valQuery) {

  // Queries are:   All Rows from Table, Specific SQL (https://chpdev.service-now.com/sys_choice_list.do?sysparm_query=nameINjavascript:getTableExtensions(...)

  if (this.DataSource.type == 'JDBC') {

  try {

  this.DataSource.mid_server = strMidServer;

  this.DataSource.database_name = strDBName;

  this.DataSource.database_port = strDBPort;

  this.DataSource.jdbc_user_name = strDBUserName;

  this.DataSource.jdbc_password = strDBPassword;

  this.DataSource.jdbc_server = strDBServer;

  this.DataSource.table_name = strDBTableName;

  this.DataSource.query = valQuery;

  this.DataSource.update();

  }

  catch (err) {

  this.log.logError('setJDBCProperties - err.message = ' + err.message);

  }

  }

  else {

  this.log.logWarning('setJDBCProperties - function called when Data Source Type not JDBC');

  }

  },

  setSpecificSQL: function (strSQL) {

  if (this.DataSource.query == 'Specific SQL') {

  try {

  this.DataSource.sql_statement = strSQL;

  this.DataSource.update();

  }

  catch (err) {

  this.log.logError('setSpecificSQL - err.message = ' + err.message);

  }

  }

  else {

  this.log.logWarning('setJSpecificSQL - function called when Query not Specific SQL');

  }

  },

  setLDAPTarget: function (strLDAPTarget) {

  if (this.DataSource.type == 'LDAP') {

  try {

  this.DataSource.ldap_target = strLDAPTarget;

  this.DataSource.update();

  }

  catch (err) {

  this.log.logError('setLDAPTarget - err.message = ' + err.message);

  }

  }

  else {

  this.log.logWarning('setLDAPTarget - function called when Data Source Type not LDAP');

  }

  },

  setFilePath: function (strFilePath) {

  if (this.DataSource.file_retrieval_method == 'File') {

  try {

  this.DataSource.file_path = strFilePath;

  this.DataSource.update();

  }

  catch (err) {

  this.log.logError('setFilePath - err.message = ' + err.message);

  }

  }

  else {

  this.log.logWarning('setFilePath - function called when File Retrieval Method not File');

  }

  },

  getImportSetTransformMaps: function () {

  try {

  var grTransformMaps = new GlideRecord('sys_transform_map');

  grTransformMaps.addQuery('source_table', this.DataSource.import_set_table_name);

  grTransformMaps.addQuery('active', true);

  grTransformMaps.orderBy('order');

  grTransformMaps.query();

  this.log.logDebug('getImportSetTransformMaps - grTransformMaps.getEncodedQuery() = ' + grTransformMaps.getEncodedQuery() + '; grTransformMaps.getRowCount() = ' + grTransformMaps.getRowCount());

  return grTransformMaps;

  }

  catch (err) {

  this.log.logError('getImportSetTransformMaps - err.message = ' + err.message);

  }

  },

  processDataSource: function () {

  this.log.logNotice('processDataSource begin (' + this.DataSource.name + ')');

  var grTransformMaps = this.getImportSetTransformMaps();

  try {

  while (grTransformMaps.next()) {

  var objDSL = new DataSourceLoader();

  this.log.logNotice('processDataSource - Invoking DataSourceLoader._load(' + this.DataSource.name + ', ' + grTransformMaps.name + ', false)');

  objDSL._load(this.DataSource.name, grTransformMaps.name, false);

  }

  var now = new GlideDateTime();

  this.DataSource.last_run_datetime = now.getDisplayValue();

  this.DataSource.update();

  this.log.logNotice('processDataSource complete (' + this.DataSource.name + ')');

  }

  catch (err) {

  this.log.logError('processDataSource - err.message = ' + err.message);

  }

  },

  checkAttachment: function() {

  var boolAttachment = false;

  try {

  var newAttachment = new GlideRecord('sys_attachment');

  newAttachment.addQuery('table_name', 'sys_data_source');

  newAttachment.addQuery('table_sys_id', this.DataSource.sys_id);

  newAttachment.query();

  this.log.logDebug('checkAttachment - newAttachment.getEncodedQuery() = ' + newAttachment.getEncodedQuery() + '; newAttachment.getRowCount() = ' + newAttachment.getRowCount());

  if (newAttachment.next()) {

  boolAttachment = true;

  }

  }

  catch (err) {

  this.log.logError('checkAttachment - err.message = ' + err.message);

  }

  return boolAttachment;

  },

  setCategory: function(txtCategory) {

  this.DataSource.category = txtCategory;

  this.DataSource.update();

  },

  type: 'utilsDataSource'

};

And the Inbound Email Action code is:

gs.include('validators');

var log = new GSLog('com.snc.email.log', 'Asset Inventory Data Source Insert');

var msg = 'begin (' + sys_email.sys_id + ')' + '\n';

try {

  var now = new Date();

  msg += 'now = ' + now.toUTCString() + '\n';

  var EUtils = new utilsEmail(sys_email.sys_id);

  msg += 'EUtils instantiated ' + '\n';

  var DSUtils = new utilsDataSource();

  msg += 'DSUtils instantiated' + '\n';

  var grAttachments = EUtils.getAttachments();

  var intAttachments = grAttachments.getRowCount();

  msg += 'intAttachments = ' + intAttachments + '\n';

  var strTableLabel = 'Solarwinds Import';

  var strTableName = 'u_solarwinds_import';

  var strTransformMap = '';

  var valType = 'File';

  var valFormat = 'Excel';

  var valRetrieveMethod = 'Attachment';

  msg += 'constants set' + '\n';

  if (intAttachments != 0) {

  msg += 'First if (intAttachments != 0) passed' + '\n';

  if (intAttachments == 1) {

  msg += 'Second if (intAttachments == 1) passed' + '\n';

  now = new Date();

  msg += 'now = ' + now.toUTCString() + '\n';

  current.name = 'Asset Inventory - ' + arrAttachments[0].file_name + ' - ' + now.toUTCString();

  current.import_set_table_label = strTableLabel;

  current.import_set_table_name = strTableName;

  current.type = valType;

  current.format = valFormat;

  current.file_retrieval_method = valRetrieveMethod;

  current.insert();

  msg += 'current inserted:   ' + current.sys_id + '\n';

  }

  else {

  msg += 'Else clause' + '\n';

  while (grAttachments.next()) {

  msg += 'while loop' + '\n';

  now = new Date();

  var strName = 'Asset Inventory - ' + grAttachments.file_name + ' - ' + now.toUTCString();

  msg += 'newDataSource = DSUtils.createDataSource(' + strName + ', ' + strTableLabel + ', ' + strTableName + ', ' + valType + ', ' + valFormat + ', ' + valRetrieveMethod + ')' + '\n';

  var newDataSource = DSUtils.createDataSource(strName, strTableLabel, strTableName, valType, valFormat, valRetrieveMethod);

  msg += 'newDataSource created:   ' + newDataSource.sys_id + '\n';

  var newAttachment = DSUtils.setAttachment(grAttachments);

  msg += 'attachment inserted:   ' + newAttachment.sys_id + '\n';

  DSUtils.setCategory('Solar Winds Asset Inventory');

  }

  }

  }

}

catch (err) {

  log.logError('err.message = ' + err.message);

}

log.logDebug(msg);

[nmartinez]

This is very weird; I have this script working (provided below). But the import fails when the attachment is excel. The very same file works if I manually import the file though... If I save the file to a CSV format; it works fine with the script.

I don't understand why an Excel (2003 format) would work manually but not via script. Any one else have this problem?

[Brian Broadhurs]

What error message are you getting?

Brian

[nmartinez]

That is the odd thing; I don't get any error messages. According to my system logs; the script correctly however it the data import indicates that it skipped records. [total: 156, inserts 0, updates 0, ignored 0, skipped 156, errors 0 (0:00:00.316)]

Now this is the weird part; the data source only had 5 records; so I don't know where it's seeing 156 items. And; the system knows it's getting an excel file because I see the log that it received the email / attachment: Attachment: daily_data.xls,application/vnd.ms-excel

[Brian Broadhurs]

If you have checked the Data Source, and it definitely has an attachment with only 5 entries, but the Import Log says it processed 156 records, then it sounds like the Scheduled Import is picking up the wrong Data Source. Is it possible that the code in the email inbound action that puts the reference to the new Data Source into the Scheduled Import record - the line gr2.update(); in the sample script - isn't working properly, so that when you trigger the scheduled import, it is re-processing a different Data Source?

Can you post the source of your email inbound action script so we can have a look?

Another thought - does your spreadsheet have more than one worksheet? Your data would need to be in the first worksheet.

Brian