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