Import excel data into fields in custom table

[Anna_Servicenow]

I have the below requirement,

> We have a custom form (table) IPM task. I have created a UI action 'Import data'. Now when user attaches a excel with column 'case id' and 'resolved by'(column constant and number of rows keeps changing), the data in the excel should be exported to the field in the form like below automatically. How to achieve this?

[AshishKM]

Hi @Anna_Servicenow , 

We got it, but what is the issue here, have you implemented some code or logic to achieve this. 

If yes, please share. 

-Thanks,

AshishKM

[Anna_Servicenow]

We have not implemented this , Trying to do this without using a transform map.

We are trying to do this with a UI action or by creating a variable of type attachment and on change of attachment , trying to trigger GlideExcelParser. 

below is returning value in scripts backgroud with value in 2 column

var attachment = new GlideSysAttachment();
var getdata = new GlideRecord('sys_attachment');
getdata.addQuery('table_sys_id','eae02aa5c3221210462c1dd1b40131b7');
getdata.addQuery('table_name','ZZ_YYu_bpm');
getdata.query();
if(getdata.next()){

var attachmentSysID = getdata.sys_id.toString();
var attachmentStream = attachment.getContentStream(attachmentSysID);

var demodata = new sn_impex.GlideExcelParser();
demodata.parse(attachmentStream);

var caseID = [];
var resolvedBy = [];
var headers = demodata.getColumnHeaders();
var header1 = headers[0];
var header2 = headers[1];
while(demodata.next()){
var row = demodata.getRow();
caseID.push(row[header1]);
resolvedBy.push(row[header2]);
}
gs.info("Case ID: " + caseID);
gs.info("Resolved By: " + resolvedBy);
}

I need this value to be set to the form section shown in the above pic

[yuvarajkate]

To import data from an Excel file into a ServiceNow custom form, create a UI Action for "Import Data" and add a file upload field. Use a Script Include to parse the uploaded Excel file, extracting the "case id" and "resolved by" columns. Map the parsed data to the corresponding fields in the IPM task table using GlideRecord. In the UI Action script, trigger the parsing and updating of records upon the user's file upload. Ensure error handling and proper security for file uploads, and test the implementation to verify correct functionality.

UI Action Script:

function onClick() {
    var file = current.your_attachment_field; // Get the uploaded file
    var excelData = parseExcelFile(file); // Parse the Excel file

    // Process each row of the Excel data
    for (var i = 0; i < excelData.length; i++) {
        var caseId = excelData[i].case_id;
        var resolvedBy = excelData[i].resolved_by;
        
        // Use GlideRecord to update the IPM task table
        var gr = new GlideRecord('ipm_task');
        if (gr.get('case_id', caseId)) {
            gr.resolved_by = resolvedBy;
            gr.update();
        }
    }

    // Provide feedback to the user
    gs.addInfoMessage('Data Imported Successfully');
}

Script Include Script to parse data from Excel:

var excelData = parseExcelFile(file); // function to parse the uploaded Excel file
for (var i = 0; i < excelData.length; i++) {
    var caseId = excelData[i].case_id;
    var resolvedBy = excelData[i].resolved_by;
    
    // Create or update the record in the IPM Task table
    var gr = new GlideRecord('ipm_task');
    if (gr.get('case_id', caseId)) {
        gr.resolved_by = resolvedBy;
        gr.update();
    }
}

Please do let me know if this helped you in your problem. 

[Anna_Servicenow]

@yuvarajkate Can this be used in on submit of the form, what changes should I make in that case to both the scripts?