Export data to a file using apex

Question

I have a requirement to

1. Run SELECT query on custom object
2. export the data to csv file
3. Automate / schedule step-1 and step-2 .

I know how to achieve step-3, by using apex scheduler class. But I couldn't find anything on exporting data from apex code. We have been using Jitterbit, but now our client wants to get rid of it, and use only salesforce.

Answer 1

If you're concerned about time or memory limits, use a batch class. This is how I would perform the export of a potentially unknown size (10k+ records).

global class X implements Database.Batchable<SObject>, Database.Stateful {
    string[] Lines;

    global Database.QueryLocator start(Database.BatchableContext bc) {
        lines = new String[0];
        return Database.getQueryLocator( your-query-here );
    }
    global void execute(Database.BatchableContext bc, SObject[] scope) {
        for(Sobject record: scope) {
            String line = '';
            // build your csv line here
            lines.add(line);
        }
    }
    global void finish(Database.batchablecontext bc) {
        String finalResults = String.join(lines, '\n');
        lines = null; // Free temp memory to avoid heap errors
        // proceed to send attachment by email
    }
}

The best performance I've gotten for a synchronous export (that is, a direct query, plus processing about 30 fields per record) is about 5,000 records before a CPU timeout exception, so if this is a concern, definitely go with a batch. If you're confident that you can beat the timeout, you could perform the query and translation directly in the scheduler class itself without resorting to a batch.