Script to create an import set, and run the tranform, when the Import Set table already exists?

[thomaskennedy]

I need to populate an import set through script, mark the rows up with additional values looked up from elsewhere, and then run a transform to push the result into the target table. (Of course I could script all of this, but that's what the map is for.)

The data source, the import set table and the transform map already exist, as these were created when I was planning to have the transform map do everything. I'm looking over the scripting options in Utah, and I see classes like GlideImportSetTable, and instructions like this:

• To execute an Import Set transform:
• Create an Import Set table using the GlideImportSetTable API.
• Create a Transform Map using the GlideImportSetTransformMap API.
• Create or use an existing Import Set using the GlideRecord API.
• Execute an Import Set Transform using the GlideImportSetTransformer API.

But I already have all that stuff. I want to create an import set, populate my import set rows, associate them with the import set, and then hand the import set off to the transformer. But I don't find any code samples of any one doing that, except through the methods shown above. Is there a reason I should not do all that through GlideRecord, by  creating a new row in sys_import_set, and so on? It seems strange to create a new import set table and a new map every time I run this import.

[Bharath24]

@Niklas Peterson 
I want to load data from an excel attached in the data source using script. Is it possible? Is there any api similar to [GlideImportSetLoader] which can run on scoped application as well?

Thanks in advance.

[thomaskennedy]

Something like this:

// First, get the data source sys_id
var gr_ds = new GlideRecord("sys_data_source");
gr_ds.addQuery("name", "DatasourceName");
gr_ds.addQuery("sys_scope", "your_scope_sys_id");
gr_ds.setLimit(1);
gr_ds.query();
gr_ds.next();
var ds_sys_id = gr_ds.getValue("sys_id");

// Get the first attachment on that row
// Not shown: check that there is an attachment, check name, check data type, get only most recent attachment etc
var gr_att = new GlideRecord("sys_attachment");
gr_att.addQuery("table_name", "sys_email");
gr_att.addQuery("table_sys_id", email_sys_id);
gr_att.setLimit(1);
gr_att.query();
gr_att.next();
var attachment_sys_id = gr_att.getValue("sys_id");

// assuming that attachment was an xlsx...
var parser = new sn_impex.GlideExcelParser();
var attachment = new GlideSysAttachment();
var attachmentStream = attachment.getContentStream(attachment_sys_id);
parser.parse(attachmentStream);

// we assume data starts at row 2
var row_number = 1;

// Move to the first data row
while (parser.next()) {
  var row = parser.getRow();

  // increment the rowNumber so the first value is 2
 row_number++;

 var column_value = row["your column_name"] || "";
 // ...
}

[RiteshSwarnakar]

If there are multiple Transform Maps for the same Import Source Table you can use the below code:

var impSet = new GlideRecord('sys_import_set'); 
impSet.initialize();
impSet.mode = 'asynchronous';
impSet.table_name = 'u_import_tablename';
impSet.state = 'loading';
impSet.insert();

            //creating record in import table
	var impRec = new GlideRecord("u_import_tablename");
	impRec.u_id = 'testID';   
        impRec.u_name = 'testName';  
	impRec.sys_import_set = impSet.sys_id;
	impRec.insert();
					
				//runs all transform maps for that source table
				var mapsList = this.getMap(impSet.table_name);
				var t = new GlideImportSetTransformerWorker(impSet.sys_id, mapsList);
				t.setProgressName("Transforming: " + impSet.number);
				t.setBackground(true);
				t.start();
	


		function getMap(sTable){
			var mapGR = new GlideRecord("sys_transform_map");
			mapGR.addQuery("source_table", sTable);
			mapGR.addActiveQuery();
			mapGR.query();
			var mapsList = [];
			while(mapGR.next())
			mapsList.push(mapGR.getUniqueValue());
			return mapsList.join();
		}