0

I need to generated a CSV like file (pipe delimited) that will contain tens of thousands of lines where the final file might be larger than 5MB or might incur in SF limits. Is there a way to get around the problem and maybe load the file in chunks ?

The user needs one large file and they cannot break it into parts.

POC Class:

public class POCPipeDelimited {
    public List<Contact> cs{get; set;}
    public POCPipeDelimited() {
        cs = new List<Contact>();
        //this is just an example,
        //I will pull data from various sObject as well as hav calculated fields
        for (Contact c : [Select id, Name from Contact ]){
            cs.add(c); cs.add(c); cs.add(c); cs.add(c); cs.add(c);
            cs.add(c); cs.add(c); cs.add(c); cs.add(c); cs.add(c);
            cs.add(c); cs.add(c); cs.add(c); cs.add(c); cs.add(c);
            cs.add(c); cs.add(c); cs.add(c); cs.add(c); cs.add(c);
            cs.add(c); cs.add(c); cs.add(c); cs.add(c); cs.add(c);
            cs.add(c); cs.add(c); cs.add(c); cs.add(c); cs.add(c);
            cs.add(c); cs.add(c); cs.add(c); cs.add(c); cs.add(c);
            cs.add(c); cs.add(c); cs.add(c); cs.add(c); cs.add(c);
        }
    }
}

POC Page (btw, how do I avoid the first empty line?):

<apex:page Controller="POCPipeDelimited" contentType="text/plain" cache="false">
<apex:repeat value="{!cs}" var="contact">
{!contact.Name}|{!contact.Id}
</apex:repeat>
</apex:page>

naturally those are just examples that probably have many issues, but I do not even know where to start to possibly generate

5
  • So, just to re-confirm, you are looking to generate a CSV that is pipe delimited?
    – Mahmood
    Commented May 2, 2017 at 21:39
  • If you want to avoid the blank line you can generate the entire string in Apex and then do <apex:page ...>{!data}</apex:page> without any line breaks.
    – Adrian Larson
    Commented May 2, 2017 at 21:42
  • 1
    Does it need to come out of a Visualforce page? If you could use the APIs and external code the process could be a lot easier. Commented May 2, 2017 at 21:42
  • 1
  • @AdrianLarson, I tried and that removes completely the new line
    – Fabrizio
    Commented May 3, 2017 at 13:41

1 Answer 1

1

No-code solution

I have used much simpler solution in scenarios, like the one you have at hand (custom delimiter and all). You could use Jitterbit Cloud Dataloader to achieve this task without writing a single line of code.

  1. Once you get started with Jitterbit, after login-in, goto Query
  2. Select output file path
  3. Now, on the Target step, choose "Select an existing file format"
    • from the picklist, select "Create New File format"
    • press the button "create manually", then you should see the options to select a custom delimiter.
  4. put "|" (pipe) there and you are all set.

You can schedule your exports using Jitterbit as well, just dig-in to the application.

enter image description here

Code Solution

If you must go with the code solution, and you also have to pull and process thousands of records, a better approach is to use Batch Apex. I wrote a batch apex to perform a similar task some time ago, i.e. generate CSV with loads of data. I'm giving the pseudo code below for you to review

Code

global class GenerateCSV implements Database.Batchable<SObject>, Database.Stateful {
    string[] lines;
    
    global Database.QueryLocator start(Database.BatchableContext bc) {
    
        lines = new String[0];
        return Database.getQueryLocator( 'Select Id, <fields...> From <Your_Object> <WHERE condition>');
    }
    global void execute(Database.BatchableContext bc, SObject[] scope) {
        for(Sobject record: scope) {
            String line = '';
            // header row - check the header row
            if ( lines.size() == 0 ) {
                line = '<HeaderColumn1>|<HeaderColumn2>|....|<HeaderColLast>';
                lines.add(line);
            }
            else {
                // build your csv line here
                line += string.valueof(record.get('id')) 
                +'|' + string.valueof(record.get('<Field_Name>')).replace(',','-') ;
                lines.add(line);
            }
        }
    }
    global void finish(Database.batchablecontext bc) {
        // process the file here using the lines generated in the "execute()" method.
    }
}
1
  • 1
    I will try to create a POC with the code you suggested. I have to use code as I am not exporting just an sObject but I am exporting fields from various sObjects and some are calculated
    – Fabrizio
    Commented May 3, 2017 at 13:45

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .