SOQL to CSV Utility

Recently, I needed to come up with a way for some simple case metrics to be available to users, these metrics need to contain cases that the user may not necessarily have visibility too.

Now, there are a couple of options here;

• Dashboard
• Scheduled report
• Something custom

Now the first two options are the ones I would normally use, both dashboards and report can be scheduled to be run as a user that has access to all cases, and emailed to those who need to see them, dashboards having the added advantage that they can be viewed any time.

But what if you need the data in excel? Well, you could copy/paste from the scheduled email, or get someone to export it for you. But neither are a great solution.

So you could build something custom, which is what I have done here;

Now, this tool, on its own isn't much use to your average end user, as a working knowledge of SOQL is required to use it. However, this can very easily be extended to store frequently used ‘exports' in a custom setting, use custom permissions to grant access to the tool, attach the resulting CSV to a record, or be scheduled to send the CSV via email at a set interval using scheduleable apex.

Here is how it works;

The SOQL query entered in the text box is run against the database, returning a list of sObjects. Once we have this list we need to construct a CSV.

The first line of the CSV is the column headings, taken from the text box on the page (or alternatively, the API names of the field entered as part of the query string). For the sake of simplicity, I have simply extracted these fields from the SOQL query (the fields in the list are always in the same order as in the query), however you could use the SObjectDescribe to do fancier things here.

The code then loops through the list of sObjects, and subsequently the list of field names, each loop writing a line of the CSV and then adding a new line at the end

 for(Sobject s :sobjects) {
            for(String fn :fieldNames) {
                csvReport += '"' + s.get(fn) + '"' + ',';
            }
            csvReport += '\r\n';
        }

`
We then return the CSV as a string to the VisualForce controller, and display it on a page. In order to make the page ‘render as' a CSV, rather than HTML or text, we need to set the content type attribute of , this also allows us to set the filename of the downloaded CSV.

html
<apex:page controller="SOQLCSVController"
contentType="application/csv#{!fileName}.csv"cache="true" showheader="fase">
<apex:outputText value="{!stringCSV}" />
</apex:page>
`
That really is it, on pressing the Download button, the user is sent to the Download VisualForce page, which renders as a CSV and the browser downloads.

If you wanted to send the generated CSV via email, or attach it to a record you could simply call the Blob class to and attach the resulting blob to an email or record.

The complete code for this example is sitting on my github, feel free to use it and extend it as you wish.