export to csv from sql on the fly

For downloading ?

Here is an example of writing a csv file that has client info to a file on the server:

<!--- start the file by writing the first line --->
<cffile
action="WRITE"
addnewline="Yes"
file="myquery.csv"
output="contact,client,street,city,state,zip">

<!--- loop through the query, writing each row to a new line in the file --->
<cfoutput query="getNames">
<cfset FullName = getNames.FirstName & ' ' & getNames.LastName>
<cffile
action="APPEND"
addnewline="Yes"
file="myquery.csv"
output="""#FullName#"",""#getNames.client#"",""#getNames.street#"",""#getNames.city#"",""#getNames.state#"",""#getNames.zip#""">
</cfoutput>

If you wanted to then provide a link to the user to that file, you could then add:

<a href="http://mysite.com/myquery.csv">your file</a>

or, you could redirect the user after the file is created (the user's browser would determine whether it displayed on their screen or if the user is prompted to save or open):

<cflocation url="http://mysite.com/myquery.csv">

To download a CSV file (without writing a file to the server). You could use the CSVFormat() function from cflib.org. It converts a query to to a csv formatted string.
http://www.cflib.org/udf.cfm?ID=404

The code for the download would be:
<CFHEADER NAME="Content-Disposition" VALUE="attachment; filename=yourFileName.csv">
<CFCONTENTTYPE VALUE="text/csv">
<cfoutput>#CSVFormat(mytestquery, "'")#</cfoutput>

Or to have the CSV open in Excel:
<CFHEADER NAME="Content-Disposition" VALUE="attachment; filename=yourFileName.xls">
<CFCONTENTTYPE VALUE="application/vnd.ms-excel">
<cfoutput>#CSVFormat(mytestquery, "'")#</cfoutput>

theamzngq, ok let me try that..

Also, is there a way to not store it on the server but immediately pull up the Save/Cancel dialog so the user can save it to their machine.  If it must be saved to the server, then how about if the user clicks Save in the dialog box, then saves it, then the file on the server gets deleted.  Any ideas?

@COwebmaster,

My suggestion doesn't write a file to the server at all.

Using CFFILE is also good. But its better to write to the file once, intstead of appending each line,  to avoid a lot of expensive IO operations.

<cfset theFileContents = "ColumnHeader1,ColumnHeader2" />
<cfloop query="yourQuery">
        <cfset theFileContents = theFileContents & "#Column1#,#Column2##NewLine#"/>
</cfloop>

<cffile action="WRITE" output="#theFileContents#" ....>

aqx , how can I tie that into creating the csv on the fly though?

aqx , ok then how can I tie in your suggestion above with the download?

The code for the download would be:
<CFHEADER NAME="Content-Disposition" VALUE="attachment; filename=yourFileName.csv">
<CFCONTENTTYPE VALUE="text/csv">
<cfoutput>#CSVFormat(mytestquery, "'")#</cfoutput>

Or to have the CSV open in Excel:
<CFHEADER NAME="Content-Disposition" VALUE="attachment; filename=yourFileName.xls">
<CFCONTENTTYPE VALUE="application/vnd.ms-excel">
<cfoutput>#CSVFormat(mytestquery, "'")#</cfoutput>

The UDF accepts a query.  So run your cfquery, then pass the query object  to the CSVFormat function:

<CFHEADER NAME="Content-Disposition" VALUE="attachment; filename=yourFileName.csv">
<CFCONTENTTYPE VALUE="text/csv">
<cfoutput>#CSVFormat(YourCfquery, "'")#</cfoutput>

COwebmaster,

A full example would be someting like this

<cfquery nme="YourQuery" datasource="abc">
     SELECT Column1, Column2, Column2 FROM YourTable
</cfquery>

<CFHEADER NAME="Content-Disposition" VALUE="attachment; filename=yourFileName.csv">
<CFCONTENTTYPE VALUE="text/csv">
<cfoutput>#CSVFormat(YourQuery, "'")#</cfoutput>

If you're familiar with how to use UDFs, agx's suggestion is much cleaner and easier to use.  If you did want to go the file route, ags'x example will write the csv file.  I'm not sure why you're asking how to "tie that into creating the csv on the fly".  That is how you would do it...

Ok, so using this example will:

1.) create the csv file
2.) Open up a Save/Cancel dialog box

Correct?

<cfquery nme="YourQuery" datasource="abc">
     SELECT Column1, Column2, Column2 FROM YourTable
</cfquery>

<CFHEADER NAME="Content-Disposition" VALUE="attachment; filename=yourFileName.csv">
<CFCONTENTTYPE VALUE="text/csv">
<cfoutput>#CSVFormat(YourQuery, "'")#</cfoutput>

sorry, I've got heavy post lag looks like...I've missed a ton of responses.  Carry on!

@COwebmaster,

Yes it will.


Btw, the basic concept of converting the query to CSV format is the exactly same as what theamzngq described.  The UDF just does the looping for you. The only real difference is whether you want to create a physical file on the server or not.  

@COwebmaster

Correction, it will do that IF you've installed the UDF correctly :)
http://www.cflib.org/udf.cfm?ID=404

I don't know if my host has.  They are using cf7 though

Or you could rework theamzngq's code to remove the cffile.

Ok, I tried but I get an error saying:

A tag starting with 'CF' has been detected. This tag is not supported by this version of ColdFusion. Please verify your typo and try again.

Unknown tag: CFCONTENTTYPE.  
ColdFusion cannot determine how to process the tag CFCONTENTTYPE because the tag is unknown and not in any imported tag libraries. The tag name might be misspelled.

Oops. There should be a space between cfcontent and type. Try this

<CFHEADER NAME="Content-Disposition" VALUE="attachment; filename=yourFileName.csv">
<CFCONTENT           type="text/csv">
<cfoutput>#CSVFormat(mytestquery, "'")#</cfoutput>

Ok, I was able to diaplay output to the browser, just need to pull up the dialog box to save it instead of displaying it on the browser

To your response:

"Btw, the basic concept of converting the query to CSV format is the exactly same as what theamzngq described.  The UDF just does the looping for you. The only real difference is whether you want to create a physical file on the server or not. "

- I just want the user to download the csv not display it in the browser.

By using content-disposition "attachment" (not "inline)  it should prompt to download
<CFHEADER NAME="Content-Disposition" VALUE="attachment; filename=yourFileName.csv">

But you could also try using an XLS extension:
<CFHEADER NAME="Content-Disposition" VALUE="attachment; filename=yourFileName.xls">
<CFCONTENTTYPE VALUE="application/vnd.ms-excel">

Oh wait, I think I got it.

How do i change the column names?  Also, the first line is empty.  how do i get rid of that?

I don't get any empty line. Can you post the first 2 or three rows of the csv?

[blank]
id example
1  test1
2  test2

How would I change the column header "example" to "list"

The blank is probably caused by NOT putting cfoutput on the same line, like this:
<cfoutput>#CSVFormat(mytestquery, "'")#</cfoutput>

The simplest way to change the headers is to alias the column names in your sql query.

SELECT  Column1 AS [TheFirstColumn], Column2 AS [TheSecondColumn] ....

Or you could change the function so it does not append th column headers, and append the header names you want manually.

Looks great.  Thanks aqx for your help.