|
| |
|
|
fhenkel
Posts: 183
Joined: 1/7/2004
Status: offline
 |
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
Posts: 9206
From: Biddeford, ME USA
Status: offline
|
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
%>
_____________________________
Don't take you're eye off your final destination.
ASP Checkbox Function Tutorial.
| |
|
|
|
fhenkel
Posts: 183
Joined: 1/7/2004
Status: offline
|
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
Posts: 183
Joined: 1/7/2004
Status: offline
|
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
Posts: 9206
From: Biddeford, ME USA
Status: offline
|
RE: Help on exporting Access data to a Excel sheet. - 7/27/2005 13:31:17
Which script did you use, mine or yours?
_____________________________
Don't take you're eye off your final destination.
ASP Checkbox Function Tutorial.
| |
|
|
|
fhenkel
Posts: 183
Joined: 1/7/2004
Status: offline
|
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
Posts: 9206
From: Biddeford, ME USA
Status: offline
|
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.
_____________________________
Don't take you're eye off your final destination.
ASP Checkbox Function Tutorial.
| |
|
|
|
fhenkel
Posts: 183
Joined: 1/7/2004
Status: offline
|
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
Posts: 9206
From: Biddeford, ME USA
Status: offline
|
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.
_____________________________
Don't take you're eye off your final destination.
ASP Checkbox Function Tutorial.
| |
|
|
|
fhenkel
Posts: 183
Joined: 1/7/2004
Status: offline
|
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
Posts: 9206
From: Biddeford, ME USA
Status: offline
|
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
%>
_____________________________
Don't take you're eye off your final destination.
ASP Checkbox Function Tutorial.
| |
|
|
|
fhenkel
Posts: 183
Joined: 1/7/2004
Status: offline
|
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
Posts: 183
Joined: 1/7/2004
Status: offline
|
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
Posts: 183
Joined: 1/7/2004
Status: offline
|
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
Posts: 9206
From: Biddeford, ME USA
Status: offline
|
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.
_____________________________
Don't take you're eye off your final destination.
ASP Checkbox Function Tutorial.
| |
|
|
|
fhenkel
Posts: 183
Joined: 1/7/2004
Status: offline
|
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
Posts: 9206
From: Biddeford, ME USA
Status: offline
|
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?
_____________________________
Don't take you're eye off your final destination.
ASP Checkbox Function Tutorial.
| |
|
|
|
OscarSierra
Posts: 179
Joined: 5/14/2004
From: Norway
Status: offline
|
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
Posts: 183
Joined: 1/7/2004
Status: offline
|
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
Posts: 1
Joined: 8/19/2005
From: Portland, ME
Status: offline
|
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
| |
|
 New Messages |
 No New Messages |
 Hot Topic w/ New Messages |
 Hot Topic w/o New Messages |
 Locked w/ New Messages |
 Locked w/o New Messages |
|
Post New Thread
Reply to Message
Post New Poll
Submit Vote
Delete My Own Post
Delete My Own Thread
Rate Posts
|
|
|
Printable Version 
