CFLib.org – Common Function Library Project

CSVFormat(query [, qualifer] [, columns])

Last updated August 26, 2008

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

 
Rated 5 time(s). Average Rating: 2.2

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:

view plain print about
<cfscript>
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:

view plain print about
<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>
blog comments powered by Disqus

Search CFLib.org


Latest Additions

Dave Anderson Dave Anderson added
iniToStruct
20 day(s) ago

Dave Anderson Dave Anderson added
deDupeArray
20 day(s) ago

Richard Richard added
dice
22 day(s) ago

Isaac Dealey Isaac Dealey added
getRelative
a while ago

Top Rated

Darwan Leonardo Sitepu backupDatabase
Rated 5.0, 22 time(s)

Barney Boisvert indentXml
Rated 5.0, 10 time(s)

Kevin Pepperman generateSsccAsn
Rated 5.0, 4 time(s)

Raymond Camden highlightAndCrop
Rated 5.0, 4 time(s)

Created by Raymond Camden / Design by Justin Johnson