Special Characters breaking Apex Loop

Question

I have code that is being uploaded as a CSV that is breaking if special characters are present i.e:

• comma ,
• Apostrophe '
• à

I have built docs that has them remove these but the one I am not sure how to fix. Any recommendations on how this can be skipped for characters like 'à'?

Apex:

public with sharing class UploadCSVController {

//Public Variables----------------------------------------------------------------------------------------------------------------------------------------------
public transient Blob    fileBody{ get; set; }                              //fileBody
public transient Integer fileSize{ get; set; }                              //fileSize
public transient String  fileName{ get; set; }                              //fileName
public List<Lead>        leadsList{ get; set; }                             //List of inserted Leads
public List<String>      fieldNamesList{ get; set; }                        //List of fields in Lead Object's fields which are avaiable in CSV
public Boolean           reVariableSave{ get; set; }                        //Variable to render Save Button
public Boolean           reVariableBlock{ get; set; }                       //Variable to render PageBlock with inserted record details
public Boolean           reVariable{ get; set; }                            //Variable to render Record Details Table    
public Integer           totalRecords{ get; set; }                          //Total number of records in CSV File
public Integer           insertedRecords{ get; set; }                       //Number of records successfully inserted
public Integer           failedRecords{ get; set; }                         //Number of failed records

//Private Variables----------------------------------------------------------------------------------------------------------------------------------------------
String fileBodyAsString = '';                                               
//String value of file Body
String header           = '';

//Constructor----------------------------------------------------------------------------------------------------------------------------------------------------
public UploadCSVController(){
    leadsList           = new List<SObject>();
    fieldNamesList      = new List<String>();
    reVariableSave      = false;
    reVariableBlock     = false;
    reVariable          = false;
    totalRecords        = 0;
    insertedRecords     = 0;
    failedRecords       = 0;    
}

//Method to check file size and whether the file body is readable------------------------------------------------------------------------------------------------
public void ReadFromFile(){
    reVariableBlock = false;
    if(fileSize < 3280760 && fileName.substringAfterLast('.').equalsIgnoreCase('csv')){
        system.debug('file size' + filesize);
        system.debug('file name after' + fileName.substringAfterLast('.'));
        system.debug('fileBodyAsString ' + fileBodyAsString);
        system.debug('fileBody.toString() ' + fileBody.toString());
        try{
            fileBodyAsString = fileBody.toString();
            ReadCSVFile();
        }
        catch(exception e){
            ApexPages.addMessage(new ApexPages.Message(ApexPages.Severity.ERROR,'Error reading CSV file'));
        }
    }
    else{
        ApexPages.addMessage(new ApexPages.Message(ApexPages.Severity.ERROR,'No File Chosen or,'));
        ApexPages.addMessage(new ApexPages.Message(ApexPages.Severity.ERROR,'File Size greater than 50 KB or,'));
        ApexPages.addMessage(new ApexPages.Message(ApexPages.Severity.ERROR,'File is not of CSV type'));
    }
}

//Method to read the file and create a Lead list which will be inserted and check exceptions in CSV file------------------------------------------------------
private void ReadCSVFile(){
    if(fileBodyAsString.length() > 100000){
        header = fileBodyAsString.substring(0, 2000).split('\n')[0];
        system.debug('line 67 passed');
    }
    else{
        header = fileBodyAsString.split('\n')[0];
        system.debug('line 71 passed');
    }
    fileBodyAsString = fileBodyAsString.substringAfter('\n');
    system.debug('field Name List before clear ' + fieldNamesList);
    leadsList.clear();
    fieldNamesList.clear();
    system.debug('line 78 passed');
    for(String str : header.split('\n')[0].split(',')){
        fieldNamesList.add(str.trim());
    }
    system.debug('line 81 passed');
    system.debug('fieldNamesList line 81 ' + fieldNamesList);
    String                      LeadDetails         = '';
    Map<string, SObjectField>   fieldNamesMap       = Schema.getGlobalDescribe().get('Lead').getDescribe().fields.getMap();
    System.debug('Field Name List After Clear before if check ' + fieldNamesList);
    System.debug('Field Name List After Clear before if check ' + fieldNamesList[0]);
    system.debug('line 84 passed');
    System.debug('fieldNamesMap ' + fieldNamesMap);
    System.debug('LeadDetails ' + LeadDetails);
    if(fieldNamesList[0].equalsIgnoreCase('LastName')){
        System.debug('Line 91 passed');
        Lead lds                = new Lead();
        Integer flag            = 0; 
        while( !String.isEmpty(fileBodyAsString)){                                                                 
            
            //Itereation over each Lead
            if(fileBodyAsString.length() > 100000){
                leadDetails = fileBodyAsString.substring(0, 100000).split('\n')[0];
            }
            else{
                leadDetails = fileBodyAsString.split('\n')[0];
            }
            fileBodyAsString = fileBodyAsString.substringAfter('\n');
            String lastnameValue = leadDetails.substringBefore(',').trim();
            for(Integer j=0; j<fieldNamesList.size(); j++){                                                                 
                //Iterating over the fields & assigning value to them for each Lead 
                Schema.DisplayType fieldType = fieldNamesMap.get(fieldNamesList[j]).getDescribe().getType();
                if( fieldType == Schema.DisplayType.DATE ){              
                    //Setting value of fields which are of Date Type
                    try{
                        lds.put(fieldNamesList[j].trim(), Date.valueOf(leadDetails.substringBefore(',').trim()));
                    }
                    catch(Exception e){
                        flag = 1;
                        ApexPages.addMessage(new ApexPages.Message(ApexPages.Severity.ERROR, 'Empty ' 
                                                                   + fieldNamesList[j].trim() 
                                                                   + ' field or Date is not in format \"YYYY-MM-DD\" for record with LastName = ' 
                                                                   + lastnameValue));
                    }
                }
                else if( fieldType == Schema.DisplayType.DATETIME ){ //Setting value of fields which are of DateTime Type
                    try{
                        lds.put(fieldNamesList[j].trim(), Datetime.valueOf(leadDetails.substringBefore(',').trim()));
                    }
                    catch(Exception e){
                        flag = 1;
                        ApexPages.addMessage(new ApexPages.Message(ApexPages.Severity.ERROR, 'Empty ' + fieldNamesList[j].trim() 
                                                                   + ' field or DateTime is not in format \"YYYY-MM-DD HH:MM:SS\" for record with LastName = ' 
                                                                   + lastnameValue));
                    }
                }
                else if( fieldType == Schema.DisplayType.BOOLEAN ){  //Setting value of fields which are of Boolean Type
                    if(leadDetails.substringBefore(',').trim().equalsIgnoreCase('true') 
                       || leadDetails.substringBefore(',').trim().equalsIgnoreCase('false') 
                       || leadDetails.substringBefore(',').trim() == ''){
                        lds.put(fieldNamesList[j].trim(), Boolean.valueOf(leadDetails.substringBefore(',').trim()));
                    }
                    else{
                        flag = 1;
                        ApexPages.addMessage(new ApexPages.Message(ApexPages.Severity.ERROR,  'Acceptable value for ' 
                                                                   + fieldNamesList[j].trim() 
                                                                   + ' field is \"TRUE/FALSE\" for record with LastName = ' 
                                                                   + lastnameValue));
                    }
                }
                else if( fieldType == Schema.DisplayType.TEXTAREA ){ //Setting value of fields which are of TextArea Type
                    if(leadDetails.left(1) != '\"'){                                                                 
                        //Setting value of fields which are of TextArea Type with comma
                        lds.put(fieldNamesList[j].trim(), leadDetails.substringBefore(',').trim());
                    }
                    else{                                                                                               
                        //Setting value of fields which are of TextArea Type without comma
                        lds.put(fieldNamesList[j].trim(), leadDetails.substringBetween('\"', '\"') .trim());
                        leadDetails = leadDetails.substringAfter('\"').substringAfter('\"');
                    }
                }
                else{                                                                                                   
                    //Setting value of all other type of fields
                    try{
                        lds.put(fieldNamesList[j].trim(), leadDetails.substringBefore(',').trim());
                    }
                    catch(Exception e){
                        flag = 1;
                    }
                }
                leadDetails = leadDetails.substringAfter(',');
            }
            leadsList.add(lds);
            lds = new Lead();
        }                  
        if(flag != 1){                                                                                                  
            //If there is no exception, show Save button
            reVariableSave = true;
            ApexPages.addMessage(new ApexPages.Message(ApexPages.Severity.INFO,  'Click Save Button to insert the Records '));
        }
    }
    else{
        ApexPages.addMessage(new ApexPages.Message(ApexPages.Severity.ERROR,  'This has failed to work ....'));
    }
}

//Save method to insert the Lead Recods and counting inserted records and failed records----------------------------------------------------------------------
public Pagereference save(){
    reVariableBlock = true;
    Database.SaveResult[] srList = Database.insert(leadsList, false);
    leadsList.clear();
    Set<Id> savedLeadIdSet = new Set<Id>();
    totalRecords    = 0;
    insertedRecords = 0;
    failedRecords   = 0;
    for (Database.SaveResult sr : srList) {
        ++totalRecords;
        if (!sr.isSuccess()) {
            ++failedRecords;               
            for(Database.Error err : sr.getErrors()) {
                ApexPages.addMessage(new ApexPages.Message(ApexPages.Severity.ERROR,  err.getStatusCode() 
                                                           + ': ' + err.getMessage() 
                                                           + ' for Record at Line Number: ' 
                                                           + (totalRecords + 1)));
            }
        }
        else{
            ++insertedRecords;
            savedLeadIdSet.add(sr.getId());
        }
    }
    leadsList = Database.query('SELECT ' + header + ' FROM Lead WHERE Id in :savedLeadIdSet');
    if(leadsList.size() > 0 && leadsList.size()<400){
        reVariable = true;
    }
    return null;
}
}

VisualForce:

<apex:page lightningStylesheets="true" controller="UploadCSVController">
<h1 style="font-size:30px"><center>Automation Upload</center></h1><br/><br/>

<apex:form id="idForm">
    <apex:pageMessages /><br/><br/>

    <apex:pageBlock tabStyle="Lead" id="idInputBlock">
        <p style="height:13px; font-size:13px"><b>Set the first column of the CSV file as LastName Field:</b></p><br/>
        <apex:inputFile value="{!fileBody}" fileSize="{!fileSize}" fileName="{!fileName}" accept=".csv" contentType="text/csv" id="idInputFile"></apex:inputFile>                 
        <!-- Input File -->
        <apex:commandButton value="Upload File" action="{!ReadFromFile}" onclick="document.getElementById('actionStatusUP').style.display = 'inline';" />  
        <!-- Upload Button to upload file -->
        <img src="/img/loading.gif" style="display:none" id="actionStatusUP" />                    
        <apex:pageBlockButtons location="bottom" >                                                                                                          
        <!-- Save Button to insert the records -->
            <apex:commandButton value="Save" action="{!save}" rendered="{! reVariableSave}" onclick="document.getElementById('imageSV').style.display = 'inline';" />
            <img src="/img/loading.gif" style="display:none" id="imageSV" />
        </apex:pageBlockButtons>
        <p style="height:13px; font-size:13px"><b><br/> 
            Please be sure to upload a .csv file. You can find an example for the file type here
            </b>
        </p>
        <br/>
    </apex:pageBlock>

    <apex:pageBlock rendered="{! reVariableBlock}" id="idTable">
        <apex:pageBlockSection collapsible="false" columns="2" >
            <p><b>Total Records in CSV File:&nbsp;</b></p>
            <apex:outputText value="{! totalRecords}" style="color:black" />
            <p><b>Number of Records Successfully inserted:&nbsp;</b></p>
            <apex:outputText value="{! insertedRecords}" style="color:green" />
            <p><b>Number of Failed Records:&nbsp;</b></p>
            <apex:outputText value="{! failedRecords}" style="color:red" />
        </apex:pageBlockSection><br/>
        <center><p style="color:dodgerblue;font-size:25px"><b>Details of the inserted records</b></p></center><br/><br/>
        <apex:pageBlockTable value="{! leadsList}" rendered="{! reVariable}" var="lds" >                                                           
        <!-- Table to display details of inserted records -->
            <apex:repeat value="{! fieldNamesList}" var="fld" >
                <apex:column value="{! lds[fld]}" />
            </apex:repeat>
        </apex:pageBlockTable>
    </apex:pageBlock>
    
