3

When constructing a csv via apex how do we avoid hardcoding the recordString assignment within the query loop?

Note I have a dynamic List<String> fieldsList that represent field names. For question simplicity assume that I already build a dynamic soql that runs successfully but I'm having trouble removing the hardcoding from the recordString assignment. Ideally this would not be hardcoded so that i can dynamically generate the csv behind the already-dynamic soql query. Example of generating csv from apex is found here https://developer.salesforce.com/forums/?id=906F000000090JcIAI but both the blog and my post hardcode the recordString whereas ideally the recordString variable assignment would also be dynamic (ie follows the List<String> fieldsList).

What's an efficient way to replace the hardcoded assignment of recordString with all elements of my generated List<String> fieldsList ? Assuming it does not crash the loop perhaps i must iterate the fieldsList string to build the assignment syntax im seeking for recordString = such as 'a'+'.'+fieldsList[0] +','+ 'a'+'.'+fieldsList[1], ... n where n is the size of my fieldsList. Has anyone dynamically assigned field values to the recordString in csv generation before? How did you do it? Note I guarantee all string elements of fieldsList represent valid fields on Account.

List<Account > acclist = [Select ... from Account limit 10]; //assume a working "dynamic query" where i use  List<String> fieldsList for the field selections
string header = '...'; // assume i build my header without issues, based on fieldsList.
string finalstr = header ;
for(Account a: acclist)
{
  //the challenge. How to replace hardcoded assignments with all elements of fieldsList (a.element1, a.element2, etc) within this loop? 
      string recordString = '"'+a.id+'","'+a.Name+'","'+a.CreatedDate+'","'+a.LastModifiedDate +'"\n';      
      finalstr = finalstr +recordString;
    }
    Messaging.EmailFileAttachment csvAttc = new Messaging.EmailFileAttachment();
    blob csvBlob = Blob.valueOf(finalstr);
    string csvname= 'Account.csv';
    csvAttc.setFileName(csvname);
    csvAttc.setBody(csvBlob);
    Messaging.SingleEmailMessage email =new Messaging.SingleEmailMessage();
    String[] toAddresses = new list<string> {'test@test.com'};
    String subject ='Account CSV';
    email.setSubject(subject);
    email.setToAddresses( toAddresses );
    email.setPlainTextBody('Account CSV ');
    email.setFileAttachments(new Messaging.EmailFileAttachment[]{csvAttc});
    Messaging.SendEmailResult [] r = Messaging.sendEmail(new Messaging.SingleEmailMessage[] {email});
3

If possible, construct the data client-side with something like papaparse. This is much less error-prone and will run a lot faster besides. If you're insisting on doing this in Apex, consider using an existing CSV library if you can find one.

Generally speaking, you would load the data into a structure like this:

List<Map<String, Object>> csvValues = new List<Map<String, Object>>();
Set<String> fieldNames = new Set<String>();
for(sObject record: records) {
  Map<String, Object> fieldValues = record.getPopulatedFieldsAsMap();
  csvValues.add(fieldValues);
  fieldNames.addAll(fieldValues.keySet());
}
String[] allData = new String[0];
// Build header
String[] tempRow = new String[0];
for(String fieldName: fieldNames) {
  tempRow.add(fieldName.escapeCSV());
}
allData.add(String.join(tempRow,','));
for(Map<String, Object> valuesFromRecord: csvValues) {
  tempRow.clear();
  for(String fieldName: fieldNames) {
    Object value = valuesFromRecord.get(fieldName);
    if(value == null) {
      value = '';
    }
    tempRow.add((''+value).escapeCSV());
  }
  allData.add(String.join(tempRow,',');
}
String finalCsv = String.join(allData,'\n');
allData.clear(); // free heap //
1
  • 1
    Brian for president! ( thanks for the great answer, the client-server considerations, and for opening my eyes to getPopulatedFieldsAsMap() and escapeCSV() Jun 17 '20 at 17:35

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.