Skip to content
Create a gist now

Instantly share code, notes, and snippets.

Embed URL


Subversion checkout URL

You can clone with
Download ZIP
CF method to convert a query to csv output.
* queryToCsv
* Allows us to pass in a query object and returns that data as a CSV.
* This is a refactor of Ben Nadel's method,
* @param {Query} q {required} The cf query object to convert. E.g. pass in: qry.execute().getResult();
* @param {Boolean} hr {required} True if we should include a header row in our CSV, defaults to TRUE
* @param {String} d {required} Delimiter to use in CSV, defaults to a comma (,)
* @return {String} CSV content
public string function queryToCsv(required query q, required boolean hr = true, required string d = ","){
var colNames = listToArray( lCase(arguments.q.columnlist) );
var newLine = (chr(13) & chr(10));
var buffer = CreateObject('java','java.lang.StringBuffer').Init();
// Check if we should include a header row
// append our header row
ArrayToList(colNames,arguments.d) & newLine
// Loop over query and build csv rows
for(var i=1; i <= arguments.q.recordcount; i=i+1){
// this individual row
var thisRow = [];
// loop over column list
for(var j=1; j <= arrayLen(colNames); j=j+1){
// create our row
thisRow[j] = replace( replace( arguments.q[colNames[j]][i],',','','all'),'""','""""','all' );
// Append new row to csv output
JavaCast( 'string', ( ArrayToList( thisRow, arguments.d ) & iif(i < arguments.q.recordcount, "newLine","") ) )
return buffer.toString();

Nice script, one minor change to your header section, the "& newLine" adds a new line for every column header, turning them into rows. Altering the code to below fixes the problem
// append our header row
ArrayToList(colNames,arguments.d) // remove line feed & newLine
//append line feed after header row

The only other change I needed to make for my purposes was to change the colNames section (the script exports the columns in Alpha order). The only ways around this are to pass a manual list or use a SQL view (so you can select the column list). Neither is global, so I skipped adding this code.


That's odd

  ArrayToList(colNames,arguments.d) & newLine

Should do the trick and only uses one buffer.append()

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Something went wrong with that request. Please try again.