Embed URL

HTTPS clone URL

SSH clone URL

You can clone with HTTPS or SSH.

Download Gist

CF method to convert a query to csv output.

View test-query-to-csv-func.cfm
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>
clb39 commented

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.

Owner

That's odd

buffer.append( 
  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.