</apex:form>
</apex:page>

Answer 1

You're not parsing according to the rules of RFC 4180. You need a proper CSV parser, such as one of the few written by myself or Daniel Ballinger. The reason why à may be failing is that you're not using Unicode as your file encoding. When you save the file, make certain that you use UTF-8 as your file encoding, or you will absolutely be unable to read the file properly, as Apex always uses UTF-8. That's just just the limitations you're going to have to live with.

Here's an example using one of my CSV parsers:

CsvParser parser = new CsvParser(
    'Col 1,Col 2\n"Special\nLine","With bonus char: à'+
    '"\n"And with a\n line break and comma, too!",');
System.debug(
    parser.parse()
);

Which outputs:

({Col 1=Special
Line, Col 2=With bonus char: à}, {Col 1=And with a
 line break and comma, too!, Col 2=})

You must use a full and complete parser in order to properly read CSV.

Further, your code has some very expensive operations you should clean up. Since you know the sObject you want, avoid using Schema.getGlobalDescribe(), as it is every expensive.

Instead:

Map<string, SObjectField> fieldNamesMap = sObjectType.Lead.fields.getMap(); 

Also:

Integer flag            = 0; 

Is very oldschool, definitely not recommended for modern code. Instead:

Boolean hasError = false;

...

hasError = true;

...

