Automated CSV File Generation and Column Mapping in ServiceNow Using Custom Action

[JAGRUTHIK]

Automated CSV File Generation and Column Mapping in ServiceNow Using Custom Action

Introduction

In many ServiceNow implementations, organizations need to export data to external systems in a predefined CSV format. These exports often run on a daily schedule and must follow a specific column structure required by external systems. This document explains how a Custom Action script can retrieve records, map ServiceNow fields to CSV columns, generate CSV content, and attach the generated file to a record.

Daily CSV File Generation

The CSV file is generated daily using a scheduled process or Flow Designer action. The script retrieves records that match a specific time condition, such as records updated the previous day, ensuring the exported data remains current and relevant.

CSV Header Definition

The script defines the required CSV column headers before processing records. These headers represent the structure expected by the receiving system.

Example Header Code

var headers = [
"TICKET_ID","D_PROGRAM_ID","D_PROGRAM_DESC","BARCODE",
"DEALER_ID","DEALER_DESC","D_VAC","D_MODEL","D_VIN","D_REG_DATE",
"DECLARATION_DATE","PROTOCOL_DATE","SUBMIT_DATE","MATCH_DATE",
"OPERATOR_ID","PROGRESS_STATUS"
];

var csvData = '';
csvData = headers.join(';');
csvData = csvData + "\r\n";

Retrieving Records from ServiceNow

Records are retrieved using GlideRecord with an encoded query to fetch data updated during the previous day.

Example Query Code

var gr = new GlideRecord("x_msxin_sales_inc_sales_promotion_claim_case_line");
gr.addEncodedQuery(
'sys_domain=095645aefbede210a936f0f6beefdcde^sys_updated_onONYesterday@javascript:gs.beginningOfYesterday()@javascript:gs.endOfYesterday()'
);
gr.query();

Mapping ServiceNow Fields to CSV Columns

Each CSV column corresponds to a specific field in ServiceNow. The script reads values from ServiceNow records and places them in the correct column position within the CSV structure.

Example Field Mapping Table

CSV Column	ServiceNow Field
TICKET_ID	practice_id or number
D_PROGRAM_ID	sales_promotion
D_PROGRAM_DESC	sales_promotion.name
DEALER_ID	requesting_service_organization
D_MODEL	model
D_VIN	serial_number
W_INVOICE_ID	invoice.invoice_number
W_INVOICE_DATE	invoice.invoice_date

Building CSV Rows

The script loops through each record and appends mapped values to the CSV string.

Example Row Creation Code

while (gr.next()) {

    var vals = [];

    if(gr.practice_id){
        vals.push(gr.practice_id);
    }
    else{
        vals.push(gr.number);
    }

    vals.push(gr.sales_promotion.getDisplayValue());
    vals.push(gr.sales_promotion.name);
    vals.push(gr.model);
    vals.push(gr.serial_number);

    csvData += vals.join(';') + "\r\n";
}

Handling Conditional Values

In some cases, ServiceNow field values must be converted into standardized codes required by external systems.

Example Conditional Logic

if(gr.barter == 'trade_in'){
    vals.push('P');
}
else if(gr.barter == 'scrappage'){
    vals.push('R');
}
else if(gr.barter == 'foreign_market'){
    vals.push('E');
}
else{
    vals.push('');
}

Attaching the Generated CSV File

After building the CSV content, the file is attached to a record using GlideSysAttachment.

Example Attachment Code

if (gr.getRowCount() > 0) {

    var grFileRec = new GlideRecord("u_ford_daily_digest");
    grFileRec.initialize();
    grFileRec.insert();

    var grAttachment = new GlideSysAttachment();

    var fileId = grAttachment.write(
        grFileRec,
        "daily_export.csv",
        "application/csv",
        csvData
    );

    grFileRec.setValue('u_file', fileId);
    grFileRec.update();
}

Benefits of This Approach

- Automated daily CSV export
- Flexible field mapping
- Reduced manual work
- Integration-ready data format
- Improved maintainability

Conclusion

Automating CSV file generation using a Custom Action in ServiceNow provides a reliable solution for exporting structured data to external systems. By defining CSV headers, mapping ServiceNow fields, and generating the file automatically, organizations can streamline integrations and reporting processes.