Help on exporting Access data to a Excel sheet. (Full Version)

All Forums >> [Web Development] >> ASP and Database



Message

fhenkel -> Help on exporting Access data to a Excel sheet. (7/27/2005 11:52:53)

I have searched the forums and found several ideas but can't get any to work. I have a Access database with a number of Tables. What my boss has requested is can we have a link on our FP extensions 2002 site which he can click and have a certain table of data piped out to a Excel spreadsheet. I found the code below but it gives a ODBC error when run.

****************************************************

<%
dim accessdb, cn, rs, sql
' Name of the Accessdb being read
accessdb="fss_tracking.mdb"

' Connect to the db with a DSN-less connection
cn="DRIVER={Microsoft Access Driver (*.mdb)};"
cn=cn & "DBQ=" & server.mappath(accessdb)

' Create a server recordset object
Set rs = Server.CreateObject("ADODB.Recordset")

' Select all data from the table results
sql = "select * results "

' Execute the sql
rs.Open sql, cn
%>

<%
' Tells the browser to open excel
Response.ContentType = "application/vnd.ms-excel" %>
<html>
<body>
<table BORDER="1" align="center">
<tr>
<td>Activity_Type</td>
<td>Activity</td>
<td>Company</td>
<td>Name1</td>
<td>Ticket_Number</td>
<td>Description</td>
<td>Time_Spent</td>
<td>Weekend</td>
<td>Timestamp</td>
</tr>

<%
' Move to the first record
rs.movefirst

' Start a loop that will end with the last record
do while not rs.eof
%>

<tr>
<td>
<%= rs("activity_type") %>
</td>

<td>
<%= rs("activity") %>
</td>

<td>
<%= rs("company") %>
</td>

<td>
<%= rs("name1") %>
</td>

<td>
<%= rs("ticket_number") %>
</td>

<td>
<%= rs("description") %>
</td>

<td>
<%= rs("time_spent") %>
</td>

<td>
<%= rs("weekend") %>
</td>

<td>
<%= rs("timestamp") %>
</td>
</tr>

<%
' Move to the next record
rs.movenext
' Loop back to the do statement
loop %>
</table>

</body>
</html>

<%
' Close and set the recordset to nothing
rs.close
set rs=nothing
%>

************************************

I have checked the server and their are no connections in either System or User DSN sections. The error I'm getting is:

Microsoft OLE DB Provider for ODBC Drivers error '80004005'

[Microsoft][ODBC Microsoft Access Driver]General error Unable to open registry key 'Temporary (volatile) Jet DSN for process 0x16ac Thread 0x1700 DBC 0x1d47024 Jet'.

/FSS_Tracking/Results/test_page.asp, line 17


Any help would be appreciated.



rdouglass -> RE: Help on exporting Access data to a Excel sheet. (7/27/2005 12:31:51)

quote:

accessdb="fss_tracking.mdb"


Is that DB in the fpdb folder? If so, try this:

accessdb="/fpdb/fss_tracking.mdb"

If not, here is the code I use:

<%

Server.ScriptTimeout = 300
Dim rstemp, conntemp, exportQuery, myDSN
myDSN = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.Mappath("/fpdb/myDatabase.mdb")
exportFields = "PswdName,UserName,Pswd"
exportQuery = ("SELECT * FROM myTable")
set conntemp=server.createobject("adodb.connection")
conntemp.open myDSN
set rstemp=conntemp.execute(exportQuery)

Dim F, Head, Body
Head = "<table><tr><td>"
For Each F In rstemp.Fields
Head = Head & F.Name & "</td><td>"
Next
Head = Head & "</td></tr>" & vbCrLf

Body = "<tr><td>"
Body = Body & rstemp.GetString(,,"</td><td>","</td></tr><tr><td>","")
Body = left(Body,len(Body)-8) & "</table>"

conntemp.close
set conntemp=nothing

