CSVFormat(query [, qualifer] [, columns])
Last updated August 26, 2008
Version: 2 | Requires: ColdFusion 5 | Library: DataManipulationLib
Description:
CSVFormat accepts the name of an existing query and converts it to CSV format, using the column names as headers. It can wrap each column value with an optional qualifier. Very handy for use inside a cffile tag to create Excel files from queries.
Return Values:
A CSV formatted string.
Example:
mytestquery = QueryNew('FirstName,LastName,Title');
QueryAddRow(mytestquery, 3);
QuerySetCell(mytestquery, 'FirstName', 'Steve', 1);
QuerySetCell(mytestquery, 'LastName', 'Drucker', 1);
QuerySetCell(mytestquery, 'Title', 'CEO', 1);
QuerySetCell(mytestquery, 'FirstName', 'Dave', 2);
QuerySetCell(mytestquery, 'LastName', 'Watts', 2);
QuerySetCell(mytestquery, 'Title', 'CTO', 2);
QuerySetCell(mytestquery, 'FirstName', 'Dave', 3);
QuerySetCell(mytestquery, 'LastName', 'Gallerizzo', 3);
QuerySetCell(mytestquery, 'Title', 'VP', 3);
</cfscript>
<cfoutput>
<pre>
#CSVFormat(mytestquery, "'")#
</pre>
</cfoutput>
Parameters:
| Name | Description | Required |
|---|---|---|
| query | The query to format. | Yes |
| qualifer | A string to qualify the data with. | No |
| columns | The columns ot use. Defaults to all columns. | No |
Full UDF Source:
<cfscript>
/**
* CSVFormat accepts the name of an existing query and converts it to csv format.
* Updated version of UDF orig. written by Simon Horwith
*
* @param query The query to format. (Required)
* @param qualifer A string to qualify the data with. (Optional)
* @param columns The columns ot use. Defaults to all columns. (Optional)
* @return A CSV formatted string.
* @author Jeff Howden (cflib@jeffhowden.com)
* @version 2, August 26, 2008
*/
function CSVFormat(query) {
var returnValue = ArrayNew(1);
var rowValue = '';
var columns = query.columnlist;
var qualifier = '';
var i = 1;
var j = 1;
if(ArrayLen(Arguments) GTE 2) qualifier = Arguments[2];
if(ArrayLen(Arguments) GTE 3 AND Len(Arguments[3])) columns = Arguments[3];
returnValue[1] = ListQualify(columns, qualifier);
ArrayResize(returnValue, query.recordcount + 1);
columns = ListToArray(columns);
for(i = 1; i LTE query.recordcount; i = i + 1)
{
rowValue = ArrayNew(1);
ArrayResize(rowValue, ArrayLen(columns));
for(j = 1; j LTE ArrayLen(columns); j = j + 1)
rowValue[j] = qualifier & query[columns[j]][i] & qualifier;
returnValue[i + 1] = ArrayToList(rowValue);
}
returnValue = ArrayToList(returnValue, Chr(13));
return returnValue;
}
</cfscript>
Search CFLib.org
Latest Additions
structBlend
20 day(s) ago
IsZIPUK
20 day(s) ago
getTagContentAll
26 day(s) ago
ListReturnDuplicat...
1 month(s) ago
ListReturnDuplicat...
1 month(s) ago