Dynamic csv generation for a dynamic soql query in apex

Question

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> {'[email protected]'};
    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});

Answer 1

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 //