CFLib.org – QueryToCSV2

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

Last updated March 23, 2005

author

Version: 1 | Requires: ColdFusion MX | Library: StrLib

Rated 15 time(s). Average Rating: 4.5

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:

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:

	
	<cfscript>
/**
 * 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();
}
</cfscript>