can we load attached file to any table

anshul_jain25 · ‎08-07-2018

I want to load data of attachment from RITM to a table.

Is it possible.

Basically it means i want to load data in service now table when raised an RITM and attached any file.

Aman Gurram · ‎08-07-2018

Okay. So Assuming you know how to setup Data Sources, Import Sets and Transform Maps. Below are the steps you can follow to implement this.

1. Create your Data Source of Type File and Format is CSV. (Configure other parameters as needed)

2. Create your Transform Map.

3. Now that you have a Data Source and a Transform Map setup.

use the below function Calls in a UI Action/Business Rule to Kick off the Process

var ImportUtilObj = new Import_Util();
var areAttachmentsValid = ImportUtilObj.validateAttachments(current.getTableName(),current.sys_id);
if(areAttachmentsValid)
	{
	var dataSource=ImportUtilObj.getDataSourceByName('your Data Source name');
	var transformMap= ImportUtilObj.getTransformMapByName('your Transform Map name');
	ImportUtilObj.loadAttachmentforDataSource(dataSource,current.getTableName(),current.sys_id);
	var importSetRec=ImportUtilObj.importAndTransformCSV(dataSource,transformMap);
}

4. Step 3 is dependent on the below Script Include Named: Import_Util

var Import_Util = Class.create();
Import_Util.prototype = {

    initialize: function() {},
    getDataSourceByName: function(dataSourceName) {

        //get the DataSource
        var dataSource = new GlideRecord('sys_data_source');
        dataSource.get('name', dataSourceName);
        return dataSource;

    },

    loadAttachmentforDataSource: function(dataSource, table_name, table_sys_id) {

        //delete all the attachments before attaching a new CSV file
        var attach = new GlideSysAttachment();
        attach.deleteAll(dataSource);
        //Attach the new CSV after deleting all the old CSV'
        GlideSysAttachment.copy(table_name, table_sys_id, 'sys_data_source', dataSource.sys_id);

    },

    importAndTransformCSV: function(dataSource, transformMap) {

        // 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, transformMap.sys_id);
        transformWorker.setBackground(true); // if you want to run it in Foreground then set it to False
        transformWorker.start();
        return importSetRec;

    },

    getTransformMapByName: function(transformMapName) {

        //get the DataSource
        var transformMap = new GlideRecord('sys_transform_map');
        transformMap.get('name', transformMapName);
        return transformMap;

    },
    validateAttachments: function(table_name, table_sys_id) {
        var attachment = new GlideRecord('sys_attachment');
        attachment.addQuery('table_name', table_name);
        attachment.addQuery('table_sys_id', table_sys_id);
        attachment.query();

        var multiple_attchmentCheck = attachment.getRowCount();
        if (multiple_attchmentCheck > 1) {
            gs.addErrorMessage('Please attach only one CSV file, multiple attachments are not supported');
            attachment.deleteMultiple();
            return false;
        } else if (multiple_attchmentCheck == 0) {
            gs.addErrorMessage('Please attach atleast one CSV file');
            return false;
        } else
            return true;
    },
    type: 'Import_Util'
};

View solution in original post

bardakov · ‎08-07-2018

You can copy attachment form RITM to to record in another table via script

GlideSysAttachment().copy(String sourceTable, String sourceID, String targetTable, String targetID)

https://developer.servicenow.com/app.do#!/api_doc?v=jakarta&id=r_SGSA-copy_S_S_S_S

RAHUL Khanna1 · ‎08-07-2018

The above will contain rdundant data .....you can go with related list .....

Aman Gurram · ‎08-07-2018

Correct me If am wrong, this is what I understood from what you posted.

For a RITM, if there is any file attached, you want to load the contents of the attached file on RITM to a Table.

If this is correct, then it really depends upon the type of attachment. If its a CSV we can parse the data in CSV file and use DataSources and Transform maps to populate Target Tables.

What type of attachment are you talking about? Is it a csv/txt/pdf/execl ?

Please let us know.

anshul_jain25 · ‎08-07-2018

Yes it will be a CSV or Excel.