Need to export csv file without header

Ajay Sharma2 · ‎11-23-2020

Hi Team,

Need to exort CSV file without headers in exported sheet,

Please help!

Hitoshi Ozawa · ‎11-23-2020

Following Scripted REST API will export all incident records.

It'll create an csv as an attachment to a record and the send it back.

(function process( /*RESTAPIRequest*/ request, /*RESTAPIResponse*/ response) {

    var tmpTableName = 'incident'; // name of table to create attachment
    var number = 'INC0000055'; // record number to create attachment
    var exportFileName = 'example.csv';  // name of temporary attachment file
    var contentType = 'text/csv';

	var exportTableName = 'incident';
    var columnNames = ['sys_id', 'number', 'assigned_to.name'];  // names of columns to export

    try {
        // insert attachment
        var attachment = new GlideSysAttachment();
        var grTable = new GlideRecord(tmpTableName);
        if (grTable.get('number', number)) {
            var attachmentSysId = grTable.sys_id;

            var rec = new GlideRecord(tmpTableName);
            rec.get(attachmentSysId);
            var content = getRecords();
            var agr = attachment.write(rec, exportFileName, contentType, content);

            // get attachment and send back to user
            var grAttachRead = new GlideRecord('sys_attachment');
            grAttachRead.addQuery('table_sys_id', grTable.sys_id);
            grAttachRead.addQuery('file_name', exportFileName);
            grAttachRead.query();
            if (grAttachRead.hasNext()) {
                var count = 0;

                var hdrs = {},
                    attachment_sys_id = exportFileName;

                hdrs['Content-Type'] = contentType;
                response.setStatus(200);
                response.setHeaders(hdrs);

                if (grAttachRead.next()) {
                    var writer = response.getStreamWriter();
                    var attachmentStream = new GlideSysAttachmentInputStream(grAttachRead.sys_id);
                    writer.writeStream(attachmentStream);
                }
            }

            // delete temporary attachment file from record
            var grAttachDelete = new GlideSysAttachment();
            grAttachDelete.deleteAttachment(grAttachRead.sys_id);
        }
    } catch (e) {
        gs.error("ERROR=", e);
        return {
            'error': e.message
        };
    }

    // function to return records to export in csv
    function getRecords() {
        var records = [];
        var grInc = new GlideRecord(exportTableName);
        grInc.query();
        while (grInc.next()) {
            var columnValue = [];
            columnNames.forEach(function(name) {
                columnValue.push(grInc.getElement(name).toString());
            });
            records.push(columnValue.join(','));
        }
        return records.join('\n');
    }
})(request, response);

curl script sample

curl "https://<service instance name>.service-now.com/api/<servicenow instance number>/exportcsv" \
--request GET \
--header "Accept:text/csv" \
--user '<user name>':'<password>' \
--output <csv file name>.csv

View solution in original post

Hitoshi Ozawa · ‎11-25-2020

No. The script won't work with export set.

The script can be executed on a MID server. It's the MID server is Windows, call the script rest api from powershell. If it's linux, use curl.

To separate using semicolon, change the following line to use semicolon instead of a comma.

records.push(columnValue.join(';'));

Hitoshi Ozawa · ‎11-25-2020

Powershell script is like below. Be sure to replace values of user name, password, servicenow instance name, namespace.

# Eg. User name="admin", Password="admin" for this code sample.
$user = "<user name>"
$pass = "<password>"

# Build auth header
$base64AuthInfo = [Convert]::ToBase64String([Text.Encoding]::ASCII.GetBytes(("{0}:{1}" -f $user, $pass)))

# Set proper headers
$headers = New-Object "System.Collections.Generic.Dictionary[[String],[String]]"
$headers.Add('Authorization',('Basic {0}' -f $base64AuthInfo))
$headers.Add('Accept','application/json')


# Specify endpoint uri
$uri = "https://<servicenow instance name>.service-now.com/api/<namespace>/exportcsv"

# Specify HTTP method
$method = "get"




# Send HTTP request
$response = Invoke-RestMethod -Headers $headers -Method $method -Uri $uri 

# Print response
$response.RawContent

Ajay Sharma2 · ‎11-26-2020

@Hitoshi Ozawa

Not sure, getting the file exported but not in CSV format.

It is showing as type: file in file properties.

Regards,

Ajay Sharma

Hitoshi Ozawa · ‎11-26-2020

Sorry, my bad. Response format should be in "text/csv" so the line should be

$headers.Add('Accept','text/csv')