Convert SObject.toString output to a CSV value line (Regexp..)

Question

I am running into ToManyStatements Limits while creating a CSV blob for with the following code

for(SObject row: list) {
  for(String fieldName : allFields) {
    line += fieldName + ',';
  }
  csvAsString += '\n' + line;
}

What I need is probably a clever regexp that replaces Salesforce SObject String format like:

CustomObject__c: {field__c=value, field__c=value,...}

to a csv value line like:

value,value,value,....

Then I could change my code like this:

for(SObject row: list) {
  csvAsString += '\n' + row.doRegExpMagic();
}

To create the first line of the CSV I probably need another regexp to extract the field list

field1__c,field2_c,...

from my SOQL query

SELECT field1__c,field2_c,... FROM CustomObject__c ...

All you Regexp gurus out there.. please help ;-)

Answer 1

Creating a CSV file blob in Apex

My recommendations:

1. use Batch Apex, passing in the initial Lists as stateful variables using Database.Stateful, and then creating a CSV row out of each SObject during the Batch Apex execute method. In the finish method of your Batch Apex, send an email containing the generated CSV. Using Batch Apex to do the CSV row generation will avoid the Too Many Script Statements error, since Batch Apex limits are reset for each Batch Apex execute method invocation. The resultant CSV file can be stored as a String instance variable, and converted to a Blob in the finish method, then emailed to the user requesting the export.
2. Create the blob client-side using JavaScript (no script statement limits there!), and sent to Apex using one of the following
   • JS Remoting
   • VF ActionFunction
   • HTTP POST Request

Example of Batch Apex

Here is some sample code for the JavaScript method:

1. To create CSV fields in JavaSCript, here's some a method that will work:

   // Escapes a String such that it can be used in a CSV cell
   // Based on Apache Commons StringEscapeUtils library
   // (see Wikipedia and RFC 4180)
   // Parameters:
   //  input - the input CSV column String, may be null
   //  Returns:
   //  the input String, enclosed in double quotes 
   //      if the value contains a comma, newline or double quote, 
   //      '' if null string input.
   //  Also, any double quote characters in the value
   // are escaped with another double quote.
   
   var QUOTE = '"',
       ESCAPED_QUOTE = "\"\"",
       CHARS_THAT_MUST_BE_QUOTED = [ ',' , '"' , '\n' ];
   
   var escapeCSVCell = function(c) {
      if ( c.indexOf( QUOTE ) > -1 ) c = c.replace(/"/g, ESCAPED_QUOTE );
         $.each(CHARS_THAT_MUST_BE_QUOTED,function(i,char){
            if (c.indexOf(char) > -1) {
               c = QUOTE + c + QUOTE;
               return false;
            }
         });
      return c;
   }
   
   // Call escapeCSVCell on each CSV field
   

Parsing CSV files in Apex

Number One: Do not roll your own CSV Parser: use someone else's! There are a lot of complexities to parsing IETF RFC 4180 - compliant CSV files that you will uncover, one after another, if you try to do roll your own parser. Trust me, not worth it.

And guess what? You don't have to!

Marty Chang has done the Salesforce/Apex developer commmunity a huge service by writing a rock-solid, IETF RFC 4180 compliant CSV Parser FOR APEX!!!

Here is the link to download the 2 files he wrote for this purpose, CSVParser and CSVReader. I've used it before, trust me, it handles everything, even newlines embedded in your CSV fields (try writing that yourself --- NOT straightforward.)

Here's how to use it:

// (Only necessary if your CSV file starts as a string)
Blob csvBlob = Blob.valueOf(csvString);

// Convert your csv file into a list of CSV fields
List<List<String>> fieldsByLine = CSVParser.readIETFRFC4180CSVFile(csvBlob);

Yeah, it's that easy!

His solution uses some good RegEx to bypass the "too many statements" limit.

Creating SObject records from CSV Rows in Apex

As far as creating records from these CSV rows, i've done this before using Batch Apex. Convert your CSV files into rows during the Batch Apex job and create rows one-by-one in the execute method. Also, shameless plug, there's actually an IETF RFC 4180-compliant CSV Import Wizard built-in to Skuid, so if you are open to using an external library, why roll your own parser?

Parsing CSV Rows in Apex

Winter 13 (API v26) has some awesome methods for escaping/unescaping CSV fields:

• String.escapeCsv(String textToMakeIntoACSVField)
• String.unescapeCsv(String csvField)

Answer 2

Here's a rather rough way of achieving what you want, though remember that if you need the fields in a certain order you're going to have trouble with splitting the string representation of the object in this way!

String theObject = 'CustomObject__c: {field__c=value, field__c=value}';
theObject = theObject.subString(theObject.indexOf('=') + 1, theObject.length() - 1);
String output = theObject.replaceAll(',.*=', ',');

Probably not fool proof*, but hopefully will get you on your way.

• Definitely not fool proof, you wouldn't want to work on some data where a fool has put an comma in a field, for example.

Answer 3

You can also read and process the CSV file in the execute method. http://developer.financialforce.com/customizations/importing-large-csv-files-via-batch-apex/

Answer 4

I'm going to guess there's a simple reason for this - but instead of Apex, what about doing a weekly export of the data (Data Export under Setup)? Auto-converts to CSV. I'm assuming you need it more on-demand?

Answer 5

I would do this client side.

You can perform multiple queries and save all the results in a single array, and generate a csv for thousands and thousands of records this way because limits won't get in the way.

You don't need to involve the server at all, except for fetching the data. Use downloadify (https://github.com/dcneiner/Downloadify) to initiate a purely client side download.