The CSV (Comma-Separated Values) file format is a text file format in which you can store tabular data, such as data from a database table. This format is can be read by various spread sheet programs such as Microsoft Excel and OpenOffice.org Calc. This format is not suitable for storing binary data.
The following ASP/VBScript code sample allows you to export data from a database table to a CSV file. The code accesses the table through an ADODB Recordset object, formats the data into the CSV format and sends the data to the browser along with appropriate response headers. This code should be saved as an ASP script on a webserver. You should see an "Open/Save As..." dialog when you access the script through a web browser.
<% sub Write_CSV_From_Recordset(RS) ' ' Export Recordset to CSV ' http://salman-w.blogspot.com/2009/07/export-recordset-data-to-csv-using.html ' ' This sub-routine Response.Writes the content of an ADODB.RECORDSET in CSV format ' The function closely follows the recommendations described in RFC 4180: ' Common Format and MIME Type for Comma-Separated Values (CSV) Files ' http://tools.ietf.org/html/rfc4180 ' ' @RS: A reference to an open ADODB.RECORDSET object ' if RS.EOF then ' ' There is no data to be written ' exit sub end if dim RX set RX = new RegExp RX.Pattern = "\r|\n|,|""" dim i dim Field dim Separator ' ' Writing the header row (header row contains field names) ' Separator = "" for i = 0 to RS.Fields.Count - 1 Field = RS.Fields(i).Name if RX.Test(Field) then ' ' According to recommendations: ' - Fields that contain CR/LF, Comma or Double-quote should be enclosed in double-quotes ' - Double-quote itself must be escaped by preceeding with another double-quote ' Field = """" & Replace(Field, """", """""") & """" end if Response.Write Separator & Field Separator = "," next Response.Write vbNewLine ' ' Writing the data rows ' do until RS.EOF Separator = "" for i = 0 to RS.Fields.Count - 1 ' ' Note the concatenation with empty string below ' This assures that NULL values are converted to empty string ' Field = RS.Fields(i).Value & "" if RX.Test(Field) then Field = """" & Replace(Field, """", """""") & """" end if Response.Write Separator & Field Separator = "," next Response.Write vbNewLine RS.MoveNext loop end sub ' ' EXAMPLE USAGE ' ' - Open a RECORDSET object (forward-only, read-only recommended) ' - Send appropriate response headers ' - Call the function ' dim RS1 set RS1 = Server.CreateObject("ADODB.RECORDSET") RS1.Open "SELECT * FROM TABLE_NAME_HERE", "CONNECTION_STRING_HERE", 0, 1 Response.ContentType = "text/csv" Response.AddHeader "Content-Disposition", "attachment;filename=export.csv" Write_CSV_From_Recordset RS1 %>
Note: It is also possible to export data from an ADODB Recordset using database connectivity drivers.