asked on

output a table to a csv file

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""><a href=""http://dxclvl2.mcilink.com/database/users/LOGIN.ASP"">Click here to return to the login page</a> ")
 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">Entry date</td>
<td width="6%">Notify By</td>
<td width="6%">Support To:</td>
<td width="6%">Vendor:</td>
<td width="6%">Product Type:</td>
<td width="6%">DXC:</td>
<td width="6">Action:</td>
<td width="5%">Hours:</td>
<td width="5%">Minutes:</td>
<td width="6%">Duty Shift:</td>
 <td width="6%">Project ame:</td>
<td width="6%">Element Name</td>
 <td width="8">Tik #</td>
 <td width="20%">comments</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
%>

rosesa

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.

vijay7248

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

DaRocker22

'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

ASKER CERTIFIED SOLUTION

unifavtech74

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial