Creating CSV Files out of Thin Air
If you don't have as much control over the webserver, or simply want an easy way to produce a file for a single user regardless of how many people are using the server, there is an easy way to do this with ASP.
Most ASP users are familiar with Response.Write and Response.Redirect, but there are other cool methods associated with it that don't get as much play as they probably should. In the case below we are going to use Response.ContentType and Response.AddHeader to easily generate a csvFile on the fly.
First let's connect to our database and loop through the values, storing them in a variable called csvFile:
Dim conn, SQL, rs, csvFile
Set conn = Server.CreateObject("ADODB.Connection")
' Change to your Parameters
SQL = "SELECT * FROM MyTable"
Set rs = Server.CreateObject("ADODB.RecordSet")
' Create a Header Record
csvFile = "Name, Cell, Work" & vbCrLf
rs.Open SQL, conn, 1, 3
Do while not rs.EOF
csvFile = csvFile & rs("name") & "," & rs("cell") & rs("workphone") & vbcrlf
Set conn = Nothing
set rs = Nothing
Once the data has been collected and store in the variable csvFile we write to the screen for the first time.
Response.ContentType = "application/csv"
Response.AddHeader "content-disposition", "inline; filename=export.csv"
The above lines let the web browser know that what it is about to receive should not be handle as a web page, but instead should be treated like another type of file (in this case, CSV). If you have a handler set for CSV Files (And if you use Microsoft Office, the chances are you do, and that it is Excel) then instead of a web page being displayed, you will get and Open/Save box, similar to this:
Click Open, and the file will be loaded directly into Excel. The end user will still need to adjust columns and make the headings look pretty, but it gives them 90% of what they need and the rest should be under their control anyway, IMHO.