Generate CSV file through script

[asifnoor]

In this article, I will share with you a code snippet that will allow you to export the data to CSV of any table through the script and attach it to a record.

var Headers = ["Number","Caller","Short Desc","Assignment Group", "Assigned To"];
var fileName = 'Incidents.csv';
var csvData = ''; //The variable csvData will contain a string which is used to build the CSV file contents
for (var i = 0; i < Headers.length; i++) { //Build the Headers
	csvData = csvData + '"' + Headers[i] + '"' + ',';
}
csvData = csvData+"\r\n";

var gr = new GlideRecord("incident");
gr.addActiveQuery();
gr.query();
while(gr.next()) {
	csvData = csvData + '"' + gr.number + '",' + '"' + gr.caller_id.getDisplayValue() + '",' + '"' + gr.short_description+'",' + '"' + gr.assignment_group.getDisplayValue() + '",' + '"' + gr.assigned_to.getDisplayValue() + '"';
	csvData = csvData+"\r\n";
}

//attach the file to a record.
var grRec = new GlideRecord("incident");
grRec.addQuery("sys_id","6c9b1b7fdb9bff004caea386059619d8");
grRec.query();
if(grRec.next()){
	var grAttachment = new GlideSysAttachment();
	grAttachment.write(grRec, fileName, 'application/csv',csvData);
}

Let me know if you have any questions.

Kindly bookmark the article and click on helpful if you find this useful.

[AdrianKWilson]

Can this be used to email the csv? Looking to email some fields from a requested item form via csv. 

[asifnoor]

To eamil, you need to have the CSV present as attachment. If the requirement is to generate CSV dynamically and send an email, then yes you can use this script to create a CSV and upload it as attachment and then use that attachment in the email.

Mark the comment as helpful if it helps.

[DB1]

Hi Asifnoor,

Thanks for the great Article.

I wanted to ask if this can be converted to Script Include and also Styled from UI page by calling the same Script Include?

Say for example I need to add styles using HTML through UI Page to make column names like Inc. number/Short description/caller_id bold or add colours.

If we can do so, can you please guide us on how to implement the same?

[asifnoor]

Hi,

Its a server side script. So yes you can write a SI and put this code there.

Coming to formatting, while you can format by adding html tags, but once the file is saved as type csv, all formatting will be lost when you open the CSV in 3rd party application.

[DB1]

Thanks for the reply.

So does it mean there is less chance of styling in this case?

Because I have a similar code and was able to dowload data as CSV but looking for options to style it if possible.

Regards,

Bhavani

[asifnoor]

Hello Bhavani,

Which application are you going to use to open the CSV file? 

[DB1]

Hi Asifnoor,

MS Excel is the appliication we use to open CSV files.

Thanks,

Bhavani

[asifnoor]

Hi Bhavani,

For CSV there is no option to formatting because the content type CSV does not support formatting. 

[Hitoshi Ozawa]

