Import set on the same tables

ninonino · ‎09-16-2018

Dear Community,

the scenario is the following: I have to import data on the same table. Generally, I perform an import uploading an excel template (raw data) every time but, as you know, a new staging table is created, and then I have lots of tables among the modules in the navigation bar.

I Suppose that I could do another import on the same table starting from an existing import set table, and then I should rearrange another transform map. Could you please confirm it or give me some suggestions?

Thanks in advance

Surjeet Singh · ‎09-17-2018

Hello,

If you wish that client / end user himself can upload data without any help of servicenow admin than creating Service Catalog / Record Producer is an alternate solution to Load Data OOB feature.

Data Import Using Service Catalog / Record Producer

1) Service Catalog - Have business rule after insert on RITM table which runs only for that particular Catalog Item.

Record Producer - Create business rule after insert on target table.

Validate file type on RP / Service Catalog itself before submission, if needed

2) Have a data source, transform map and field map created as usual. coalesce etc will be present.

3) Have scheduled import created with this data source run as administrator since it will be triggered from script. In left nav type Scheduled Imports and create new

4) In the business rule condition check whether the RITM / target table of RP has attachment then only run the business rule and have following script

Business rule condition:

current.hasAttachments();

//extra check you can have is whether it is csv or xls based on data source attachment file type

Business rule script:

//delete all the attachments before attaching a new CSV file to data source
var dataSourceSysId = ''; // sys_id of the data source you created in step 2

var gr = new GlideRecord('sys_data_source');
if(gr.get(dataSourceSysId)){  
   var attach = new GlideSysAttachment();  
   attach.deleteAll(gr);
}      
        //Attach the new CSV after deleting all the old CSV'
GlideSysAttachment.copy('sourcetable', 'sys_id', 'destinationtable', 'sys_id');

var schImp_GR = new GlideRecord('scheduled_import_set');
schImp_GR.addQuery('data_source',dataSourceSysId);
schImp_GR.query();
if(schImp_GR.next()){
   gs.executeNow(schImp_GR); // use this if it is in scoped app
   //SncTriggerSynchronizer.executeNow(schImp_GR); // if in non-scope app i.e. global
   //the above 2 lines will start the import process, etc automatically
   //Please make sure your scoped application has access to delete records from attachment table.
}

Copying / Moving an Attachment
Copying all of the attachments associated with a given record is fairly straightforward. You simply call the copy method GlideSysAttachment class, and pass in four strings:

1) The table you want to copy the attachment from (incident, change_request, etc.).
2) The sys_ID of the record you want to copy the attachment from.
3) The table that you want to copy the attachment to.
4) The sys_ID of the record you want to copy the attachment to.

This might look something like this:

GlideSysAttachment.copy(table_name, table_record_sys_id, sys_data_source, dataSource_sys_id);

Regards,

Surjeet Singh

Please mark the answer as Helpful/Correct if applicable, so that it may help others in future.

View solution in original post

ninonino · ‎09-20-2018

Thanks a lot for your reply, it was very helpful to clarify other doubts as well.

Bharat Tagad · ‎05-06-2024

Hi @Surjeet Singh ,

Thanks a lot.

We are implementing the same for our client.

We want the solution like when we attach excel file and submits record producer the transform map should start running and create records in target table.

If we repeat this process for different excel files then also it should work properly (I mean in parallel it should insert records from different excels to target table).

The issue we facing is:

When we upload a file and submit record producer the transform map starts running.
But when we want another file upload (previous transform is in progress), actually the next transform map also get started but it takes the excel file which is attached in the previous request and starts creating duplicate records in target table( records which are already inserted from first excel file).
It doesn't insert the records from the next uploaded files.
Same thing happens for next subsequent requests too but all the attachments get attached to Data source record.

Note: We are using one onComplete() Transform script to delete the attachment when transform map gets completed.

we are unable to find the cause of this.

It will be very useful if you could guide on this..

Thanks in advance

Bharat.

hiranmayeesahoo · ‎03-12-2019

I have tried copying single attachment to target record without GlideSysAttachment.copy() method as this copies all attachment.

Try below code to copy the attachment -

var dataSourceSysId = ' '; //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);
}

Thanks

Hiran

Please mark the answer as Helpful/Correct if applicable, so that it may help others in future.