ERROR in loading headers from the xlsx data source

[Melissa Berry]

Need help!

We are using a catalog item with a workflow run script to copy the attachment from the RITM to the data source and then triggering a data source to do the transform of data.

The data source is triggered by scheduled data import. We specified the header row is 1 and Sheet is 1 in data source. 

Getting this error when transform runs: java.io.IOException: error in loading headers from the xlsx data source: No valid entries or contents found, this is not a valid OOXML (Office Open XML) file ( scheduled import job: Asset Data)

[SanjivMeher]

Did you try downloading a template of the table where you are loading the data using the Create Excel Template button?

You can use that template to load data.

[Melissa Berry]

Yes. we are able to load the data using that method, but the requirement is to be able to load the data from attachment on the catalog item, so we set it up to copy the attachment to the data source and then trigger the data source by a scheduled import

Data Source

Run Script in workflow to copy attachment from RITM to Data Source

Scheduled Import

Run Script in workflow to Trigger Transform

[SanjivMeher]

This is what I use, which you can try

// Set the following variables with the name of your import set table and task id column
var importSetTableName = "sn_audit_import_rfi";
var transformMapIDs = "2a6d5c5adba1dcd00c40a4cb0b96197b"; //Use a comma to specify multiple transform maps

// Setup data source for attachment
var ds = new GlideRecord('sys_data_source');
ds.name = "Record Producer Import by " + gs.getUserName();
ds.import_set_table_name = importSetTableName;
ds.file_retrieval_method = "Attachment";
ds.type = "File";
ds.format = "Excel";
ds.header_row = 1;
ds.sheet_number = 1;
if (producer.new_exam == 'No') {
    ds.instance_name = producer.exam;
} else {
    ds.instance_name = current.sys_id;
}

var dsid = ds.insert();
GlideSysAttachment.copy('sn_audit_engagement', current.sys_id, 'sys_data_source', dsid);
/*
 * Schedule Load of Attachment
 *
 * This 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.   Scheduling the import to happen 30 seconds later so that attachment has time to be copied.
 */
var createdBy = gs.getUserID();
new global.FileImportUtils().scheduleImport(dsid, transformMapIDs, createdBy);

And script include FileImportUtils

var FileImportUtils = Class.create();
FileImportUtils.prototype = {
	initialize: function() {
	},
	scheduleImport: function(dataSourceID, transformMapIDs, createdBy) {
		/*
 		* Create scheduled job to process import because these APIs are not available in scoped applications
 		*/
		
		var schRec = new GlideRecord("sys_trigger");
		schRec.name = "Load Data Source: " + dataSourceID;
		schRec.trigger_type = 0;   // Run Once
		schRec.script = "new global.FileImportUtils().loadImportSet('" + dataSourceID + "', '" + transformMapIDs + "', '" + createdBy + "')";
		
		var nextAction = new GlideDateTime();
		nextAction.addSeconds(1);
		schRec.next_action = nextAction;
		schRec.insert();
	},
	loadImportSet: function(dataSourceID, transformMapIDs, createdBy) {
		// Impersonate the user so that records show created by user
		new GlideImpersonate().impersonate(createdBy);
		
		// Get Datasource Record
		var dataSource = new GlideRecord("sys_data_source");
		dataSource.get(dataSourceID);
		
		// 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: 'FileImportUtils'
};

[Melissa Berry · Accepted solution]

TY for the info. I might look into doing it your way. It looks like yours will work with multiple where as mine is only single.

I found my issue. I had quotes around current.sys_id...achh!!