Create Salesforce Records from CSV Upload using Lightning Web Component

The following code illustrates creating Salesforce records by simply uploading a CSV file. There are also few features added,

1) User can download a template prior to upload

2) File Uploader validates user input on CSV with regex

3) Any validation errors, the system will allow the user to download "Issue Report" file which has an Error columns which describes the exact error in the row

4) User can reupload after correcting the errors from the Issue Report file itself

CSV Component Code (CSVUploader.html)

<template>
    <lightning-card title="Manual CSV File Instructions">
        <div class="slds-var-m-left_small">
            <div>1. Select <strong>Download CSV Template</strong> by clicking the button below..</div>
            <div class="slds-var-m-top_small">
                <lightning-button
                    icon-name="utility:download"
                    label="Download CSV Template"
                    title="Download CSV Template"
                    onclick={downloadTemplate}
                    class="slds-var-m-left_x-small"
                ></lightning-button>
            </div>
            <div class="slds-var-m-top_medium">
                2. Open the downloaded CSV Template file in your preferred spreadsheet software or text editor (i.e., <strong>Microsoft Excel</strong>, <strong>Google Sheets</strong>, etc.).
            </div>
            <div>
                3. Create a row in the CSV file for each record to import.
            </div>
            <div>
                4. Save the file with a <strong>.CSV</strong> extension.
            </div>
            <div>
                5. Drag and drop your complete, saved CSV file or click the <strong>Upload Files</strong> button below to upload your complete, saved CSV file.
            </div>
        </div>
        <div class="slds-var-m-left_small">
            <lightning-input
                label="Upload Your file here"
                type="file"
                accept=".csv"
                onchange={handleFileUpload}
            ></lightning-input>
        </div>
        <template if:true={showCorrectMyFile}>
            <lightning-layout-item size="12" class="slds-var-m-top_small">
                <lightning-button
                    icon-name="utility:download"
                    variant="destructive-text"
                    label="Download Issue Report"
                    title="Download Issue Report"
                    onclick={downloadErrorData}
                ></lightning-button>
            </lightning-layout-item>
        </template>
    </lightning-card>
</template>

CSV Uploader Javascript Code (CSVUploader.js)

import { LightningElement } from 'lwc';
import submitRecords from '@salesforce/apex/CSVUploaderController.submitRecords';