call Response.AddHeader("Content-Disposition", "attachment;filename=exportdata.xls")
Response.ContentType = "application/vnd.ms-excel"
Response.Write Head
Response.Write Body
%>



fhenkel -> RE: Help on exporting Access data to a Excel sheet. (7/27/2005 13:18:19)

From what you have answered that since the page resides under /fss_tracking/results the form should be ../../fpdb/fss_tracking.mdb? I'll try it you rway and if not then I'll add the extended levels. Thanks for the quick response.



fhenkel -> RE: Help on exporting Access data to a Excel sheet. (7/27/2005 13:29:59)

OK, we're almost home! It works fine up until it should open Excel and import the data but it only display it in a browser window, not Excel??
Any ideas on that one?



rdouglass -> RE: Help on exporting Access data to a Excel sheet. (7/27/2005 13:31:17)

Which script did you use, mine or yours?



fhenkel -> RE: Help on exporting Access data to a Excel sheet. (7/27/2005 14:17:01)

Sorry about that, mine. I can make a page up with yours in it if that would work as described..



rdouglass -> RE: Help on exporting Access data to a Excel sheet. (7/27/2005 14:30:08)

quote:

<%
' Tells the browser to open excel
Response.ContentType = "application/vnd.ms-excel" %>


Change this to this:

<% call Response.AddHeader("Content-Disposition", "attachment;filename=exportdata.xls")
Response.ContentType = "application/vnd.ms-excel"%>

and remove the <html> and <body> tags. That should be better.



fhenkel -> RE: Help on exporting Access data to a Excel sheet. (7/27/2005 15:21:27)

quote:

<% call Response.AddHeader("Content-Disposition", "attachment;filename=exportdata.xls")
Response.ContentType = "application/vnd.ms-excel"%>


This opens a download progress bar and puts the file somewhere on the drive, haven't found it yet...... Ok, that works as it did before, it just doesn't display the "Open or Save" box, which is good. Excel still never opens to display the data. I guess what I was expecting was for the data to be exported directly into Excel and Excel open with the spreadsheet.



rdouglass -> RE: Help on exporting Access data to a Excel sheet. (7/27/2005 15:55:46)

Why don't you give my code a whirl? Put it on it's own page and browse to it.

Also, if there is no data returned I don't believe Excel will open.



fhenkel -> RE: Help on exporting Access data to a Excel sheet. (7/27/2005 16:09:31)

Ok, yours works fine when you select the "Open" choice in the download window. Now I'm really getting picky but can we get yours to open in Excel with the lines showing? I have a ton of data and without the lines on the spreadsheet it really is hard to read.

The other code does put the lines in the .XLS.. Thanks again for all your help on this.



rdouglass -> RE: Help on exporting Access data to a Excel sheet. (7/27/2005 16:20:53)

quote:

can we get yours to open in Excel with the lines showing?


How 'bout this version? 

<% 

Server.ScriptTimeout = 300 
Dim rstemp, conntemp, exportQuery, myDSN 
myDSN = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.Mappath("/fpdb/myDatabase.mdb") 
exportFields = "PswdName,UserName,Pswd" 
exportQuery = ("SELECT * FROM myTable") 
set conntemp=server.createobject("adodb.connection") 
conntemp.open myDSN 
set rstemp=conntemp.execute(exportQuery) 

Dim F, Head, Body 
Head = ""
For Each F In rstemp.Fields
  Head = Head & ", " & trim(F.Name)
Next
Head = Mid(Head,3) & vbCrLf
call Response.AddHeader("Content-Disposition", "attachment;filename=exportdata.csv")
Response.ContentType = "application/download"
Response.Write Head
Response.Write rstemp.GetString(,,",",vbCrLf,"")
conntemp.close
set conntemp=nothing

%>



fhenkel -> RE: Help on exporting Access data to a Excel sheet. (7/27/2005 19:14:02)

