CFLib.org – Common Function Library Project

Description:
Transform a query result into a csv formatted variable.

Return Values:
Returns a string.

Example:

<cfset test_query = QueryNew("ValueField, DisplayField")>
<cfset QueryAddRow(test_query, 3)>
<cfset QuerySetCell(test_query,"ValueField","blue", 1)>
<cfset QuerySetCell(test_query,"DisplayField","my favorite color is blue", 1)>
<cfset QuerySetCell(test_query,"ValueField","changed the text for the heck of it", 2)>
<cfset QuerySetCell(test_query,"DisplayField","blah blah blah", 2)>
<cfset QuerySetCell(test_query,"ValueField","Louisiana", 3)>
<cfset QuerySetCell(test_query,"DisplayField","The State of Louisiana", 3)>
<cfoutput>
<pre>
#querytoCSV(test_query)#
</pre>
</cfoutput>

Parameters:

Full UDF Source:

<cfscript>
/**
 * Transform a query result into a csv formatted variable.
 * 
 * @param query 	 The query to transform. (Required)
 * @param headers 	 A list of headers to use for the first row of the CSV string. Defaults to cols. (Optional)
 * @param cols 	 The columns from the query to transform. Defaults to all the columns. (Optional)
 * @return Returns a string. 
 * @author adgnot sebastien (&#115;&#97;&#100;&#103;&#110;&#111;&#116;&#64;&#111;&#103;&#105;&#108;&#118;&#121;&#46;&#110;&#101;&#116;) 
 * @version 1, June 26, 2002 
 */
function QueryToCsv(query){
	var csv = "";
	var cols = "";
	var headers = "";
	var i = 1;
	var j = 1;
	
	if(arrayLen(arguments) gte 2) headers = arguments[2];
	if(arrayLen(arguments) gte 3) cols = arguments[3];
	
	if(cols is "") cols = query.columnList;
	if(headers IS "") headers = cols;
	
	headers = listToArray(headers);
	
	for(i=1; i lte arrayLen(headers); i=i+1){
		csv = csv & """" & headers[i] & """;";
	}

	csv = csv & chr(13) & chr(10);
	
	cols = listToArray(cols);
	
	for(i=1; i lte query.recordCount; i=i+1){
		for(j=1; j lte arrayLen(cols); j=j+1){
			csv = csv & """" & query[cols[j]][i] & """;";
		}		
		csv = csv & chr(13) & chr(10);
	}
	return csv;
}
</cfscript>