Issues with an Inbound Action creating a Data Source when there's an image attached to the email

[Wayne Richmond]

Hi guys. I've created an Inbound Action to process an attached Excel document and load the data into a custom table via a Transform Map. This works fine unless there is an image in the email, e.g, an email signature. When this happens, the importer tries to read the image and fails, meaning the Import Set doesn't get updated. It seems to ignore the Excel file.

As a work around, I've created a BR on the sys_attachment table to stop it inserting attachments with a type contain "image" on the sys_data_source table. However, I thought there would be a better way to do this somewhere else but I can't see where. Here is the script in the Inbound Action:

(function runAction( /*GlideRecord*/ current, /*GlideRecord*/ event, /*EmailWrapper*/ email, /*ScopedEmailLogger*/ logger, /*EmailClassifier*/ classifier) {

    var importSetTableName = "name_of_my_import_set_table";
    var transformMapIDs = "073eaef61b97d614942bfc4cd34bcb3e";
    var applicatonScope = "Global";
	var gdt = new GlideDateTime();

    // Create the datasource record
    current.name = "File import: My file name " + gdt;
    current.import_set_table_name = importSetTableName;
    current.file_retrieval_method = "Attachment";
    current.type = "File";
    current.format = "Excel";
    current.header_row = 1;
    current.sheet_number = 1;
    current.sys_package.setDisplayValue(applicatonScope);
    current.sys_scope.setDisplayValue(applicatonScope);
    var dataSourceID = current.insert();

    new global.EmailFileImportUtils().scheduleImport(dataSourceID, transformMapIDs);

})(current, event, email, logger, classifier);

 I've reviewed the EmailFileImportUtils Script Include but I can't see where it's doing the import of the file itself. I assume that's happening via the GlideImportSetLoader API but I cannot modify this.

Any better ideas than my work around?

[Ankur Bawiskar]

@Wayne Richmond 

how is the file copied to data source so that the transform begins?

Please share the complete script include for "EmailFileImportUtils"

If my response helped please mark it correct and close the thread so that it benefits future readers.

[Wayne Richmond]

Hi Ankur. Sorry, I never got notified of your reply!

Here is the Script Include:

var EmailFileImportUtils = Class.create();
EmailFileImportUtils.prototype = {
	initialize: function() {
	},
	
	scheduleImport: function(dataSourceID, transformMapIDs) {
		/*
 		* Create scheduled job to process import
 		*
 		* The inbound email action will generate an import data source, however the attachment isn't copied to the data source until
 		* after the insert of the record.   The code below will create a scheduled job to process the import 30 seconds later
 		* so that attachment has time to be copied to the data source from the email.
 		*/
		
		var schRec = new GlideRecord("sys_trigger");
		schRec.name = "Load Data Source: " + dataSourceID;
		schRec.trigger_type = 0;   // Run Once
		schRec.script = "new global.EmailFileImportUtils().loadImportSet('" + dataSourceID + "', '" + transformMapIDs + "')";
		
		var nextAction = new GlideDateTime();
		nextAction.addSeconds(90);   // 30 seconds should be enough time however this can be changed.
		schRec.next_action = nextAction;
		schRec.insert();
	},
	
	loadImportSet: function(dataSourceID, transformMapIDs) {
		// Get Datasource Record
		var dataSource = new GlideRecord("sys_data_source");
		dataSource.get(dataSourceID);
		
		// If CSV and header isn't on row 1, recreate attachment with empty rows removed
		if (dataSource.getValue("format") == "CSV" && dataSource.getValue("header_row") > 1) {
			var attachmentRec = new GlideRecord("sys_attachment");
			attachmentRec.addQuery("table_sys_id", dataSource.getValue("sys_id"));
			attachmentRec.query();
			if (attachmentRec.next()) {
				var oldAttachmentID = attachmentRec.getValue("sys_id");
				var inputStream = new GlideSysAttachment().getContentStream(oldAttachmentID);
				var textReader = new GlideTextReader(inputStream);
				var ln = " ";
				var newLine = "";
				
				var lineCounter = 0;
				var headerRow = parseInt(dataSource.getValue("header_row"));
				while((ln = textReader.readLine()) != null) {
					lineCounter += 1;
					if (lineCounter < headerRow) {
						continue;
					}
					
					if (ln.length > 1) {
						newLine += ln + "\n";
					}
				}
				new GlideSysAttachment().write(dataSource, 'Changed ' + attachmentRec.getValue('file_name'), "text/csv", newLine);
			}
		}		
		
		// Process data source file
		var loader = new GlideImportSetLoader();
		var importSetRec = loader.getImportSetGr(dataSource);
		var ranload = loader.loadImportSetTable(importSetRec, dataSource);
		importSetRec.state = "loaded";
		importSetRec.update();
		
		// Transform import set
		var transformWorker = new GlideImportSetTransformerWorker(importSetRec.sys_id, transformMapIDs);
		transformWorker.setBackground(true);
		transformWorker.start();
	},
	
	type: 'EmailFileImportUtils'
	
};