Convert .csv file to .xlsx file and auto import to target table received attachment via email

[umam7]

Hi,

When I'm trying to load .xlsx file data automatically received via email to the target table records are getting created with correct data.

But when I'm trying to load .csv file data to the target table, records are getting created with empty data. As this is not working I tried to convert .csv to .xlsx file automatically and then import. Same is not working and looking for the solution.

Inbound Email Action:

(function runAction(current, event, email, logger, classifier) {  
    // Modify the next 2 lines with the names of your Import Set Table.
    var importSetName = "import_batches";
    var importSetLabel= "u_import_batches";
    var applicationScope = "Global";

    // Setup the data source
    current.name = importSetName + " " + gs.nowDateTime();
    current.import_set_table_name = importSetLabel;
    current.import_set_table_label = importSetName;
    current.type = "File";
    current.format = "CSV";
    current.sheet_number = 1;
    current.header_row = 1;
    current.file_retrieval_method = "Attachment";
    current.sys_package.setDisplayValue(applicationScope);
    current.sys_scope.setDisplayValue(applicationScope);
    current.delimiter = ";";
    gs.log("Data Source Delimiter Set: " + current.delimiter);
    var myDS = current.insert();
    gs.log("Data Source Created: " + myDS);

    // Convert CSV to Excel
    var csvData = ''; // Add logic to retrieve the CSV data from the email or attachment
    var workbook = new global.ExcelWorkbook();
    var worksheet = workbook.addSheet('Sheet1');

    var rows = csvData.split('\n');
    rows.forEach(function(row, rowIndex) {
        var columns = row.split(';'); // Use the delimiter set in your data source
        columns.forEach(function(column, columnIndex) {
            worksheet.setValue(rowIndex + 1, columnIndex + 1, column);
        });
    });

    var fileName = 'converted_file.xlsx';
    var fileData = workbook.save(fileName);

    // Optionally, attach the Excel file to a record
    var grAttachment = new GlideSysAttachment();
    grAttachment.write(current, fileName, 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet', fileData);
    grAttachment.insert();

    gs.log("Excel File Created and Attached");

    // Schedule the import
    new global.EmailFileImportUtils().scheduleImport(myDS);  
})(current, event, email, logger, classifier);

Script Include:

var EmailFileImportUtils = Class.create();  

EmailFileImportUtils.prototype = {  

    initialize: function() {  

    },  

    scheduleImport: function(dataSourceID) {  

        /*

         * Create scheduled job to process import

         *

         * will generate an import data source, however the attachment

         * isn't copied to the data source until after the record insert.

         * The code below creates a scheduled job to process the file import

         * 30 seconds later so that attachment has time to be copied to the

         * data source from the email.

         */  

        var schRec = new GlideRecord("sys_trigger");  

    //  gs.log("Testing logs1");

        schRec.name = "Load Data Source: " + dataSourceID;  

        schRec.trigger_type = 0; // Run Once  

        schRec.script = "new global.EmailFileImportUtils().loadImportSet('" + dataSourceID + "')";  

        var nextAction = new GlideDateTime();  

        nextAction.addSeconds(60); // 30 seconds however this can be changed.  

        schRec.next_action = nextAction;  

        schRec.insert();  

    },  

    loadImportSet: function(dataSourceID) {  

        // Get Datasource Record  

        var dataSource = new GlideRecord("sys_data_source");  

        dataSource.get(dataSourceID);

          //gs.log("Load import set" + dataSourceID);

        //  gs.log("Load import set" + dataSource);

        // Process data source file  

        var loader = new GlideImportSetLoader();  

        var importSetRec = loader.getImportSetGr(dataSource);  

        var ranload = loader.loadImportSetTable(importSetRec, dataSource);  

        gs.log("Imported Set Record: " + importSetRec);

        gs.log("Imported Set Record Contents: " + JSON.stringify(importSetRec));

       // importSetRec.state = "loaded";

        importSetRec.update();  

        // Transform import set  

        this._doTransform(importSetRec);

    },  

    _doTransform: function(set){

        var importSetRun = new GlideImportSetRun(set.getUniqueValue());

        var importLog = new GlideImportLog(importSetRun, set.data_source.name);

        var ist = new GlideImportSetTransformer();

        ist.setLogger(importLog);

        ist.setImportSetRun(importSetRun);

        ist.transformAllMaps(set);

     },

    type: 'EmailFileImportUtils'  

};

[Ankur Bawiskar]

@umam7 

why not use xlsx only?

is it the case that sometimes you will receive xlsx and sometimes csv?

If my response helped please mark it correct and close the thread so that it benefits future readers.

[umam7]

Hi Ankur,

We receive only csv file and not xlsx file from a team. They are unable to send xlsx file directly, it requires some process to share and it is difficult at the moment. Hence request is to import the csv file.

[Ankur Bawiskar]

@umam7 

it's not easy to convert csv to xlsx as csv is plain text file

why not directly attach that csv to data source and handle the transformation?

how does the csv look like?

It should have 1st row as column headers and from next row onwards it should be all data

If my response helped please mark it correct and close the thread so that it benefits future readers.

[umam7]

HI Ankur,

Yes, we also have tried by attaching csv to data source, still not working. csv file looks like below

1st Row -- Fieldname1; Fieldname2; Fieldname3; Fieldname4; Fieldname5; Fieldname6; Fieldname7; Fieldname8; Fieldname9; Fieldname10; Fieldname11

2nd Row -- Dropdownvalue;Test.JOBS;Dropdownvalue;Test;JOBS;;;;;;

As I'm unable to share the data, i have shared test data with same pattern