public
Last active

CF method to convert a query to csv output.

  • Download Gist
test-query-to-csv-func.cfm
ColdFusion
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50
<cfscript>
/**
* 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, http://www.bennadel.com/blog/1239-Updated-Converting-A-ColdFusion-Query-To-CSV-Using-QueryToCSV-.htm
* @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
if(arguments.hr){
 
// append our header row
buffer.append(
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
buffer.append(
JavaCast( 'string', ( ArrayToList( thisRow, arguments.d ) & iif(i < arguments.q.recordcount, "newLine","") ) )
);
}
 
return buffer.toString();
 
};
</cfscript>

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
buffer.Append(
ArrayToList(colNames,arguments.d) // remove line feed & newLine
);
//append line feed after header row
buffer.Append(newLine);

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

buffer.append( 
  ArrayToList(colNames,arguments.d) & newLine
);

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

Please sign in to comment on this gist.

Something went wrong with that request. Please try again.