Scheduled Data Export - Pre and Post export script execution

[AmanPratapS7233]

Hi All,

I want to export incident table data to MID Server and I have created a export set and definition for that. I have some date/time fields for ex, "opened_at". I want to change the format in the excel sheet to "yyyy-mm-dd HH:mm:ss.SSSSSS".

Could anyone please suggest if, I have to use a pre or post script ? And what and how to write the logic. Any sample script could help a lot.

Thanks

[Ankur Bawiskar]

@Naveen20 

but the question is about using scheduled export set and not any other custom solution

[AmanPratapS7233]

Do we have some solution for Scheduled Export Set ?

[Naveen20]

You can try this custom approach with a staging table - if that suits your need - 

How it works:

1. Create a custom table (e.g., u_incident_export_staging) with all needed columns, where date fields are String type
2. Create a Scheduled Script that populates the staging table with formatted data
3. Point your Scheduled Export Set at the staging table instead of incident

Step 1 — Create staging table with string fields for dates

Step 2 — Scheduled Script to populate staging (runs before export):

// Clear old staging data
var del = new GlideRecord('u_incident_export_staging');
del.deleteMultiple();

// Populate with formatted data
var gr = new GlideRecord('incident');
gr.addQuery('active', true); // your filters
gr.query();

while (gr.next()) {
    var staged = new GlideRecord('u_incident_export_staging');
    staged.initialize();
    staged.setValue('u_number', gr.getValue('number'));
    staged.setValue('u_short_description', gr.getValue('short_description'));

    // Format opened_at
    var gdt = new GlideDateTime(gr.getValue('opened_at'));
    if (gdt.isValid()) {
        var internal = gdt.getValue();
        var ms = gdt.getNumericValue() % 1000;
        var micro = ('000000' + (ms * 1000)).slice(-6);
        staged.setValue('u_opened_at', internal.substring(0, 19) + '.' + micro);
    }

    // Repeat for other date fields as needed
    staged.insert();
}

gs.info('Staging populated: ' + gr.getRowCount() + ' rows');

Step 3 — Schedule it:

• Schedule the staging script to run ~15 minutes before the Scheduled Export Set runs
• The Export Set then picks up the pre-formatted staging data and exports as-is

[Tanushree Maiti]

Hi @AmanPratapS7233 

If the date and time format is hh:mm:ss in the glide.sys.date_format System Properties setting, and you export time values to Excel, they appear in 24-hour military time.

To display the exported values in standard 12-hour am/pm time formats, select the 1:30PM time format in Format Cells > Time in Excel.

refer: Export date and time formats 

[AmanPratapS7233 · Accepted solution]

Hi All, I got the solution - we just have to go to the 'import export' system property and in the 'export csv format' field, we have to write UTF-8 instead of default and this works fine then.