if(!hasError) {

Also, I recommend that you avoid the use of .equalsIgnoreCase and use the much simpler:

someString == 'false'

Because == is already a case-insensitive comparison, albeit with a slightly higher cost for long strings.

Note that my CsvParser returns a List<Map<String, String>> so you can access the fields directly, but I also have a line parser I don't think I've posted up to this point that returns a List<List<String>>. I'll include that below. This one runs faster, but you have to match up the headers yourself. It also has an option to read CSV files without headers, which skips some checks.

---

public class CsvParserListStyle {
    String csv;
    Integer rowCount;
    Integer cellCount;
    final static String END_LINE = '\r';
    final static String END_CELL = ',';
    final static String ESCAPE_CHAR = '"';
    final static String SPACE = ' ';
    
    public CsvParserListStyle(String csvString) {
        csv = csvString.replace('\r\n','\r').replace('\n','\r')+'\r';
    }
    public List<List<String>> parseWithoutHeaders() {
        List<List<String>> results = new List<List<String>>();
        cellCount = 1;
        rowCount = 0;
        Integer rowSize;
        do {
            String[] line = readLine();
            Integer lineSize = line.size();
            if(lineSize == 0) {
                continue;
            }
            if(rowSize == null) {
                rowSize = lineSize;
            }
            if(rowSize != lineSize) {
            //    throwInvalidStateError('CSV row has '+lineSize+' cell(s), expected '+rowSize+' cell(s)');
            }
            results.add(line);
            rowCount++;
        } while(csv != '');
        return results;
    }
    public List<List<String>> parseWithHeaders() {
        List<List<String>> results = new List<List<String>>();
        List<String> headers = readLine();
        Set<String> uniqueHeaders = new Set<String>();
        cellCount = 1;
        rowCount = 0;
        for(String header: headers) {
            if(!uniqueHeaders.add(header)) {
                throwInvalidStateError('Duplicate header detected');
            }
        }
        rowCount++;
        Integer headerCellCount = headers.size();
        do {
            String[] line = readLine();
            Integer currentRowCellCount = line.size();
            if(currentRowCellCount == 0) {
                continue;
            }
            if(headerCellCount != currentRowCellCount) {
                cellCount = 0;
                throwInvalidStateError('CSV row has '+currentRowCellCount+' cell(s), expected '+headerCellCount+' cell(s)');
            }
            results.add(line);
            rowCount++;
        } while(csv != '');
        return results;
    }
    List<String> readLine() {
        List<String> results = new List<String>();
        cellCount = 1;
        while(csv != '' && csv.left(1) != END_LINE) {
            results.add(readCell());
            if(csv != '') {
                if(csv.indexOfAny(END_LINE+END_CELL) != 0) {
                    throwInvalidStateError('Unexpected CSV state while reading line');
                }
                if(csv.left(1) == END_CELL) {
                    csv = csv.substring(1);
                    if(csv.left(1) == END_LINE) {
                        results.add('');
                    }
                }
            }
        }
        if(csv.length()>0) {
            csv = csv.substring(1);
        }
        return results;
    }
    String readCell() {
        csv = csv.replaceFirst('^ *','');
        return csv.left(1) == ESCAPE_CHAR? readQuotedCell(): readUnquotedCell();
    }
    String readQuotedCell() {
        String result = '';
        do {
            Integer nextEscape = csv.indexOf(ESCAPE_CHAR,1);
            if(nextEscape == -1) {
                throwInvalidStateError('Quoted cell does not end in a quote');
            }
            result += csv.substringBefore(ESCAPE_CHAR)+ESCAPE_CHAR;
            csv = csv.substringAfter(ESCAPE_CHAR);
        } while(csv != '' && !csv.left(1).containsAny(END_LINE+END_CELL));
        return result.removeStart(ESCAPE_CHAR).removeEnd(ESCAPE_CHAR).replace(ESCAPE_CHAR+ESCAPE_CHAR,ESCAPE_CHAR);
    }
    String readUnquotedCell() {
        Integer nextCellBreak = csv.indexOfAny(END_CELL+END_LINE);
        Integer nextEscape = csv.indexOf(ESCAPE_CHAR);
        String result = csv;
        if(nextCellBreak != -1 && nextEscape != -1 && nextCellBreak > nextEscape) {
            throwInvalidStateError('You cannot include a quoted character in an unquoted cell');
        }
        if(nextCellBreak != -1) {
            result = csv.left(nextCellBreak);
            csv = csv.substring(nextCellBreak);
        }
        return result;
    }
    void throwInvalidStateError(String message) {
        throw new InvalidStateException(String.format('{0} @ row {1} cell {2}', new String[] { message, ''+rowCount, ''+cellCount}));
    }
    public class InvalidStateException extends Exception { }
}