output a table to a csv file

darklink
darklink used Ask the Experts™
on
Ok, I have a table being generated as the result of a SQL.  What I want is to put a button on this asp page called "download" that when clicked prompts the user with a save as dialog and allows them to save the table on the users pc as a ".csv" file.  I am appending the asp code for the page here.  I know I'm probably boneheading it.  Any help will be appreceated.


<%@LANGUAGE="VBSCRIPT"%>
<%
'Dimension variables
Dim adoCon          'Holds the Database Connection Object
Dim strUserName
Dim rstrackerevents
Dim strtrackerevents

'If the session variable doesn't exist, redirect to the login page
If  Session("logged") <> "true" Then
response.Write("<div align=""center""><strong><a href=""http://dxclvl2.mcilink.com/database/users/LOGIN.ASP"">Click here to return to the login page</a></strong><BR>")
      Response.End
End If

  strUserName = Session("user")

'Create an ADO connection object
Set adoCon = Server.CreateObject("ADODB.Connection")

'Set an active connection to the Connection object using a DSN-less connection
adoCon.Open "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & Server.MapPath("proj.mdb")

'Create an ADO recordset object
Set rstrackerevents = Server.CreateObject("ADODB.Recordset")

'Initialise the strSQL variable with an SQL statement to query the database
strtrackerevents = "SELECT qrytimetoday.* FROM qrytimetoday where login='" & strUserName & "'"

Const adOpenStatic = 3
'Open the recordset with the SQL query
rstrackerevents.Open strtrackerevents, adoCon
%>
<html>
<head>
<title>Untitled Document</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
</head>

<body>
        <table width="100%" border="3" cellpadding="0" cellspacing="0" bordercolor="#000000">
                  <tr>
                             <td width="6"><b>Entry date</b></td>
                    <td width="6%"><b>Notify By</b></td>
                    <td width="6%"><b>Support To:</b></td>
                    <td width="6%"><b>Vendor:</b></td>
                    <td width="6%"><b>Product Type:</b></td>
                    <td width="6%"><b>DXC:</b></td>
                    <td width="6"><b>Action:</b></td>
                    <td width="5%"><b>Hours:</b></td>
                    <td width="5%"><b>Minutes:</b></td>
                    <td width="6%"><b>Duty Shift:</b></td>
                              <td width="6%"><b>Project ame:</b></td>
                    <td width="6%"><b>Element Name</b></td>
                              <td width="8"><b>Tik #</b></td>
                              <td width="20%"><b>comments</b></td>
                  </tr>
                </table>
<%
                        Do While not rstrackerevents.EOF
      Response.Write("                <table width=""100%"" border=""1"" cellpadding=""0"" cellspacing=""0"" bordercolor=""#000000"">" & vbCrLf)
        Response.Write("                                <tr> " & vbCrLf)
      Response.Write("                                    <td width=""6%"">" & rstrackerevents("ent_date") & "</td>" & vbCrLf)
      Response.Write("                                    <td width=""6%"">" & rstrackerevents("notify") & "</td>" & vbCrLf)
      Response.Write("                                    <td width=""6%"">" & rstrackerevents("support") & "</td>" & vbCrLf)
       Response.Write("                                   <td width=""6%"">" & rstrackerevents("platform") & "</td>" & vbCrLf)
         Response.Write("                                 <td width=""6"">" & rstrackerevents("support_2") & "</td>" & vbCrLf)
        Response.Write("                                  <td width=""6%"">" & rstrackerevents("tac") & "</td>" & vbCrLf)
        Response.Write("                                  <td width=""6%"">" & rstrackerevents("action") & "</td>" & vbCrLf)
        Response.Write("                                  <td width=""5%"">" & rstrackerevents("hrs_elap") & "</td>" & vbCrLf)
        Response.Write("                                  <td width=""5%"">" & rstrackerevents("min_elap") & "</td>" & vbCrLf)
        Response.Write("                                  <td width=""6%"">" & rstrackerevents("admin") & "</td>" & vbCrLf)
        Response.Write("                                  <td width=""6%"">" & rstrackerevents("main_proj_name") & "</td>" & vbCrLf)
        Response.Write("                                  <td width=""6%"">" & rstrackerevents("element_name") & "</td>" & vbCrLf)
        Response.Write("                                  <td width=""6%"">" & rstrackerevents("ticket_number") & "</td>" & vbCrLf)
        Response.Write("                                  <td width=""20%"">" & rstrackerevents("COMMENTS") & "</td>" & vbCrLf)
       Response.Write("                                 </tr>" & vbCrLf)
       Response.Write("                               </table>" & vbCrLf)      

                        rstrackerevents.movenext
loop
%>
        </BODY></HTML>
        <%
'Reset server objects
rstrackerevents.Close
Set rstrackerevents = Nothing

Set adoCon = Nothing
%>

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Commented:
I don't know if this is the best solution but its working.

Sample file "test.csv":
<%
Response.ContentType = "text/csv"
%>
Entry date;Notify By;Support To;etc
<%
For i = 1 To 10
      Response.Write       "rstrackerevents(""ent_date"")" & i & ";" & _
                  "rstrackerevents(""notify"")" & i & ";" & _
                  "rstrackerevents(""support"")" & i & ";" & _
                  "rstrackerevents(""etc"")" & i  & vbCrLf
Next
%>
You have to add your database connection, etc.

Then you have to configure the IIS that the IIS process this .csv file like .asp.
Go to the IIS Managment Console and create a virtual Directory “CSV”. Right Click on that CSV and Click “Configuration” below Application Settings. Click Add.
Executable: C:\WINDOWS\system32\inetsrv\asp.dll
Extension: .csv
Limit to: GET
Script Engine: check
OK

