Oh my god. It's full of code!

Export SOQL Query as CSV

Hey guys,
Long time no blog! Sorry about that, been kind of busy and honestly haven’t had too many interesting tidbits to share. However, I think I have something kind of neat to show you. I had a project recently where the user wanted to be to create a custom SOQL query and export the results as a CSV file. I don’t know why they didn’t want to use regular reports and export (my guess is they figured the query may be too complex or something) but it sounded fun to write, so I didn’t argue.

Breaking this requirement down into it’s individual parts revealed the challenges I’d have to figure out solutions for:
1) Allow a user to create a custom SOQL query through the standard interface
2) Extract and iterate over the fields queried for to create the column headings
3) Properly format the query results as a CSV file
4) Provided the proper MIME type for the visualforce page to prompt the browser to download the generated file

As it turns out, most of this was pretty easy. I decided to create a custom object called ‘SOQL_Query_Export__c’ where a user could create a record then specify the object to query against, the fields to get, the where condition, order by and limit statements. This would allow for many different queries to be easily created and saved, or shared between orgs. Obviously the user would have to know how to write SOQL in the first place, but in this requirement that seemed alright. The benefit as well is that an admin could pre-write a query, then users could just run it whenever.

With my data model/object created now I set about writing the apex controller. I’ll post it, and explain it after.

public class SOQL_Export {

    public SOQL_Query_Export__c exporter     {get;set;}
    public list<sobject>        queryResults {get;set;}
    public list<string>         queryFields  {get;set;}
    public string               queryString  {get;set;}
    public string               fileName     {get;set;}
    
    public SOQL_Export(ApexPages.StandardController controller) 
    {
        //Because the fields of the exporter object are not refernced on the visualforce page we need to explicity tell the controller
        //to include them. Instead of hard coding in the names of the fields I want to reference, I simply describe the exporter object
        //and use the keyset of the fieldMap to include all the existing fields of the exporter object.
        
        //describe object
        Map<String, Schema.SObjectField> fieldMap = Schema.SOQL_Query_Export__c.sObjectType.getDescribe().fields.getMap();
        
        //create list of fields from fields map
        list<string> fields = new list<string>(fieldMap.keySet());
        
        //add fields to controller
        if(!Test.isRunningTest())
        {
            controller.addFields(fields);
        }
        //get the controller value
        exporter = (SOQL_Query_Export__c) controller.getRecord();

        //create a filename for this exported file
        fileName = exporter.name + ' ' + string.valueOf(dateTime.now());
                
        //get the proper SOQL order direction from the order direction on the exporter object (Ascending = asc, Descending = desc)
        string orderDirection = exporter.Order_Direction__c == 'Ascending' ? 'asc' : 'desc';
        
        //create a list of fields from the comma separated list the user entered in the config object
        queryFields =  exporter.fields__c.split(',');
        
        //create the query string using string appending and some ternary logic
        queryString = 'select ' + exporter.fields__c + ' from ' + exporter.object_name__c;
        queryString += exporter.where_condition__c != null ? ' where ' + exporter.where_condition__c : '';
        queryString += exporter.Order_by__c != null ? ' order by ' + exporter.Order_by__c + ' ' + orderDirection :'';
        queryString += exporter.Limit__c != null ? ' limit ' +string.valueOf(exporter.Limit__c) : ' limit 10000';
        
        //run the query
        queryResults = database.query(queryString);
        
        
    }

    //creates and returns a newline character for the CSV export. Seems kind of hacky I know, but there does not seem to be a better
    //way to generate a newline character within visualforce itself.
    public static String getNewLine() {
      return '\n';
    }
}

Because I was going to use the SOQL_Query_Export__c object as the standard controller my apex class would be an extension. This meant using the controller.addFields method (fields not explicitly added by the addFields method or referenced in the visualforce page are not available on the record passed into the controller. So if I had attempted to reference SOQL_Query_Export__c.Name without putting it in my add fields method, or referencing it on the invoking page it would not be available). Since my visualforce page was only going to be outputting CSV content, I have to manually add the fields I want to reference. I decided instead of hard coding that list, I’d make it dynamic. I did this by describing the the SOQL_Query_Export__c object and passing the fields.getMap() keyset to the controller.addFields method. Also, just as something to know, test classes cannot use the addFields method, so wrap that part in an if statement.

