3

I have created a batch that will copy some event fields to a csv and attach it to an email. The problem I have is that I failed to replicate some characters (• bullet point, in this case) from the actual data. Is it possible to do it in the csv and if so, how?

Note: I have tried making a report in salesforce and exported the same data to csv using Unicode (UTF-8)and it has the same issue.

enter image description here

Here is my code:

public class AS_batchCsvCreator implements Database.Batchable<sObject>, Database.Stateful{

public String THECSVDATA ='';

public AS_batchCsvCreator() {
    THECSVDATA += 'Id,Subject,Description \n';
}

public Database.QueryLocator start(Database.BatchableContext BC) {
    return Database.getQueryLocator('Select Id,Subject,Description from Event');
}

public void execute(Database.BatchableContext BC, List<Event> scope) {
    for(Event evnt : scope){
        List<String> csvRow = new List<String>();

        csvRow.add((String.valueOf(evnt.Id) != null)?String.valueOf(evnt.Id):'');
        csvRow.add((String.valueOf(evnt.Subject) != null)?String.valueOf(evnt.Subject):'');
        csvRow.add((String.valueOf(evnt.Description) != null)?String.valueOf(evnt.Description):'');
        String appData = listToCSVStringConverter(csvRow);
        THECSVDATA += appData;
        THECSVDATA += '\n';
    }
}

public void finish(Database.BatchableContext BC) {
    AsyncApexJob batchResult = [Select Id, Status, NumberOfErrors, JobItemsProcessed,  
    TotalJobItems, CreatedBy.Email, ExtendedStatus from AsyncApexJob where Id = :bc.getJobId()];
    try {
        Messaging.EmailFileAttachment csvAttachment = new Messaging.EmailFileAttachment();
        Blob csvBlob = blob.valueOf(THECSVDATA);

        String csvName = 'Test.csv';
        csvAttachment.setFileName(csvName);
        csvAttachment.setBody(csvBlob);

        Messaging.SingleEmailMessage email = new Messaging.SingleEmailMessage();

        String[] toAddresses = new String[] {batchResult.CreatedBy.Email};

        String subject = 'Event CSV)';
        email.setSubject(subject);
        email.setToAddresses(toAddresses);
        email.setPlainTextBody('Attached is the event csv.');
        email.setFileAttachments(new Messaging.EmailFileAttachment[]{csvAttachment});
        Messaging.SendEmailResult[] r = Messaging.sendEmail(new Messaging.SingleEmailMessage[]{email});
    } catch(Exception ex) {

    }
} 

public static String listToCSVStringConverter(List<String> theString) {
    String returnString = '';
    for(String newStr : theString) {
        returnString += newStr.escapeCsv()+',';
    }
    return returnString;
}

}

1 Answer 1

4

This appears to be classic Microsoft Excel behavior when confronted with UTF-8 text. Excel likes to interpret a valid UTF-8 bytestream as if it were encoded in Windows Codepage 1252 or other encodings.

In UTF-8, the bullet character is three bytes, 0xE2 0x80 0xA2. In both Windows CP 1252 and ISO Latin-1, 0xE2 = 'â'. I'm not entirely clear on how the remaining two bytes are mangled into what looks like an em dash in the screenshot, but I think it's clear that this is an encoding issue.

Salesforce represents text in UTF-8 and doesn't provide robust text-encoding support, so this issue really has to be addressed on the client side. The easiest solution is simply adopting the free, open-source LibreOffice, which offers excellent support for UTF-8 encoded CSV files.

As cropredy notes, you can do some level of filtering on your characters in Apex, but for free-text fields this is unlikely to be a sustainable solution - you would have to remove all multibyte characters to prevent this issue. Additionally, it won't be fast, and for large amounts of text you could encounter performance issues. This is a situation where Salesforce really just requires that you use client software that fully supports UTF-8, and Excel does not.

2
  • so you mean there is nothing I can do in apex to apply it in csv?
    – Micz Domz
    Commented Mar 1, 2018 at 5:04
  • @MiczDomz - Apex (and all of SFDC) works in UTF-8 so you are generating a UTF-8 CSV - the issue is with Excel which does not support UTF-8 in CSVs. You could preprocess the text in Apex and change bullets for asterisks if that is the only flawed character. Otherwise client needs to use Google Sheets, LibreOffice, OpenOffice
    – cropredy
    Commented Mar 1, 2018 at 6:03

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .