CFLib.org – Common Function Library Project

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

Last updated June 26, 2002
Download UDF

author

adgnot sebastien                                  adgnot sebastien

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>

Search CFLib.org


Latest Additions

Shawn Porter Shawn Porter added
DeMoronize
3 hour(s) ago

Chris Carey Chris Carey added
readPropertiesFi...
1 day(s) ago

Randy Johnson Randy Johnson added
lastDayofWeek
3 day(s) ago

Frank Marion Frank Marion added
sitemapPing
7 day(s) ago

Top Rated

James Sleeman                                     QuickSort
Rated 5.0, 3 time(s)

Barney Boisvert indentXml
Rated 5.0, 3 time(s)

Nathan Dintenfass                                 queryColumnsToSt...
Rated 5.0, 3 time(s)

Kevin Pepperman generateSsccAsn
Rated 5.0, 3 time(s)

Created by Raymond Camden / Design by Justin Johnson