ASP Q&A;: Creating a CVS File Using Classic ASP

This is the fourth in a series of tips being posted based on the most read posts in one of our ASP Forums. (Previous Q&A)

Problem / Question:

I have generated a report that displays on the screen as a table in the web browser. Now the customer would like to export to a CSV file so they can import the data into whatever app they'd like.

The fields to be exported are:

• CustomerName
• ContactName
• Firstinput
• EmployeeName
• EmployeeRate
• Hours
• TOTAL

I've found a few examples online, but none that I could get to work.

Solution:

I would not try to generate the CSV file on the same ASP page that generates browser output. The  "option" to export to CSV should be a link or button that takes me to a completely different page. If you do that, then code like the following should work to generate  the entire CVS file without the need for looping or other oddities:

CONST QT = """"
...
SQL = "SELECT field1, field2, ... FROM table ... "
Set RS = conn.Execute( SQL )
If RS.EOF Then Response.End 

For f = 0 To RS.Fields.Count-1
    hdr = hdr & "," & QT & RS.Fields(f).Name & QT
Next
Response.Write Mid(hdr,2) & vbNewLine ' lop off leading comma

prefix = QT 
midfix = QT & "," & QT
suffix = QT & vbNewLine
rows = RS.getString( , , midfix, suffix & prefix )
Response.Write prefix & Left(rows, Len(rows) - Len(prefix) )
RS.Close
...

Based on posts by forum members ITJoe and Bill Wilkerson