1

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.

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" 
  • 1
    I like the approach, a few notes though, Your sample code never actually uses the options you define. It's also important to specify source and destination locales. Last, the output formats I see in the documentation seem to support the format I would expect SFDC to require – gNerb Mar 18 at 17:54
  • Whoops, copied the wrong code. Wasn't sure if it was a locale issue so much as a formatting issue, focus more on the format. Not sure what you're trying to say with your last line. That the output from the parse function isn't valid for SF? – battery.cord Mar 18 at 18:15
  • One more thing that might improve the answer, how does the parser know which value is the day, month and year? for instance, in US the format is usually mm/dd/yyyy but in EU it's dd/mm/yyyy. In your code you specify 3/19/19 but what if an end user were to upload 19/3/19 (this is more of a problem with something like 01/02/2019, is it feb 1 or jan 2)? – gNerb Mar 19 at 0:21
  • If I wrote code to cover every possibility, I'dk ask for more than just rep lol. Looking at that MDN page, "parse() assumes a local time zone", but I'm sure there are better libraries which handle time zones much better than the standard. However I didn't want to link to an external library, and I generally don't cover extra problems outside the scope of the OP's question. If the OP came back and said "this doesnt work bc I have 4 different time zones" I'd adjust my answer. This should "just work" enough to demonstrate how to modify the OP's code. – battery.cord Mar 19 at 13:10
  • 1
    Thanks, your hints helped me find a solution, I will post. – Cmd_Keen Mar 19 at 14:27
1

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'])
  • 1
    Important to note, though: not all versions of Excel have the same epoch! Old versions of Excel for Mac use 1904 as the epoch date. – David Reed Mar 19 at 14:43
  • Yes that is true, it is noted in the linked thread. – Cmd_Keen Mar 19 at 15:08
  • Nice solution, solid answer! Next time tho, I'd say put some more sample dates in your question, it would have helped me (& others) answer your question better. – battery.cord Mar 19 at 18:36

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.