Export Recordset Data to CSV Using ASP/VBScript

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
    ' https://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
    ' https://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.