This one comes up with an error prior to opening the Excel spreadsheet. If I click "Ok" it still opens it but one of the fields, Name1, has a comma after the persons last name so it puts the first name in a seperate column. Anyway to do what you have in the second script but do a XLS instead of a CSV? The first script worked perfect except for the lines not showing up.

Error: "Excel has detected that 'exportdata[1].csv is a SYLK file, but cannot load it. Either the file has errors or it is not a SYLK file format.
Click OK to try to open it in a different format. "



fhenkel -> RE: Help on exporting Access data to a Excel sheet. (7/27/2005 20:31:44)

Went back to your first script and where you have

Head = "<table><tr><td>" I added BORDER='1'

Head = "<table BORDER='1'><tr><td>" and now the cells are framed with the borders. Works great! Thanks so much for all your help today.

fhenkel




fhenkel -> RE: Help on exporting Access data to a Excel sheet. (7/28/2005 9:28:02)

Another question, the code is working great until I try to pull from a database we have that has over 19,000 records. It then only shows it is trying to download the .ASP page itself and bombs out? Is there a limit as to the amount of data it can pull and export to Excel? Each record icontains 17 fields in it.



rdouglass -> RE: Help on exporting Access data to a Excel sheet. (7/28/2005 9:51:21)

quote:

Server.ScriptTimeout = 300


Try increasing that value. 300 sets it to 5 minutes. Try something like:

Server.ScriptTimeout = 1200

That would be 20 minutes but don't do this if it is a busy production server - you may notice performance issues. This is doing a lot of work and 19K records is no trivial task for this script. It may be too much - I've never tried with that many before.

EDIT: Either that or craft your SQL to reduce the number of records and send it in chunks.



fhenkel -> RE: Help on exporting Access data to a Excel sheet. (7/28/2005 11:14:58)

Well the chunks work best but it takes forever pulling all that data. Is there any way to specify what fields to get? That would be another way of resolving that issue since we don't really need all the data.

I'd like to build your script such that we could pull only certain data fields for sending the spreadsheet to management if needed.



rdouglass -> RE: Help on exporting Access data to a Excel sheet. (7/28/2005 11:58:11)

quote:

Is there any way to specify what fields to get?


It's all done in the SQL statement. Limit your SQL statement to only the fields you want and not "SELECT * FROM..."

That any help?



OscarSierra -> RE: Help on exporting Access data to a Excel sheet. (7/30/2005 7:36:23)

I use this, don't work with MySQL and require Excel installed on the server:


/////////////////

the include file dataconn.asp here

<%
Set Conn = Server.CreateObject("Adodb.Connection")

' DSNless connection to Database or whatever you have ....
Dim DSNtemp
DSNtemp="Provider=MSDASQL;"
DSNtemp=DSNtemp & "DRIVER={Microsoft Access Driver (*.mdb)}; "
DSNtemp=DSNtemp & "DBQ=C:\Documents and Settings\Administrator\My documants\table_test.mdb;"
'DSNtemp=DSNtemp & "DBQ=" & Server.MapPath(".\table_test.mdb") & ";"

Conn.open DSNtemp
%>




//////////////////////////

the excel.asp goes here

<%@ Language="VbScript"%>
<!--#include file="dataconn.asp"-->
<%
Dim xlWorkSheet ' Excel Worksheet object
Dim xlApplication
Dim uniqueName

uniqueName = replace(Now()," ","")
uniqueName = replace(uniqueName,"/","")
uniqueName = replace(uniqueName,":","")

myfilename = uniqueName

Set xlApplication = CreateObject("Excel.Application")
xlApplication.Visible = False
xlApplication.Workbooks.Add
Set xlWorksheet = xlApplication.Worksheets(1)

'---------------------------------------------------------------------------
' The SQL below selects all the records from the Table table_1
'---------------------------------------------------------------------------
msql = "SELECT * FROM table_1"
Set RS=Conn.Execute(msql)

