Outputting CSV from an ASP page
I’ve recently had the need to write an ASP page which reads a SQL database and 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
1
2<%
3'Open a connection to the database
4Set MyConn = Server.CreateObject("ADODB.Connection")
5MyConn.Open "connection string"
6'Create the Recordset
7Set RS = MyConn.Execute("SQL Query")
8'Create a Variable for a Double Quote "
9dq=Chr(34)
10'Write out header.
11Response.AddHeader "Content-Disposition", "attachment;filename=output.csv"
12Response.ContentType = "text/csv"
13'Write out the Column Headings
14For Each F In RS.Fields
15 Response.write dq+ F.Name+dq+","
16Next
17response.write vbcrlf
18'Write out the body
19Do While NOT RS.EOF
20 For Each F In RS.Fields
21 Response.write RS(F.Name)
22 Response.write ","
23 Next
24 Response.write vbcrlf
25 RS.MoveNext
26Loop
27%>
As this exports to a CSV (comma delimited text) file, this file can also be opened in pretty much any other spreadsheet.