Data Import from attachment through Record Producer

[Vijay Baokar]

Hi Folks,

Currently i am working in a requirement where i need to load data from attachment in record producer to the target table using data import. Below is the script created which is creating data source but data is not getting loaded in target table .

once the RP is submitting the attachment should further get attached to data source and the transform map should run and load the data into target table.

Please suggest the best approach.

// Verify attachment is included and in correct format

var saGR = new GlideRecord("sys_attachment");

saGR.addQuery("table_sys_id",current.sys_id);

saGR.addQuery("table_name",'ZZ_YYsc_cart_item');

saGR.query();

// Do some checking here

if(!saGR.next()){

gs.addErrorMessage('You must attach a file to submit.Your import submission has been aborted');

current.setAbortAction(true);

}

else{

var sysAttachmentId = saGR.getUniqueValue();

if(saGR.getRowCount()>1)

{

gs.addErrorMessage('You may attach only one file at a time for this import wizard. Your import submission has been aborted');

current.setAbortAction(true);

}

var passedFormatCheck = false;

if(saGR.file_name.endsWith('.xls') == true || saGR.file_name.endsWith('.xlsx') == true){

passedFormatCheck = true;

}

else{

gs.addErrorMessage('Attached file should only be excel');

current.setAbortAction(true);

}

if(passedFormatCheck)

{

gs.addInfoMessage("The file looks good");

var importSetTableName = 'u_kpi_data';

var transformMapSysIds = '2d2c5c611b1aba106aad9687b04bcbda';

current.name = 'KPI Data Import';

current.import_set_table_name = importSetTableName;

current.file_retrieval_method = 'Attachment';

current.type = 'File';

current.format = 'Excel(.xlsx/.xls)';

current.header_row = 1;

current.sheet_number = 1;

saGR.table_name = 'sys_data_source';

saGR.update();

//var dataSourceID = current.insert(); //Remove current.insert(): A Record Producer automatically inserts the record after the script finishes. Manually calling .insert() creates a duplicate record.

//new GlideSysAttachment().copy(saGR, current);

//var updateAttachment = saGR.update();

var loader = new GlideImportSetLoader();

var importSetRec = loader.getImportSetGr(current);

if (importSetRec)

{

importSetRec.state = 'loaded';

importSetRec.update();

var transformWorker = new GlideImportSetTransformerWorker(importSetRec.sys_id,transformMapSysIds);

transformWorker.setBackground(true);

transformWorker.start();

gs.addInfoMessage('The KPI data is imported successfully');

}

else{

gs.addErrorMessage('Failed to create abort set record. Please check your data source');

current.setAbortAction(true);

}

}

}

[Ankur Bawiskar]

@Vijay Baokar 

I created blog for something similar many years ago, you can check that and enhance for your requirement

Data load and transform via Catalog Item 

💡 If my response helped, please mark it as correct ✅ and close the thread 🔒— this helps future readers find the solution faster! 🙏

[neetusingh]

@Vijay Baokar -  Can you try the below script -

I have cleaned up the logic to ensure the Data Source is created first, the attachment is properly copied using GlideSysAttachment, and the transformation is triggered using the correct references.

// 1. Locate the attachment in the temporary storage

var saGR = new GlideRecord("sys_attachment");

saGR.addQuery("table_sys_id", current.sys_id);

saGR.addQuery("table_name", 'ZZ_YYsc_cart_item');

saGR.query();

