Script to read excel sheet name and do mapping automatically

[Vignesha Priya]

How can I read a excel sheet name and map the fields according to the name of the excel using transform script?please help

[Ratnakar7]

Hi @Vignesha Priya ,

You can leverage the GlideExcelParser API, Here's an example script that demonstrates this:

The following shows how to parse an Excel file attachment multiple times to retrieve column headers and print values of each row for each worksheet. Results display column headers and values of three separate sheets in the Excel attachment.

var parser = new sn_impex.GlideExcelParser();

var attachment = new GlideSysAttachment();

// Use the attachment sys_id of an Excel file
var attachmentStream = attachment.getContentStream("<attachment_sys_id>");

// Set the source to be parsed
parser.setSource(attachmentStream);

// Get the worksheet names to be parsed in the Excel document
var list_sheet_name = parser.getSheetNames();

gs.info(" Sheet Names " + list_sheet_name.join(", "));

// Iterate over each worksheet in the Excel workbook
for (var i = 0; i < list_sheet_name.length; i++) {
    gs.info("**************************************************************************************");
    gs.info("Sheet name:    " + list_sheet_name[i]);

// Set the worksheet name to be parsed
    parser.setSheetName(list_sheet_name[i]);

// Parse each worksheet set using setSheetName()
    if (parser.parse()) {
        //retrieve the column headers
        var headers = parser.getColumnHeaders();
        var header1 = headers[0];
        var header2 = headers[1];
        var header3 = headers[2];

        //print headers
        gs.info(header1 + "\t||" + header2 + "\t||" + header3);

        // Iterate over each row in the worksheet
        while (parser.next()) {
            var row = parser.getRow();
            //print row value for both columns   
            gs.info(row[header1] + '\t|| ' + row[header2] + '\t||' + row[header3])
        }
    } else
        gs.info(parser.getErrorMessage());
}

Also, refer https://developer.servicenow.com/dev.do#!/reference/api/tokyo/server/sn_impex-namespace/GlideExcelPa...

If my response was helpful in resolving the issue, please consider accepting it as a solution by clicking on the ✅Accept solution button and giving it a thumbs up 👍. This will benefit others who may have a similar question in the future.

Thank you!

Ratnakar

[Vignesha Priya]

Thank you for the response. From this response, I could understand how to read excel name but I am trying to figure out a script which can load fields based on the name of the excel. excel 1 has four fields. excel 2 has three fields. So we created a custom table(target table) with seven fields. But we want only four fields if the name is excel1.Once relevant four fields are loaded in target table . we can map it using mapping assist.