CFLib.org – QueryToCsv

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

Last updated June 26, 2002

Download UDF

author

adgnot sebastien
Send Email

Version: 1 | Requires: ColdFusion 5 | Library: DataManipulationLib

Rated 3 time(s). Average Rating: 2.3

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:

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

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 (sadgnot@ogilvy.net) 
 * @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>