CFLib.org – Common Function Library Project

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

Last updated August 26, 2008

author

Jeff Howden

Version: 2 | Requires: CF5 | 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:

<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:

/**
 * 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;
}
blog comments powered by Disqus

Search CFLib.org


Latest Additions

Kevin Cotton added
date2ExcelDate
May 5, 2016

Raymond Camden added
CapFirst
April 25, 2016

Chris Wigginton added
loremIpsum
January 18, 2016

Gary Stanton added
calculateArrival...
November 19, 2015

Sebastiaan Naafs - van Dijk added
getDaysInQuarter
November 13, 2015

Created by Raymond Camden / Design by Justin Johnson