Creating CSV Files out of Thin Air

CSV (Comma Separated Values) are handy if you want to quickly import information into Excel, Access etc. If you have your own web server, or a co-located server, then it is relatively easy to create CSV files from your database on the fly, although it can be a bit of a pain to manage if you have a lot of hits on your server.

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:

Option Explicit
Dim conn, SQL, rs, csvFile
Set conn = Server.CreateObject("ADODB.Connection")

' Change to your Parameters
conn.Open "DSN=myDSN;DATABASE=MyDatabase;UID=myUserid;PWD=myPassword"
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
rs.MoveNext: Loop

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"
Response.Write(csvFile)

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.