CFLib.org – Common Function Library Project

QueryToCSV2(query[, headers][, cols])

Last updated March 23, 2005

author

Qasim Rasheed

Version: 1 | Requires: CF6 | Library: StrLib

Description:
This function is a modified version of QueryToCSV by Adgnot Sebastien. It converts the query into a CSV format using Java StringBuffer Class in java.lang package which is considerably faster as compared to regular string. Excellent performance improvement if you are manipulating large queries.

Return Values:
Returns a string.

Example:

<cfset data = querynew( 'a,b,c,d,e,f,g,h,i,j,k')>
<cfloop from="1" to="100" index="i">
	<cfset queryaddrow( data )>
	<cfloop list="#data.columnlist#" index="j">
		<cfset querysetcell( data, j, j & randrange( 100,500 ) )>
	</cfloop>
</cfloop>
<cfset test = QueryToCSV2( data, "AA,BB,CC", "a,b,c" )>

Parameters:

Name Description Required
query The query to convert. Yes
headers A list of headers to use for the first row of the CSV string. Defaults to all the columns. No
cols The columns from the query to transform. Defaults to all the columns. No

Full UDF Source:

/**
 * Convert the query into a CSV format using Java StringBuffer Class.
 * 
 * @param query 	 The query to convert. (Required)
 * @param headers 	 A list of headers to use for the first row of the CSV string. Defaults to all the columns. (Optional)
 * @param cols 	 The columns from the query to transform. Defaults to all the columns. (Optional)
 * @return Returns a string. 
 * @author Qasim Rasheed (qasimrasheed@hotmail.com) 
 * @version 1, March 23, 2005 
 */
function QueryToCSV2(query){
	var csv = createobject( 'java', 'java.lang.StringBuffer');
	var i = 1;
	var j = 1;
	var cols = "";
	var headers = "";
	var endOfLine = chr(13) & chr(10);
	if (arraylen(arguments) gte 2) headers = arguments[2];
	if (arraylen(arguments) gte 3) cols = arguments[3];
	if (not len( trim( cols ) ) ) cols = query.columnlist;
	if (not len( trim( headers ) ) ) headers = cols;
	headers = listtoarray( headers );
	cols = listtoarray( cols );
	
	for (i = 1; i lte arraylen( headers ); i = i + 1)
		csv.append( '"' & headers[i] & '",' );
	csv.append( endOfLine );
	
	for (i = 1; i lte query.recordcount; i= i + 1){
		for (j = 1; j lte arraylen( cols ); j=j + 1){
			if (isNumeric( query[cols[j]][i] ) )
				csv.append( query[cols[j]][i] & ',' );
			else
				csv.append( '"' & query[cols[j]][i] & '",' );
			
		}
		csv.append( endOfLine );
	}
	return csv.toString();
}
blog comments powered by Disqus

Search CFLib.org


Latest Additions

Kevin Cotton added
date2ExcelDate
May 5, 2016

Raymond Camden added
CapFirst
April 25, 2016

Chris Wigginton added
loremIpsum
January 18, 2016

Gary Stanton added
calculateArrival...
November 19, 2015

Sebastiaan Naafs - van Dijk added
getDaysInQuarter
November 13, 2015

Created by Raymond Camden / Design by Justin Johnson