Import data via a Record producer and generate/ display a message on the screen with detailed results of how many records are inserted / ignored/updated

Adithi2 · ‎07-04-2022

Hello,

I want to import data from external file expected it would be excel file. This I have implemented using the record producer where solution enables users to submit a record producer with an attached excel document which generates a data source and triggers one or many transform maps. Below is the script used in record producer:

I have two queries here:

1. Following a successful transform, message must be displayed on the screen containing the results of their transforms like how many records are inserted / updated / ignored etc.. I do not want email notification, I just want a simple message to be displayed on the screen

2. I see this code will generate data source every time. I need to use the same data source all the time with the latest attachment.

Please assist on this.

var transformMapSysIDs= 'bedc1fb21b909d5085eb64a2604bcbea';
current.name = gs.getUserName() + " User Import at: " + new GlideDateTime();
current.import_set_table_name = 'u_mass_multimedia_device'; //name of import set table
current.file_retrieval_attachment = "Attachment";
current.type = "File";
current.format = "Excel";
current.header_row = 1;
current.sheet_number =1;
current.insert(); // Need this to load & transform directly

//Time to load excel file into import table

var loader = new GlideImportSetLoader();
var importSetRec = loader.getImportSetGr(current);
var ranload = loader.loadImportSetTable(importSetRec, current);
importSetRec.state = "loaded";
importSetRec.update();

//Time to run transform with transform map
var transformWorker = new GlideImportSetTransformerWorker(importSetRec.sys_id,transformMapSysIDs);
transformWorker.setBackground(true);
transformWorker.start();

gs.addInfoMessage("Your import file has been submitted.");

//To avoid to create another data source we abort RP insert
current.setAbortAction('true');

---------------------------------------------------------------------------------------------------------------------------------------------

Thanks

Adithi

Mohith Devatte · ‎07-04-2022

Hello,

Instead of writing insert code to insert the data source , you create a data source of your own manually and how glide the the same data source every time using glide record and pick up the latest record attached to it and get the sys id of the attachment and parse it and load it using the same piece of code which you mentioned above

OR

After the transform map runs , glide record the data source and remove all the attachments attached to it through script by gliding attachment table and then next time when you submit the record producer again attach the new one to the data source and then glide record to the data source and take that attachment and parse it and load it and then again delete it after the load .So doing this in loop through script will satisfy your second requirement and for info messages you can glide record the staging table logs in the record producer script and get the count of records which are having state as inserted and display the count in info message after the load happens

OR

if things are getting complicated use one API called glide Excel parser which is the best and easy API to integrate with Excel which would parse the data in the excel and insert it in staging table and then load it into the target table. You can do this in your record producer script only.

please refer to below link to see a sample code.

https://developer.servicenow.com/dev.do#!/reference/api/quebec/server/GEPS-getRow

Please mark this helpful if this answers your question!

Thanks

Adithi2 · ‎07-05-2022

Hi,

I created data source & updated the below script (which I found in Now community itself) in record producer. I'm not sure how the script is working & the use of Scheduled import set.

Can you please assist on this ?

var dataSourceSysId = '3025e6e81b24591485eb64a2604bcb95 '; //Data source sys_id

var attID = new GlideRecord('sys_attachment');
attID.get('sys_id'); // Attachment record sys_id

//Delete existing attachment from data source
var sourceID = new GlideRecord("sys_data_source");
if(sourceID .get(dataSourceSysId)){
var attach = new GlideSysAttachment();
attach.deleteAll(sourceID );
}

var attachRef= new GlideSysAttachment();
attachRef.writeContentStream(sourceID ,attID.getValue("file_name"), attID.getValue("content_type"), attachRef.getContentStream(attID.getUniqueValue()));

// var schImp_GR = new GlideRecord('scheduled_import_set');
// schImp_GR.addQuery('data_source',dataSourceSysId);
// schImp_GR.query();
// if(schImp_GR.next()){
// SncTriggerSynchronizer.executeNow(schImp_GR);
// }

var transformMapSysIDs= 'bedc1fb21b909d5085eb64a2604bcbea';

//Time to load excel file into import table

var loader = new GlideImportSetLoader();
var importSetRec = loader.getImportSetGr(current);
var ranload = loader.loadImportSetTable(importSetRec, current);
importSetRec.state = "loaded";
importSetRec.update();

//Time to run transform with transform map
var transformWorker = new GlideImportSetTransformerWorker(importSetRec.sys_id,transformMapSysIDs);
transformWorker.setBackground(true);
transformWorker.start();

gs.addInfoMessage("Your import file has been submitted.");

//To avoid to create another data source we abort RP insert
current.setAbortAction('true');

Thanks

Adithi

Mohith Devatte · ‎07-07-2022

Hello ,

I think you need to write the deletion code after the insertion of the record or the transform happens .

Because if you delete first and then try to transform it wont fin andy attachments to parse and load at all

so remove the deletion code from top and place it at the end of the code.These below set of lines should be placed after current.setAbortAction(true);