Put this test.csv file into that folder the virtual directory is pointing to.

Link to this file <a href=”/CSV/test.csv”>download</a> and a download dialog comes up with your csv file.


After you print the table . Redirect to a different  page at Onclick,
There just add the code
    Response.ContentType = "text/csv"
If you add just this, the csv file will be opened in the browser window itself.
So add the code
  Response.AddHeader "content-disposition","attachment;filename=yourfile.csv"
This will open the save as window.

Then write the usual DB connect code and display of record set
'Create an ADO connection object
Set adoCon = Server.CreateObject("ADODB.Connection")

'Set an active connection to the Connection object using a DSN-less connection
adoCon.Open "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & Server.MapPath("proj.mdb")

'Initialise the strSQL variable with an SQL statement to query the database
strSQL = "SELECT qrytimetoday.* FROM qrytimetoday where login='" & strUserName & "'"

'Create an ADO recordset object
Set objRS = Server.CreateObject("ADODB.Recordset")
objRS.ActiveConnection = adoCon
objRS.CursorLocation = 2
objRS.CursorType = 1
objRS.LockType = 3
objRS.Open strSQL

Set objFS = Server.CreateObject("Scripting.FileSystemObject")
Set objFile = objFS.CreateTextFile(strFileName, True)

' Get the Column Headers
For Each F In objRS.Fields
      strHead = strHead & ", " & F.Name
Next

strHead = Mid(strHead,3)
' Get the Data from the Recordset and store as Comma Separated Values
strBody = objRS.GetString(,,", ",vbCrLf,"")
objFile.WriteLine(strHead)
objFile.WriteLine(strBody)
objFile.Close

' Set you file name and file path
strFileName = "download.csv"
strFileNamePath = Server.MapPath("/downloads/download.csv")

' Set the contentype, this setting will make it prompt to download
Response.ContentType = "application/octet-stream"
Response.AddHeader "content-disposition", "attachment; filename="& strFileName

' Write the CSV file using ADO Stream
Set Stream = Server.CreateObject("ADODB.Stream")
Stream.Open
Stream.LoadFromFile strFileNamePath
Response.BinaryWrite Stream.ReadText
Stream.Close
Set Stream = Nothing



-DaRocker22
This will be MUCH faster than the one posted by DaRocker22 --
Speaking from experience, I have very recently done a sizeable research on this very topic, and found this approach at least 4-10 times faster than the other approaches of creating/exporting CSV/XLS files out of web-page-based database query results.

Use the vntTimeStart and vntTimeEnd  values to check out.
Ready Code; just copy-paste and run.

<%
'Save file as "ExportToExcel-CSV.ASP"

Response.Buffer = True
Response.Expires=0

'Here create your own SQL query in strDBQuery
strDBQuery = "Select * from MyTable where myField='" & strMyValue & "'"

vntTimeStart = Time
' Create a server recordset object
Set objDBConn= Server.CreateObject("ADODB.Connection")
Set objRs = Server.CreateObject("ADODB.Recordset")

objDBConn.CursorLocation = 2    'Server Side Cursor; this enhances speed BigTime! But no RecordCount :-(

objDBConn.Open SITE_DB_DSN, SITE_DB_LOGIN_ID, SITE_DB_LOGIN_PWD

' Execute the sql
objRs.Open strDBQuery, objDBConn

If Not (objRs.EOF and objRs.BOF) then
      ' Move to the first record
      objRs.MoveFirst
Else
      'Abort the operation and get out of the memory allocation
      Response.Write "<BR><Strong>The Report Returned NO records. Aborting Export Operation...</Strong><BR>"
      objRs.close
      set objRs=nothing
      Set objDBConn = nothing

      Response.Flush
      Response.End
End If

'***************************************************************************
'Start Writing to the Buffer
'***************************************************************************
' Tell the browser to open Microsoft Excel
Response.ContentType = "application/vnd.ms-excel"

Response.Write ("<html><title>Defect Metrics Report</title><body>")
Response.Write ("<table border=""1"">")

'------------------------------------------------------
'Create the formatted Table Heading on top of the Excel/CSV sheet
'------------------------------------------------------
Response.Write ("<tr>")
intFieldCounter=0

for i=0 to objRs.Fields.Count -1
      intFieldCounter = intFieldCounter + 1
      Response.Write ("<th nowrap>" & objRs.Fields(i).Name & "</th>")
Next

Response.Write ("</tr>")

'------------------------------------------------------
'Create the Table Body
'------------------------------------------------------
objRs.MoveFirst
Do while not objRs.eof
      intRecordCounter = intRecordCounter +1
      Response.Write ("<tr>")
      For i=0 to objRs.Fields.Count - 1
            Response.Write ("<td align=""left"">" & objRs.Fields(i).Value  & "</td>")
      Next
      Response.Write ("</tr>")
      objRs.movenext
Loop
vntTimeEnd = Time

If blnDebugMode then
      vntTimeDiff = cstr(vntTimeEnd - vntTimeStart)
      vntTimeDiff = left(vntTimeDiff, InStr(1,vntTimeDiff,".",1)+4)
      Response.Write ("<tr>")
      Response.Write ("<td align=""left"">" & intRecordCounter  & " Rows and " & intFieldCounter & " Fields transferred from ADO resultset to formatted Excel File in " & vntTimeDiff & " Seconds</td>")
      Response.Write ("</tr>")
End If

Response.Write ("</table></body></html>")

' Close and set the recordset to nothing
objRs.close
set objRs=nothing
Set objDBConn = nothing

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial