Catch and change locale Excel date format when importing to ISO8601

Question

I am implemeting an excel import where besides others, a date field is imported to salesforce and inserted as a record. Now I have e.g. an excel sheet where I have a date Format like 01.02.2020 or 02/01/2020 which salesforce won't accept, following the ISO8601 in formattedDateTime. Is there a best practice way to handle such an issue? I saw that there are some possobilities to do so, but I think this doesn't apply to my issue.

I am building up a datatable via the .js-controller

cmp.set('v.customObjectTableColumns', [
                                          {label: 'Name', fieldName: 'Name', type: 'text'},
                                          {label: 'Quantity', fieldName: 'Quantity__c', type: 'number'},
                                          {label: 'Date', fieldName: 'Date__c', type: 'date'}]);

And have the Date__c with type 'date'. The Object type of this custom field is also date. I am reading in the values from the excel sheet here via the .js-helper

 fr.onload = $A.getCallback(function() {
            let fileContents = fr.result;
            let workbook = XLSX.read(fileContents, {
                type: 'binary'
            });
            let rows = XLSX.utils.sheet_to_json(workbook.Sheets[workbook.SheetNames[0]]);
            let customObjects = [];

            for (let i = 0, len = rows.length; i < len; i++) {

                for (let j = 1; j <= 5; j++) {
                    let customObject = {
                        sobjectType: 'customObject__c',
                        Name: rows[i]['Name'],
                        Quantity__c: rows[i]['Quantity']
                        Date__c: rows[i]['Date']
                    };
                    customObjects.push(customObject);
                }
            }
            cmp.set("v.customObjects", customObjects);
        });

I think I need to catch the date in the .js-controller or set it even in the view.

Answer 1

I'm far from a lightning expert, but I'd try parsing the date inside your controller.

(Sample code take from How to Format a JS date, modified)

var options = { weekday: 'long', year: 'numeric', month: 'long', day: 'numeric' };
var someDate  = new Date('3-1-19');

console.log(someDate.toLocaleDateString("en-US", options); // 3/1/2019

I'd wrap the code needed in a function & call it on your excel values before handing them over to the server. parse should be generic enough to understand any dates passed along.

Switching out toLocaleString to toIsoString should return the date in a string salesforce should understand.

function ParseDate(date) {
    var someDate  = new Date(date);

    return someDate.toISOString();
}

Then, your "parser" looks like this:

Date__c: ParseDate(rows[i]['Date']) // "1.1.2019" -> "2019-01-01T05:00:00.000Z" 

Answer 2

I used this function I found on https://gist.github.com/christopherscott/2782634:

getJsDateFromExcel: function(excelDateValue) {

            var date = new Date((excelDateValue - (25567 + 2)) * 86400 * 1000);
            var localTime = new Date(date.getTime() + (new Date()).getTimezoneOffset() * 60000);
            return localTime;
 }

It handles the five-digit-date-format of excel quite well. Turns it into a local time form which can be handled by Salesfoce.

Calling it in my eventHandler:

Date__c: self.getJsDateFromExcel(rows[i]['Date'])