Outputting CSV from an ASP page

I’ve recently had the need to write an ASP page which outputs into Microsoft Excel- a user clicks on a link to this ASP page and Excel opens up with a new spreadsheet of the data. This is how I did it……

<%
'Open a connection to the database
Set MyConn = Server.CreateObject("ADODB.Connection")
MyConn.Open "connection string"
'Create the Recordset
Set RS = MyConn.Execute("SQL Query")
'Create a Variable for a Double Quote "
dq=Chr(34)
'Write out header.
Response.AddHeader "Content-Disposition", "attachment;filename=output.csv"
Response.ContentType = "text/csv"
'Write out the Column Headings
For Each F In RS.Fields
  Response.write dq+ F.Name+dq+","
Next
response.write vbcrlf
'Write out the body
Do While NOT RS.EOF
  For Each F In RS.Fields
    Response.write RS(F.Name)
    Response.write ","
  Next
  Response.write vbcrlf
  RS.MoveNext
Loop
%>

As this exports to a CSV (comma delimited text) file, this file can also be opened in pretty much any other spreadsheet.

Leave a Reply

Your email address will not be published. Required fields are marked *