'---------------------------------------------------------------------------
' The The following code generates the header of the Excell spreadsheet
'---------------------------------------------------------------------------
xlWorksheet.Cells(1,1).Value = "rec-id"
xlWorksheet.Cells(1,1).Interior.ColorIndex = 24
xlWorksheet.Cells(1,2).Value = "customer"
xlWorksheet.Cells(1,2).Interior.ColorIndex = 24
xlWorksheet.Cells(1,3).Value = "name"
xlWorksheet.Cells(1,3).Interior.ColorIndex = 24
xlWorksheet.Cells(1,4).Value = "address"
xlWorksheet.Cells(1,4).Interior.ColorIndex = 24
xlWorksheet.Cells(1,5).Value = "zip"
xlWorksheet.Cells(1,5).Interior.ColorIndex = 24
xlWorksheet.Cells(1,6).Value = "insurance"
xlWorksheet.Cells(1,6).Interior.ColorIndex = 24

iRow = 2
If Not RS.EOF Then

Do Until RS.EOF
For i = 0 To RS.fields.Count-1
xlWorksheet.Cells(iRow,i + 1).Value = RS.fields(i)
xlWorkSheet.Cells(iRow,i + 1).Interior.ColorIndex = 2
Next
iRow = iRow + 1
RS.MoveNext
Loop

End If
strFile = myfilename
xlWorksheet.SaveAs Server.MapPath(".") & "\" & strFile & ".xls"
xlApplication.Quit ' Close the Workbook

Set xlWorksheet = Nothing
Set xlApplication = Nothing

RS.Close
Conn.Close

Set RS = Nothing
Set Conn = Nothing

Response.Write("Click <A HRef=" & strFile & ".xls>Here</A> to get XLS file")
%>




fhenkel -> RE: Help on exporting Access data to a Excel sheet. (8/2/2005 13:21:02)

rdouglas, sorry for the delay replying but was out for a couple of days. I've got it working great now. Modofied the SQL statement and have created several search forms now.. Thanks again for all your help.




smichaud -> RE: Help on exporting Access data to a Excel sheet. (8/22/2005 19:30:12)

I came across this thread when searching for efficient ways to export to .csv files and I thought I'd post my findings and ask for advice while I'm here.

I ran into 4 major problems.
1. Excel gives that SLYK error when the first field contains "ID" so I filtered that to "_ID"
2. When storing data in Access memo fields, if the user entered a carriage return then the GetString command skips it and appends it to the end of the row. Because of that, I filtered out the vbNewLine character (this is the only one that seemed to have caught it).
3. Commas in the database mess up the csv (since it's separated by commas). I added quotations to each field to solve this.
4. Quotations in the database messed with my quotations. Originally I looped through the whole table and checked for Quotes, Commas and vbNewLine's but holy cow was it slow compared to the GetString when pulling down hundreds of records. Finally it dawned on me to run the GetString and put in the \# to be replaced later. Much faster.

I would have liked a way to only put quotations around fields that contain commas though so I could save myself potentially thousands of characters on the downloads. Anyone have thoughts on how to go about doing that and maintaining the speed of the GetString function?

Below is the code I used:

Response.Clear

head = ""
for each x in rs.fields
if x.name = "ID" then
tName = "_ID"
else
tName = trim(x.name)
end if
head = head & ", " & tName
next
head = mid(head,3) & vbCrLf

call Response.AddHeader("Content-Disposition", "attachment;filename="&FileName&".csv")
Response.ContentType = "application/download"

Response.Write Head & chr(34)
sOut = replace(replace(RS.GetString(2,-1,"\#,\#","\#"&vbCr&"\#","") ,vbNewLine," "),chr(34),"'")
Response.Write replace(sOut,"\#",chr(34))

Response.End



Page: [1]

Valid CSS!




Forum Software © ASPPlayground.NET Advanced Edition 2.4.5 ANSI
0.125