There is a great UDF on cflib.org for doing this using JAVA. In my
experience trying to do it with CF, if you have more than 25000 records it
takes too long or times out.
Here is the UDF, it's updated for my purposes, but roughly intact:
<cfscript>
/**
* Convert the query into a CSV format using Java StringBuffer Class.
*
* @param query The query to convert. (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 Qasim Rasheed ([EMAIL PROTECTED])
* @version 1, September 2, 2004.
*/
function QueryToCSV2(query)
{
var csv = createobject( 'java', 'java.lang.StringBuffer');
var i = 1;
var j = 1;
var cols =
"Salutation,Last_Name,First_Name,City,State,PostalCode,Country,Email";
var headers = "Salutation,Last Name,First Name,City,State,Zip
Code,Country,Email";
var endOfLine = chr(13) & chr(10);
if (arraylen(arguments) gte 2) headers = arguments[2];
if (arraylen(arguments) gte 3) cols = arguments[3];
if (not len( trim( cols ) ) ) cols = query.columnlist;
if (not len( trim( headers ) ) ) headers = cols; headers = listtoarray(
headers ); cols = listtoarray( cols );
for (i = 1; i lte arraylen( headers ); i = i + 1) csv.append( '"' &
headers[i] & '",' ); csv.append( endOfLine );
for (i = 1; i lte query.recordcount; i= i + 1)
{
for (j = 1; j lte arraylen( cols ); j=j + 1)
{
//removed because it was causing a problem
//if (isNumeric( query[cols[j]][i] ) ) csv.append( query[cols[j]][i] &
',' );
if (isDate( query[cols[j]][i] ) ) csv.append( '"' &
DateFormat(query[cols[j]][i], "mm/dd/yyyy") & '",' );
else csv.append( '"' & query[cols[j]][i] & '",' );
} csv.append( endOfLine );
}
return csv.toString(); }
</cfscript>
Here is the code that sends it to the user:
<cfif variables.qSearch.recordcount NEQ 0>
<cfoutput>
<cfset Variables.FileName = "DataExtract#RandRange(1,1000000)#.csv">
<cffile action="WRITE"
file="#ExpandPath('generated/#Variables.FileName#')#" output="#QueryToCSV2(
variables.qSearch)#">
<cfheader name="content-disposition"
value="attachment;filename=#Variables.FileName#">
<cfcontent type="text/plain"
file="#ExpandPath('generated/#Variables.FileName#')#" deletefile="No">
</cfoutput>
<cfelse>
No records matched your query.
</cfif>
HTH
--
Alan Rother
Macromedia Certified Advanced ColdFusion MX 7 Developer
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Message: http://www.houseoffusion.com/lists.cfm/link=i:4:244285
Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4
Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
