Skip to content

Instantly share code, notes, and snippets.

What would you like to do?
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();

This comment has been minimized.

Copy link

clb39 commented May 25, 2012

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.


This comment has been minimized.

Copy link
Owner Author

CreativeNotice commented May 25, 2012

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
You can’t perform that action at this time.