var attID = new GlideRecord('sys_attachment');
attID.get('sys_id'); // Attachment record sys_id

//Delete existing attachment from data source
var sourceID = new GlideRecord("sys_data_source");
if(sourceID .get(dataSourceSysId)){
var attach = new GlideSysAttachment();
attach.deleteAll(sourceID );
}

And coming to the script they are using Import Set API to load the record into staging table using transform map and using the attachments attached to the data source that we gave in the script and parse the attachment to load the data.

var attachRef= new GlideSysAttachment();
attachRef.writeContentStream(sourceID ,attID.getValue("file_name"), attID.getValue("content_type"), attachRef.getContentStream(attID.getUniqueValue()));

This part of script is getting the attachment

var transformMapSysIDs= 'bedc1fb21b909d5085eb64a2604bcbea';

//Time to load excel file into import table

var loader = new GlideImportSetLoader();
var importSetRec = loader.getImportSetGr(current);
var ranload = loader.loadImportSetTable(importSetRec, current);
importSetRec.state = "loaded";
importSetRec.update();

This part of script is loading the records into staging table in loaded state .This we call it as import set API .

var transformWorker = new GlideImportSetTransformerWorker(importSetRec.sys_id,transformMapSysIDs);
transformWorker.setBackground(true);
transformWorker.start();

gs.addInfoMessage("Your import file has been submitted.");

//To avoid to create another data source we abort RP insert
current.setAbortAction('true');

This part of the code is transforming the data into the target table

Please accept the solution if it helps you and close the thread as this might help others too

Thanks

Adithi2 · ‎07-11-2022

Hi @Mohith Devatte ,

Thank you for your response !

I tried the below script which is not creating new Data Source but copying the attachment from 'sys_attachment' and attaching it to the already created Data Source. But the next stoppage is its not getting tranformed. When I check the 'sys_import_set' table the state is 'loading'. I have tried with 2 different scripts.

Can you please help me with the transformation part ?

Record producer on table : sys_attachment

Script 1 :

var dataSourceSysId = '3025e6e81b24591485eb64a2604bcb95'; //Data source sys_id
// //Delete existing attachment from data source
var sourceID = new GlideRecord("sys_data_source");
if (sourceID.get(dataSourceSysId)) {
// var sysAttachment = new GlideRecord('sys_attachment');
// sysAttachment.addQuery('table_sys_id', '3025e6e81b24591485eb64a2604bcb95');
// sysAttachment.query();
// sysAttachment.deleteMultiple();

var attach = new GlideSysAttachment();
attach.deleteAll(sourceID );
GlideSysAttachment.copy('sys_attachment', current.sys_id, 'sys_data_source', dataSourceSysId);
}

//----------------------------------------------------------------------------------------------------------------------
var schImp_GR = new GlideRecord('scheduled_import_set');
schImp_GR.addQuery('sys_id','1c57f5b51ba8dd5485eb64a2604bcbce'); // put sys_id of the scheduled data import just now created
schImp_GR.query();
if(schImp_GR.next()){
gs.executeNow(schImp_GR);
}
//-----------------------------------------------------------------------------------------------------

Script 2:

var dataSourceSysId = '3025e6e81b24591485eb64a2604bcb95'; //Data source sys_id
// //Delete existing attachment from data source
var sourceID = new GlideRecord("sys_data_source");
if (sourceID.get(dataSourceSysId)) {

var attach = new GlideSysAttachment();
attach.deleteAll(sourceID );
GlideSysAttachment.copy('sys_attachment', current.sys_id, 'sys_data_source', dataSourceSysId);
}

var sourceGr = new GlideRecord('sys_data_source');
sourceGr.addQuery('sys_id','3025e6e81b24591485eb64a2604bcb95');
sourceGr.query();
// if we have our data source continue
if(!sourceGr.next()) {
gs.print('Did not find Data Source ' + 'Mass Multimedia device');

}

var map = new GlideRecord('sys_transform_map');
map.addQuery('sys_id', 'bedc1fb21b909d5085eb64a2604bcbea');
map.query();
// if we have a map we can now load and run the transform
if(!map.next()) {
gs.addInfoMessage('Did not find Transform map ' + 'Mass Multimedia device');

}

gs.addInfoMessage('Loading Import Set ' + 'Mass Multimedia device');
var loader = new GlideImportSetLoader();
var importSetGr = loader.getImportSetGr(sourceGr);
var ranload = loader.loadImportSetTable(importSetGr,sourceGr);
importSetGr.state = "loaded";
importSetGr.update();

if(!ranload) {
gs.addInfoMessage('Failed to load import set ' + 'Mass Multimedia device');

}

gs.addInfoMessage('Running Transform map ' + 'Mass Multimedia device');
var t = new GlideImportSetTransformerWorker();
t.setMapID(map.sys_id);
t.transformAllMaps(importSetGr);

current.setAbortAction('true');

Thank you

Adithi