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();
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.

Copy link

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