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 

okay the separator is ; and not ,

That should still be fine

what didn't work when you added that file to data source? Did the transform not trigger?