13

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

  • I reckon you might be out of luck with this approach as I believe when you cast lists to strings it skips elements when the length > 10. Can you create a formula field representing a CSV row for a given record and just query that? – Matt Lacey Nov 19 '12 at 11:15
  • I don't cast a list to a string just a single record. I checked it out with a 500 field object and all fields and values are preserved. – Robert Sösemann Nov 19 '12 at 11:17
  • Ah of course! Good point – Matt Lacey Nov 19 '12 at 11:18
  • You are FAST, man! – Robert Sösemann Nov 19 '12 at 11:21
  • Can't you use batch job? – doga Nov 19 '12 at 11:41
7

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)
|improve this answer|||||
  • You're getting my +1 because it's a useful tool for future use. But I believe it won't help in this case. "I am running into ToManyStatements Limits while creating a CSV blob", the very first line in the question ;) – eyescream Nov 19 '12 at 12:32
  • 2
    Can you create the blob client-side? That's what I do. Create it using JavaScript, then sent to Apex using Visualforce Action, JS Remoting, or POST data. – zachelrath Nov 19 '12 at 12:35
  • 1
    zachelrath: I don't get your solution. I don't want to parse CSV. I just need to write a CSV and I need to use System code to not run into limits.. Creating a CSV in browser memory is a too hacky solution for me. – Robert Sösemann Nov 19 '12 at 12:43
  • 1
    @zachelrath, to use Batch writing files would need to be chunkifiable in any way. But as Files don't allow an append there is no way to do this. Or do you see one? – Robert Sösemann Nov 19 '12 at 20:37
  • 1
    @RobertS, as I mentioned at the top of my post, "Files" are just Blobs, and Blobs can be created just by doing Blob.valueOf(someString), and you don't have to do this until you're ready to send the final file, i.e. in the finish method of your Batch Apex. So store the String as an instance variable on your Batch class, and add to it during each iteration of the execute method. Then in the finish method, just call Blob.valueOf(csvFileString), and set this as the body of a Messaging.EmailFileAttachment – zachelrath Nov 20 '12 at 0:23
4

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.
|improve this answer|||||
  • 1
    That looks good and easy. But I'm just thinking of the case when fields contain one of those chars: ,=... – Robert Sösemann Nov 19 '12 at 11:38
  • Doh, that's an altogether much more complicated problem! Would it be easier to fire off a batch job for this? :) – Matt Lacey Nov 19 '12 at 11:44
  • What I am doing is (mis)using email services to generate big CSV files. Because their heap is 36 MB and not 6 or 12 like in Batch. But I see that this replace thing won't work. – Robert Sösemann Nov 19 '12 at 11:56
  • My other post about the CSV thing is here salesforce.stackexchange.com/questions/3894/… – Robert Sösemann Nov 19 '12 at 11:56
  • If you could create the CSV data in a formula field (or even a trigger?) then you could then extract this data with dataloader to build up the files. – Matt Lacey Nov 19 '12 at 12:03
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/

|improve this answer|||||
0

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?

|improve this answer|||||
0

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.

|improve this answer|||||

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.