Next it’s just simple work of constructing a filename for the generated file, splitting the fields (so I can get an array I can loop over to generate the column headers for the CSV file). Then it’s just generating the actual query string. I  used some ternary statements since things like order by and limit are not really required. I did include a hard limit of 10000 records if one isn’t specified since that is the largest a read only collection of sobjects can be. Finally we just run the query. That last method in the class is used by the visualforce page to generate proper CSV line breaks (since you can’t do it within the page itself. Weird I know).

So now with the controller, we look at the page.

<apex:page standardController="SOQL_Query_Export__c" cache="true"  extensions="SOQL_Export" readOnly="true" showHeader="false" standardStylesheets="false" sidebar="false" contentType="application/octet-stream#{!fileName}.csv">

  <apex:repeat value="{!queryFields}" var="fieldName">{!fieldName},</apex:repeat>{!newLine}
  
  <apex:repeat value="{!queryResults}" var="record"><apex:repeat value="{!queryFields}" var="fieldName">{!record[fieldName]},</apex:repeat>{!newLine}</apex:repeat>
  

</apex:page>

I know the code looks kind of run together. That is on purpose to prevent unwanted line breaks and such in the generated CSV file. Anyway, the first line sets up the page itself obviously. Removes the stylesheets, header, footer, and turns on caching. Now there are two reasonably important things here. The readOnly attribute allows a visualforce collection to be 10000 records instead of only 1000, very useful for a query exporter. The second is the ‘contentType=”application/octet-stream#{!fileName}.csv”‘ part. That tells the browser to treat the generated content as a CSV file, which in most browsers should prompt a download. You can also see that the filename is an Apex property that was generated by the class.

With the page setup, now we just need to construct the actual CSV values. To create the headers of the file, we simply iterate over that list of fields we split in the controller, putting a comma after each one (according to CSV spec trailing commas are not a problem so I didn’t worry about them). You can see I also invoke the {!newLine} method to create a proper CSV style newline after the header row. If anyone knows of a way to generate a newline character in pure visualforce I’d love to hear it, because I couldn’t find a way.

Lastly we iterate over the query results. For each record in the query, we then iterate over each fields. Using the bracket notation we can the field from the record dynamically. Again we create a newline at the end of each record. After this on the SOQL Export object I simple created a button that invoked this page passing in its record ID. That newly opened window would provide the download and then user could then close it (I’m experimenting with ways to automatically close the window once the download is done, but it’s a low priority and any solution would be rather hacky).

There you have it. A simple SOQL query export tool. I have this packaged up, but I’m not 100% I can give that URL away right now. I’ll update this entry if it turns out I’m allowed to share it. Anyway, hope this someone, or if nothing else shows a couple neat techniques you might be able to use.

3 responses

  1. Scott McFarlane

    That’s a great idea, if you are allowed to share your tool it would be great if you could make a new post as well as updating here, just so it pops up in RSS readers/etc. Thanks!

    August 3, 2015 at 1:14 pm

  2. Lalitha Bhat

    This looks great. I am working on something similar. I would like to get your thoughts on how to get the values of the fields dynamically in apex? i.e what’s the equivalent for !record[field] notation you have used in your VF page? I want to get the queried field values and populate the merge fields of an communication template and post it to an external interface.

    September 23, 2015 at 6:18 pm

    • Not totally sure but you might be looking for sObject.get(fieldName). That’s how you can get fields dynamically from an sObject. It also works on generic sObject types. EX

      Account testAcc = new Account();
      testAcc.name = ‘My Test’;
      system.assertEqualst(testAcc.get(‘name’),testAcc.name);

      October 21, 2015 at 1:30 am

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s