export default class CSVUploader extends LightningElement {
    //Field Data for each column recommended to create a custom metadata type and load the records here
    fieldData = [
        {"Sort_Order__c" : "1", "Field_Label__c" : "Roll Number", "Has_Regex__c" : false, "Regex__c" : "", "Error_Message__c" : ""},
        {"Sort_Order__c" : "2", "Field_Label__c" : "First Name", "Has_Regex__c" : false, "Regex__c" : "", "Error_Message__c" : ""},
        {"Sort_Order__c" : "3", "Field_Label__c" : "Last Name", "Has_Regex__c" : false, "Regex__c" : "", "Error_Message__c" : ""},
        {"Sort_Order__c" : "4", "Field_Label__c" : "Email", "Has_Regex__c" : true, "Regex__c" : /^(([^<>()[\]\.,;:\s@\"]+(\.[^<>()[\]\.,;:\s@\"]+)*)|(\".+\"))@(([^<>()[\]\.,;:\s@\"]+\.)+[^<>()[\]\.,;:\s@\"]{2,})$/i, "Error_Message__c" : "Email format invalid"},
        {"Sort_Order__c" : "5", "Field_Label__c" : "Date of Birth", "Has_Regex__c" : true, "Regex__c" : /^(\d{1,2})\/(\d{1,2})\/(\d{4})$/, "Error_Message__c" : "Invalid Date Format"}
    ]
    uploadedData = []
    csvData = []
    showCorrectMyFile = false

    //Download the CSV template first to get idea about the column names and their order
    downloadTemplate(event) {
        var myTemplate = 'Roll Number,First Name,Last Name,Email,Date of Birth'
        let downloadElement = document.createElement("a")
        downloadElement.href = "data:text/csv;charset=utf-8," + encodeURI(csvString)
        downloadElement.target = "_self"
        downloadElement.download = "Form Template.csv"
        document.body.appendChild(downloadElement)
        downloadElement.click();
    }
    
    //Handles the upload file button
    handleFileUpload(event) {
        let thiss = this
        if(event.target.files) {
            var oFile = event.target.files[0]
            if(oFile.name.endsWith('.csv')) {
                var reader = new FileReader()
                reader.onload = function(e) {
                    var csvRows = e.target.result.split("\n")
                    var headers = csvRows[0].split(',')
                    var hasError = false
                    if(headers[headers.length - 1].trim() == "Errors") {
                        hasError = true
                        headers.pop()
                    }
                    var errorMsg = ''
                    csvRows.every(function(row) {
                        var rowData = row.split(',')
                        if((!hasError && rowData.length > headers.length) || (hasError && rowData.length > (headers.length+1))) {
                            errorMsg = 'Seems like some of the row values has a comma (,), make sure no values has a comma.'
                            return false
                        }
                        return true
                    })
                    if(errorMsg !== "") {
                        alert("CSV File was not uploaded. One or more data validations failed. "+errorMsg)
                    }
                    else {
                        thiss.uploadedData = []
                        for(var i = 1; i < csvRows.length; i++) {
                            var rowData = csvRows[i].split(',')
                            var obj = {}
                            for(var j = 0; j < rowData.length; j++) {
                                if(!hasError || (hasError && j != rowData.length - 1)) {
                                    obj[headers[j].trim()] = rowData[j].trim()
                                }
                            }
                            if(i == (csvRows.length-1) && rowData[0] == "") {} 
                            else
                            thiss.uploadedData.push(obj)
                        }
                        //The main data validation on CSV
                        errorMsg = thiss.validateCSV(headers, thiss.uploadedData, thiss)
                        if(errorMsg !== "") {
                            if(errorMsg == "Please fix any data issues and re-upload the CSV file.")
                                thiss.showCorrectMyFile = true
                            alert("CSV File was not uploaded. One or more data validations failed. "+errorMsg)
                        }
                        else {
                            thiss.csvData = [...thiss.uploadedData]
                            console.log("Success! Your file has been validated successfully.")
                        }
                    }
                    console.log('Success reading CSV file ')
                };
                reader.onerror = function(ex) {
                    console.log('Error reading file '+ex)
                };
                reader.readAsText(oFile)
            }
        }
    }

    //Validates each row for required/optional and regex if present
    validateCSV(headers, jsonObj, thiss) {
        var errorMsg = ""
        if(headers) {
            if(jsonObj.length == 0) {
                errorMsg = "There are no rows in the uploaded CSV file, make sure you upload the complete file! "
            }
            else {
                if(headers.length != thiss.fieldData.length) {
                    if(headers.length > thiss.fieldData.length)
                        errorMsg = "There are extra columns in the uploaded CSV file, make sure only the columns specified in the template are present in the uploaded file! "
                    if(headers.length < thiss.fieldData.length)
                        errorMsg = "There are missing columns in the uploaded CSV file, make sure all the columns specified in the template are present in the uploaded file! "
                }
                else {
                    for(var i = 0; i < headers.length; i++) {
                        if(thiss.fieldData[i].Field_Label__c.trim() != headers[i].trim()) {
                            errorMsg = "Error reading column in the uploaded CSV file, expecting "+ thiss.fieldData[i].Field_Label__c.trim() +", but found "+ headers[i].trim() +". Make sure only the columns in the template are present in the uploaded file! "
                            break
                        }
                    }
                }
            }
            if(errorMsg == "") {
                var hasError = false
                jsonObj.forEach(row => {
                    for(const cell in row) {
                        let field = thiss.fieldData.find(o => o["Field_Label__c"].trim() == cell.trim())
                        if(field) {
                            let fieldLabel = field["Field_Label__c"].trim()
                            if(field["Is_Required__c"] == true) {
                                if(!row.hasOwnProperty(fieldLabel) || !row[fieldLabel]) {
                                    let entry = "Error Column "+parseInt(field["Sort_Order__c"])+": "+fieldLabel+" missing required field"
                                    row["Errors"] = (row.hasOwnProperty("Errors") && row["Errors"]) ? row["Errors"] + " | " + entry : entry
                                    hasError = true
                                }
                            }
                            if(row.hasOwnProperty(fieldLabel) && row[fieldLabel]) {
                                if(field["Has_Regex__c"] == true && field["Regex__c"]) {
                                    var reg = field["Regex__c"]
                                    if(!reg.test(row[fieldLabel])) {
                                        row["Errors"] = thiss.generateCSVErrorEntry(field, row, field["Error_Message__c"])
                                        hasError = true
                                    }
                                }
                                if(fieldLabel == "Date of Birth" && row.hasOwnProperty(fieldLabel) && row[fieldLabel]) {
                                    if(new Date(row[fieldLabel]) > new Date()) {
                                        row["Errors"] = thiss.generateCSVErrorEntry(field, row, "cannot be future date")
                                        hasError = true
                                    }
                                }
                            }
                        }
                    }
                })
                if(hasError) {
                    errorMsg = "Please fix any data issues and re-upload the CSV file."
                    console.log("There are some errors in the uploaded file! "+JSON.stringify(jsonObj))
                }
            }
        }
        return errorMsg
    }

    //Error entry for a row that has a validation
    generateCSVErrorEntry(field, row, msg) {
        let entry = "Error Column "+parseInt(field["Sort_Order__c"])+": "+field["Field_Label__c"]+" ("+row[field["Field_Label__c"]]+") "+msg;
        let completeEntry = (row.hasOwnProperty("Errors") && row["Errors"]) ? row["Errors"] + " | " + entry : entry
        return completeEntry
    }

    //Submit the values to Salesforce to create Contact records
    createRecords() {
        submitRecords({
            "jsonString" : JSON.stringify(this.csvData),
        })
        .then(result => {
            alert("Record Submission Success!")
        })
        .catch(error => {
            alert("Oops! There was a problem submitting your claims. Kindly contact admin.")
        });
    }
    
    //Download the error file, correct it and upload again
    downloadErrorData() {
        let csvString = "";
        let headers = "";
        this.fieldData.forEach(field => {
            headers = headers == "" ? field["Field_Label__c"] : headers + ","+ field["Field_Label__c"]
        })
        headers = headers + ",Errors";
        this.uploadedData.forEach(obj => {
            let thisRow = ""
            for (const key in obj) {
                if (Object.hasOwnProperty.call(obj, key)) {
                    thisRow = thisRow == "" ? "\r\n"+obj[key] : thisRow + "," + obj[key];
                }
            }
            csvString = csvString +""+ thisRow
        })
        let downloadElement = document.createElement("a");
        downloadElement.href = "data:text/csv;charset=utf-8," + encodeURI(headers+""+csvString);
        downloadElement.target = "_self";
        downloadElement.download = "Cancer Claims - Error.csv";
        document.body.appendChild(downloadElement);
        downloadElement.click();
    }
}

Apex Controller Code (CSVUploaderController.cls)

public class CSVUploaderController {
    @AuraEnabled
    public static void submitRecords(String jsonString) {
        Map<String,String> fieldData = new Map<String,String>();
        fieldData.put('Roll Number', 'Roll_Number__c');
        fieldData.put('Last Name', 'LastName');
        fieldData.put('First Name', 'FirstName');
        fieldData.put('Email', 'Email');
        fieldData.put('Date of Birth', 'Date_of_Birth__c');

        List<Object> rawObject = (List<Object>)JSON.deserializeUntyped(jsonString);
        for(Object rowData : rawObject) {
            if(rowData instanceof Map<String,Object>) {
                Map<String,Object> rowMap = (Map<String,Object>)rowData;
                for(String fieldLabel : rowMap.keySet()) {
                    Contact con = new Contact();
                    if(fieldLabel == 'Date of Birth') {
                        Date outDate = String.isNotBlank((String)rowMap.get(fieldLabel)) ? parserMyDate((String)rowMap.get(fieldLabel)) : Null;
                        if(outDate != Null)
                            con.put(fieldData.get(fieldLabel), outDate);
                    }
                    if(fieldData.containsKey(fieldLabel))
                        con.put(fieldData.get(fieldLabel), (String)rowMap.get(fieldLabel));
                }
            }
        }
    }

    private static Date parserMyDate(String inDate) {
        Date outDate = Null;
        try {
            outDate = Date.valueOf(inDate);
        }
        catch(Exception e1) {
            try {
                outDate = Date.parse(inDate.replace('-', '/'));
            }
            catch(Exception e2) {
                outDate = Null;
            }
        }
        return outDate;
    }
}