2

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;
}

}

3

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.

| improve this answer | |
  • so you mean there is nothing I can do in apex to apply it in csv? – Micz Domz Mar 1 '18 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 Mar 1 '18 at 6:03

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.