Truncate the header row of csv of the export set

[Mussie]

Hi Guys,

We created an export set for exporting a set of data via an MID server. However, the client is requesting for the header row of the csv (the row which contains name of the fields) want it to be truncated. Is there a way we could achieve this perhaps via running a post script? Any ideas?

Mussie

[Deepak Ingale1]

Just a though

1) If you have an access to location where that file is dumped, you can run a PowerShell script via Orchestration ( if enabled ) and modify the CSV file from the location itself.

2) Another way could be, get a hold of existing CSV file from server ( may be sys_attachment) table, read the TEXT in 2d array. Sample code as below

var stu = new GlideStringUtil();

var gsa = new GlideSysAttachment();

//Get the raw bytes in the file

var bytesInFile = gsa.getBytes('sc_cart_item','72ace5030f9722002aaeadabe1050e20');

1. gs.print(bytesInFile);

var dataAsString = Packages.java.lang.String(bytesInFile);

dataAsString = String(dataAsString);

1. gs.print(dataAsString);

3) Once you get this array, remove the 1st row and build another CSV using GlideSysAttachment APIs

[Mussie]

Thanks Deepak, I can try what you suggested but how do I make sure that the files don't get processed by the ECC queue before I make these changes? My understanding is we lose control once the files are created in sys_attachment table?

Mussie

[Mussie]

Hi Deepak,

I am generating the attachment via an export set and I have the below code on the post-export script there but I am having difficulties in identifying the record in the sys_attachment that got created from the export set. Any idea how I can get hold of the sys_id?

var attachment_table_name = 'ecc_agent_attachment';
var attachment_table_sys_id = '0775b1854f33b2407861a90f0310c762'; //randomly picked this record but this needs to be the sys_id of the attachment that just got created
attachmentGR.addQuery("table_sys_id",attachment_table_sys_id);   
attachmentGR.addQuery("table_name",attachment_table_name);   
attachmentGR.query();   
if(attachmentGR.next()){   
gs.log('running 2');
   // read the attachment...   
   var stringUtil = new GlideStringUtil();   
   var sa = new GlideSysAttachment();   
   var binData = sa.getBytes(attachmentGR);   

   // convert it to Encoded Data..   
   var encData = stringUtil.base64Encode(binData);   

   // decode the encoded data into string..   
   var csv_string = stringUtil.base64Decode(encData) + '';   
   var csv_array = csv_string.split("\r\n");   
   var new_csv_array = csv_array.shift(); //removes the first row

   // convert this back into a CSV string...   

   csv_string = new_csv_array.join("\r\n");   

   // attach it back...   

   var base64EncodeString = StringUtil.base64Encode(csv_string);   
   var document = StringUtil.base64DecodeAsBytes(this.base64EncodeString);   
   var attachment = new Attachment();   
   var table_sys_id = "0775b1854f33b2407861a90f0310c762";   
   var table_name = "ecc_agent_attachment";   
   //attach the attachment.   
   attachment_sys_id = attachment.write(table_name, table_sys_id, "1-_20170704200522.csv", "test/csv", document);   

}   

Mussie

[Deepak Ingale1 · Accepted solution]

Hi Mussie,

sys_attachment record must be tied to export set table via table_name and table_sys_id columns.

You can use these both fields on that table.