Generate custom CSV file from multiple tables using script

[Community Alums]

Hello Community,

Have a nice day!

Use case: Generate and download a custom CSV file from multiple tables which do not have relationship between them.

For e.g. Consider 3 tables: Incident(incident), CI Relationship(cmdb_rel_ci), and Catalog Item(sc_cat_item). There is no any direct relationship between them. The Downloaded CSV will look like this:

If you want to Generate and download a custom CSV file from multiple tables which have relationships between them. --> Find solution here!

Steps to generate and download a single CSV of multiple tables:

1. Declare and format table header array
2. GlideRecord tables to fetch the data and store it in the content variable
3. Attach the file to any record and download
4. Delete the Attachment

Note: You can create widget and add below code on the server side.

Step A] Declare and format table header array

var TableHeaders = ["Number", "Caller", "Short Desc", "Assignment Group", "Assigned To"];
var fileName = 'Incidents.csv';
var csvContent = ''; //will store content of CSV file
var IncidentSys_id = '60c30fe24714a****3a95ffbft6d432d'; //It is holging sys_id of incident record to which we are going to attach CSV file
var attachmentSysID = '';//will store sys_id of attachment

csvContent = csvContent + "INCIDENT\r\n";
csvContent = csvContent + "\r\n";

//Below For loop is storing columnames in sequense as stored in the variable 'TableHeaders' 
for (var i = 0; i < TableHeaders.length; i++) {
    csvContent = csvContent + '"' + TableHeaders[i] + '"' + ',';
}
csvContent = csvContent + "\r\n";

Step B] GlideRecord multiple tables to fetch the data and store it in the content variable

//Gliding data from incident table and storing it in the csvContent variable in proper format
var grInc = new GlideRecord("incident");
grInc.orderBy('number');
grInc.setLimit(5);
grInc.query();
while (grInc.next()) {
    csvContent = csvContent + '"' + grInc.number + '",' + '"' + grInc.caller_id.getDisplayValue() + '",' + '"' + grInc.short_description + '",' + '"' + grInc.assignment_grIncoup.getDisplayValue() + '",' + '"' + grInc.assigned_to.getDisplayValue() + '"';
    csvContent = csvContent + "\r\n";
}

csvContent = csvContent + "\r\n";

csvContent = csvContent + "\r\n";
csvContent = csvContent + "CI RELATIONSHIPS\r\n";
csvContent = csvContent + "\r\n";

TableHeaders = ["Parent", "Relationship", "Child"];

for (i = 0; i < TableHeaders.length; i++) {
    csvContent = csvContent + '"' + TableHeaders[i] + '"' + ',';
}
csvContent = csvContent + "\r\n";

//Gliding data from CI Relationships table and storing it in the csvContent variable in proper format
var grCMDB = new GlideRecord("cmdb_rel_ci");
grCMDB.orderBy('parent');
grCMDB.setLimit(5);
grCMDB.query();
while (grCMDB.next()) {
    csvContent = csvContent + '"' + grCMDB.parent.getDisplayValue() + '",' + '"' + grCMDB.type.getDisplayValue() + '",' + '"' + grCMDB.child.getDisplayValue() + '"';
    csvContent = csvContent + "\r\n";
}

csvContent = csvContent + "\r\n";

csvContent = csvContent + "\r\n";
csvContent = csvContent + "CATALOG ITEMS\r\n";
csvContent = csvContent + "\r\n";

TableHeaders = ["Catalog Name", "Table", "Short Description", "Category"];

for (i = 0; i < TableHeaders.length; i++) { //Build the Headers
    csvContent = csvContent + '"' + TableHeaders[i] + '"' + ',';
}
csvContent = csvContent + "\r\n";

//Gliding data from Catalog Items table and storing it in the csvContent variable in proper format
var grCatalog = new GlideRecord("sc_cat_item");
grCatalog.orderBy('name');
grCatalog.setLimit(5);
grCatalog.query();
while (grCatalog.next()) {
    csvContent = csvContent + '"' + grCatalog.name + '",' + '"' + grCatalog.table_name.getDisplayValue() + '",' + '"' + grCatalog.short_description.getDisplayValue() + '",' + '"' + grCatalog.category.getDisplayValue() + '"';
    csvContent = csvContent + "\r\n";
}

Step C] Attach the file to any record and download

//Attaching CSV file to Incident record
var grIncRec = new GlideRecord("incident");
grIncRec.addQuery("sys_id", IncidentSys_id);
grIncRec.query();
if (grIncRec.next()) {
    var grIncAttachment = new GlideSysAttachment();
    grIncAttachment.write(grIncRec, fileName, 'application/csv', csvContent);

    //Getting sys_id of Attachment record from Attachments table
    var grIncrAttachment = new GlideRecord('sys_attachment');
    grIncrAttachment.addQuery('table_sys_id', IncidentSys_id);
    grIncrAttachment.query();
    if (grIncrAttachment.next()) {
        gs.log('Report is ready to download!' + grIncrAttachment.sys_id);
	attachmentSysID = grIncrAttachment.sys_id;
    }
}

Step D] Delete the Attachment

//Code to Delete CSV which we created
var attachment = new GlideSysAttachment();
attachment.deleteAttachment(attachmentSysID);

******************************************************************************

Thanks for visiting my article. If the article helped you in any way, please hit the like button/mark it helpful. So it will help others to get the correct solution quickly.

See you in the next Article,

Prasad.