Here's a combination of things I do for this to 1) ensure that all users get
a download window (including Macintosh users) and 2) get the fastest time to
write the file.
I do it with a combination of CFM and Java. I've got a Cold Fusion custom
tag written in Java -- I've pasted the source code in here. If you have
access to the CF Administrator and can register a Java Custom tag, this is a
GREAT way to speed up writing the query to a file.
-------------------------------------------------------------------
CF code. The query to be dumps exists and is named "theQuery"
-------------------------------------------------------------------
<cfset basePath = getCurrentTemplatePath()>
<cfset tmpDir = application.tmpDir>
<cfset fileName = "#trim(session.user_login)#_#dateformat(now(),
"yyyymmdd")#_#timeformat(now(),"HHmm")#.csv"> <!--- creates a filename
unique for this user at this minute --->
<!--- This line writes the query out to the file.
see Java code for this below --->
<cfx_query2csv query="theQuery" filename="#tmpDir#\#fileName#">
<cf_browser> <!--- sets local variable "os" --->
<cfif os is "MAC">
<cfset headerValue = "attachment; filename=#fileName#">
<cfelse>
<cfset headerValue = "filename=#fileName#">
</cfif>
<cfif os is "MAC">
<cfset fileMimeType = "application/x-msexcel">
<cfelse>
<cfset fileMimeType = "application/unknown">
</cfif>
<CFHEADER NAME="Content-Disposition"
VALUE="#headerValue#">
<CFCONTENT TYPE="#fileMimeType#"
DELETEFILE="yes"
FILE="#tmpDir#\#fileName#"
RESET="yes">
---------------------------------------------------------------
Java code for cfx_query2csv
---------------------------------------------------------------
/* import com.allaire.cfx.CustomTag;
import com.allaire.cfx.Query;
import com.allaire.cfx.Request;
import com.allaire.cfx.Response; */
import com.allaire.cfx.*;
import java.io.FileWriter;
import java.io.IOException;
public class query2csv implements CustomTag
{
public void processRequest( Request request, Response response )
throws Exception
{
//get query structure
Query qMyQuery = request.getQuery() ;
if ( qMyQuery == null )
{
throw new Exception(
"Missing QUERY parameter. " +
"You must pass a QUERY parameter in " +
"order for this tag to work correctly." ) ;
}
//number of rows
int nNumRows = qMyQuery.getRowCount() ;
// Get the list of columns from the query
String[] columns = qMyQuery.getColumns() ;
int nNumColumns = columns.length ;
/* debug print
response.write( "Query has " + nNumRows + " rows and " + nNumColumns +
" Columns" ) ;
*/
String strFileName = request.getAttribute("filename");
FileWriter to_file = new FileWriter(strFileName);
try {
// Write column headers
for( int i=0; i<nNumColumns; i++ )
{ to_file.write( columns[i] + "," ) ; }
to_file.write( "\n" ) ; /* write end of line character */
// loop through rows and columns writing each out
for( int iRow=1; iRow<=nNumRows; iRow++ )
{
for( int iCols=1; iCols<=nNumColumns; iCols++ )
{
to_file.write( "\"" + qMyQuery.getData( iRow, iCols )
+ "\"");
/* if this is not the last column add a comma */
if ( iCols != nNumColumns) to_file.write( "," ) ;
} /* end for loop through columns */
to_file.write( "\n" ) ; /* write end of line character */
} /* end for loop through rows */
} /* end try */
// Always close the file, even if exceptions were thrown
finally {
if (to_file != null)
try { to_file.close(); } catch (IOException
e) { ; }
} /* end finally */
} /* end processRequest method */
} /* end query2csv class */
-----Original Message-----
From: tom muck [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, May 15, 2001 12:04 PM
To: CF-Talk
Subject: Re: CSV file;CFFILE;Sybase IQ12
Wrap your CFOUTPUT Query in a CFHEADER tag:
<CFHEADER NAME="Content-Disposition" VALUE="inline; filename=test.csv">
<CFCONTENT TYPE="application/unknown">
<cfoutput query="rs"> #rs.column1#, #rs.column2##chr(10)#</cfoutput>
<cfabort>
tom
www.basic-ultradev.com
"Carol Bluestein" <[EMAIL PROTECTED]> wrote in message
[EMAIL PROTECTED]">news:[EMAIL PROTECTED]...
> Hi all. Hoping someone who has "BEEN THERE, DONE THAT" can help.
>
> Have to create a csv file from a cfquery resultset which can then be
imported
> into a number
> of programs that do statistical analysis.
>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Structure your ColdFusion code with Fusebox. Get the official book at
http://www.fusionauthority.com/bkinfo.cfm
Archives: http://www.mail-archive.com/[email protected]/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