Short Description field may contain double quotes so it need to be escaped by replacing it with 2 double quotes.

        var sd = gr.short_description;
        sd = sd.replace(/"/g, '""');
        csvData = csvData + '"' + gr.number + '",' + '"' + gr.caller_id.getDisplayValue() + '",' + '"' + sd +'",' + '"' + gr.assignment_group.getDisplayValue() + '",' + '"' + gr.assigned_to.getDisplayValue() + '"';

[DB1]

Hi All,

As mentioned I have a similar script in place which actually queries sys_metadata table.

I need help on 2 of the following:

1. I tried to put the below script into a custom application. However, script runs only in the Global scope.

Is there any possibility that this script can be written in a custom scope and call Global functions.

If so, can somebody help with how to do it?

2.The below script returns 3 outputs 1. Name 2. Createdby and table from "sys_scope" column which are pretty straight forward from "sys_metadata"  table

However there are other fields under each application files/class like Business Rules has conditions; UI policies has short description.

In order to get those field details can I query sys_db_object table and pass the class name so that I get all the fields with respect to those tables?

function copyApplicationfiles(gr) {

output("\n Application Files");
var classname = '';
var records = [];
var sno = 1;
while (gr.next()) {
//var shortdesc= gr.sys_scope.short_description;
//output("\nApplication Name: " + gr.sys_scope.name);
//output(gr.sys_class_name + " class:"+ records.length + " Find: "+records.indexOf(gr.sys_class_name));
if(gr.sys_class_name != classname) {
classname = String(gr.sys_class_name);
sno = 1;
output("_______________________________________________________________________\n");
output("Class Name: " + gr.sys_class_name.getDisplayValue());
output('S.No\tName\tTable\tCreatedBy');
}

output(sno + '\t' + gr.sys_name + '\t' + gr.sys_class_name.getDisplayValue() + '\t' + gr.sys_created_by);
sno++;

}

}

function download(filename, data) {
var blankInc = new GlideRecord("ecc_queue");
blankInc.agent = "Background Script";
blankInc.topic = "Output: "+ filename;
blankInc.name = filename;
blankInc.source = "Application files";
blankInc.insert();
// Replace /t with ,
data = data.replace(/\t/g, ',');
var attachment = new Attachment();
var attachmentRec = attachment.write("ecc_queue", blankInc.sys_id.toString(), filename, "text/csv", data);
var attachRec = new GlideRecord("sys_attachment");
attachRec.addQuery("table_sys_id", blankInc.sys_id.toString());
attachRec.addQuery("file_name", filename);
attachRec.query();
attachRec.next();
var url = "/sys_attachment.do?sys_id="+attachRec.sys_id.toString()+"&view=true";
gs.print("\n\n");
gs.print("\n\n");
gs.setRedirect(url);

}
function output(str) {
fileoutputstr += str + '\n';
}

var fileoutputstr = "";
var appID = '9dceaed7db351054682aab92ca9610ce';
var gr = new GlideRecord("sys_metadata");
gr.addQuery('sys_scope', appID);
gr.orderBy('sys_class_name');
gr.query();
while(gr.next())
{
output("Application Name :" + gr.sys_scope.name);

copyApplicationfiles(gr);
download('ApplicationFiles_output.csv', fileoutputstr);
}

[SS64]

I was just writing an article like this. What does the header above mean?

Also, do you create this with business rules? Or is it a schedule script?

[asifnoor]

The headers are the column headers that you get in the 1st row of your document.

Its a server side standalone script, which you can run it in bg or fix script. If you want to execute through BR you can do it by making required changes.

[SS64]

Thank you.

What is standalone? Where bg ?

[asifnoor]

Standalone script which is not dependent on any other object such as a fix script or background script. 

Mark my article as helpful and comments as helpful if it helps.

[Community Alums]

It is working as expected. Thank you so much for this thread. 

[asifnoor]

You are welcome 🙂

[madhuv]

Hi,

For one of requirement we followed the same way of design. But if the number of rows to be included in the CSV file is more than 100000 , the output csv file is not generated. Is there any reason?

Regards

Madhu

[asifnoor]

I think csvData cannot store that many data and it might be breaking it. I suggest you take up multiple variables and write to CSV and check once.

[Avik Dasgupta]

Hi Asifnoor,

Thank you for the awesome article! Could you please let me know how would I download the CSV file instead of attaching it ?

[TT3]

Nice article. Can we upload the generated file via script on a external sFTP server?

[snow123]

Hi Asifnoor,

        The article is not working in UI actions. Please guide.

Regards,

Suresh

[Tanwir Singh11]

Hey @asifnoor ,

Thanks for the valuable article. 

Need some help with my requirement, I am able to generate csv but is it possible to add background colors to headers through script?

Could you help, please?

[TT3]

I don't think this is possible because this is CSV, it does not have any formatting capabilities.

[Tanwir Singh11]

Hey @asifnoor 

Is it possible to generate .xlsx file instead of .csv ? If yes, could you tell how?

Thanks!

[Sujit4]

Very useful article. Is there any way to export the csv on some shared location? any FTP server location, so that other team can use the data

thanks,

Sujit

[Nandu3]

Hi Madhu,

Did you got any solution for creating file having more than 100000 records.

Thanks,

NandaKishore

[Nandu3]

Hi Madhu,

Did you got any solution for creating .csv file having more than 100000 records.

Thanks,

Nanda kishore

[sailokesh1004]

Hi @asifnoor 

I'm trying to make the headers dynamic, the user will enter the column name in input variable, I tried like var header = inputs.header , in flow designer, but in CSV we are not getting proper header names, how do I do it?

[KS18]

@asifnoor 
Is it possible to specify a character code in the process of creating a CSV?

[ruslan]

Hello,

this code works for all types of field, except html field: it destroys the structure of table

[PriyankaThakur_]

Hi Asif , 

I tried this code and its working form me to get the data file in csv format but its blank. Could you pls suggest any changes or reason for that 

Thanks 

[vbalaji2]

Can u help me in giving a code for my condition -

My catalog item has one field as Server Name having refference table as cmdb_ci_server .

If a person selects aserver and submit the item a new requested item is created right .

Now the condition is I need the details of other fields like operating system, ram etc for the selected server name and dump this data in CSV file of requested item can this be done in flow designer 

@asifnoor can u help me in giving a solution for this it could be more helpful.

[PramodK37293381]

Hi asifnoor,

How we can genrate a CSV file and send it through mail/notification with attachment to the users using script.