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.