if (!saGR.next()) {

    gs.addErrorMessage('You must attach a file to submit. Your import submission has been aborted.');

    current.setAbortAction(true);

} else {

    // 2. Format Validation

    var fileName = saGR.getValue('file_name').toLowerCase();

    if (fileName.endsWith('.xls') || fileName.endsWith('.xlsx')) {

        var importSetTableName = 'u_kpi_data'; // Your staging table

        var transformMapSysIds = '2d2c5c611b1aba106aad9687b04bcbda'; // Your map ID

        // 3. Create a fresh Data Source record

        // We create this manually to avoid issues with the Record Producer's "current" object

        var dataSource = new GlideRecord('sys_data_source');

        dataSource.initialize();

        dataSource.name = 'KPI Import: ' + fileName + ' (' + gs.nowDateTime() + ')';

        dataSource.import_set_table_name = importSetTableName;

        dataSource.file_retrieval_method = 'Attachment';

        dataSource.type = 'File';

        dataSource.format = 'Excel';

        dataSource.header_row = 1;

        dataSource.sheet_number = 1;

        var dsSysID = dataSource.insert();

        // 4. Copy attachment from temporary storage to the new Data Source

        var gsa = new GlideSysAttachment();

        gsa.copy('ZZ_YYsc_cart_item', current.sys_id, 'sys_data_source', dsSysID);

        // 5. Trigger the Load and Transform

        var loader = new GlideImportSetLoader();

        var importSetRec = loader.getImportSetGr(dataSource);

        if (importSetRec) {

            importSetRec.state = 'loaded';

            importSetRec.update();

            // Run transformation in the background

            var transformWorker = new GlideImportSetTransformerWorker(importSetRec.sys_id, transformMapSysIds);

            transformWorker.setBackground(true);

            transformWorker.start();

            gs.addInfoMessage('File received and processing started. You will see the updated data shortly.');

        } else {

            gs.addErrorMessage('Error: The system could not create an Import Set from the attachment.');

            current.setAbortAction(true);

        }

    } else {

        gs.addErrorMessage('Invalid file format. Please upload an Excel file (.xls or .xlsx).');

        current.setAbortAction(true);

    }

}

[Tejas Adhalrao]

hi @Vijay Baokar  ,

i checked your script .

current object is  is record producer is not the any data source .

you cannot change your table names like this saGR.table_name = 'sys_data_source';

also you have used multiple abort

you can check with this script:

(function() {
  // Assume this script runs after the RP is created (current is RP record)
  // 1) find attachment on the record producer (only one allowed)
  var attGR = new GlideRecord('sys_attachment');
  attGR.addQuery('table_sys_id', current.sys_id);
  attGR.addQuery('table_name', current.getTableName()); // e.g., 'sc_cart_item' or the RP table
  attGR.query();
  if (!attGR.next()) {
    gs.addErrorMessage('You must attach a file to submit. Your import submission has been aborted.');
    current.setAbortAction(true);
    return;
  }
  // Check only one attachment
  if (attGR.getRowCount() > 1) {
    gs.addErrorMessage('Attach only one file at a time.');
    current.setAbortAction(true);
    return;
  }

  // 2) validate extension
  var fileName = attGR.file_name.toLowerCase();
  if (!(fileName.endsWith('.xls') || fileName.endsWith('.xlsx'))) {
    gs.addErrorMessage('Attached file should be Excel (.xls/.xlsx).');
    current.setAbortAction(true);
    return;
  }

  // 3) create the sys_data_source record (new data source)
  var ds = new GlideRecord('sys_data_source');
  ds.initialize();
  ds.name = 'KPI Data Import - ' + gs.nowDateTime();
  ds.import_set_table_name = 'u_kpi_data';           // target import_set table
  ds.file_retrieval_method = 'Attachment';
  ds.type = 'File';
  ds.format = 'Excel(.xlsx/.xls)';                   // keep consistent with UI
  ds.header_row = 1;
  ds.sheet_number = 1;
  var dsSysId = ds.insert();
  if (!dsSysId) {
    gs.addErrorMessage('Failed to create data source record.');
    current.setAbortAction(true);
    return;
  }

  // 4) copy the attachment to the new data source record
  try {
    var att = new GlideSysAttachment();
    att.copy(attGR.table_name.toString(), attGR.table_sys_id.toString(), 'sys_data_source', dsSysId, attGR.sys_id.toString());
  } catch (e) {
    gs.addErrorMessage('Attachment copy failed: ' + e);
    current.setAbortAction(true);
    return;
  }

  // 5) load import set and run transform
  var loader = new GlideImportSetLoader();
  var importSetRec = loader.getImportSetGr(ds);
  if (!importSetRec) {
    gs.addErrorMessage('Failed to create import set record. Please check your data source.');
    current.setAbortAction(true);
    return;
  }

  // mark loaded (optional, but good for clarity)
  importSetRec.state = 'loaded';
  importSetRec.update();

  // 6) run transform map(s)
  // transformMapSysId can be a single sys_id or comma-separated string
  var transformMapSysId = '2d2c5c611b1aba106aad9687b04bcbda'; // adjust
  var worker = new GlideImportSetTransformerWorker(importSetRec.sys_id.toString(), transformMapSysId);
  worker.setBackground(true);
  worker.start();

  gs.addInfoMessage('The KPI data import has been submitted and transform started.');
})();