|
| |
|
|
DesiMcK
Posts: 447
Joined: 4/26/2004
From: Essex, UK
Status: offline
 |
Exporting to Excel - 4/29/2004 18:06:35
Here is yet another silly question form me. I found this post on the site rgarding exporting to excel.
quote:
I stumbled across this at a different message board. Something to remember here is this will be screwed up if any of your fields contain a comma. This should be a good starting point.
<%
Response.Addheader "Content-Disposition", "inline; filename=filename.csv"
Response.ContentType = "application/download"
conn.Open connectString
Set rs = conn.Execute("your_sq_lhere")
output = rs.GetString(2,,",")
rs.Close
Response.Write output
%>
Name your export file with a .csv (comma separated values) extension instead of .xls or the spreadsheet will open on the browser.
[This message has been edited by jbennett (edited 09-16-2001).]
What do I do to implement it? Do I need to attach it to a button in order to activate it?
Desi
|
|
|
|
rdouglass
Posts: 9280
From: Biddeford, ME USA
Status: offline
|
RE: Exporting to Excel - 4/30/2004 8:08:08
Actually this script can be on any ASP page. If it's the only thing on the page, a download dialogue box should open for the browser. Of course, that's assuming you have a valid connection and SQL query. Myself, I prefer this script 'cause it gives you column headings:
http://www.pstruh.cz/tips/detpg_RSConvert.htm
_____________________________
Don't take you're eye off your final destination.
ASP Checkbox Function Tutorial.
| |
|
|
|
DesiMcK
Posts: 447
Joined: 4/26/2004
From: Essex, UK
Status: offline
|
RE: Exporting to Excel - 4/30/2004 11:26:20
OK, I think I understand the script but I am not sure how to implement it. Would you be able to advise me in very simple steps. Thanks for your time,
Desi
| |
|
|
|
rdouglass
Posts: 9280
From: Biddeford, ME USA
Status: offline
|
RE: Exporting to Excel - 4/30/2004 12:03:19
1. Create a new page and go to HTML view.
2. Delete ALL code, even the <html> tags.
3. Paste this code on the page. This should be the ONLY code on the page:
<%
Dim DSN, RS, SQL, Conn
'This next line is your database connection line
DSN="DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & server.mappath("/fpdb/db1.mdb")
'This next line is your SQL for the data you want to use
SQL = "SELECT * FROM Item"
set Conn=server.createobject("adodb.connection")
Conn.open DSN
Set RS = Conn.Execute (SQL)
Dim F, Head
For Each F In RS.Fields
Head = Head & ", " & F.Name
Next
Head = Mid(Head,3) & vbCrLf
Response.Addheader "Content-Disposition", "inline; filename=myExportName.csv"
Response.ContentType = "application/download"
Response.Write Head
Response.Write RS.GetString(,,", ",vbCrLf,"")
Set RS = nothing
Conn.close
%>
4. Edit the applicable areas that are in bold. They will be specific to your web site, but most Windows servers should be able to accomidate the DSN. Try just changing the db filename and the SQL query to see if you have any luck.
5. Save the page as an ASP page.
6. Put a link (to this page) on any other page in your site.
7. Click link in browser to download CSV file.
Does that help?
_____________________________
Don't take you're eye off your final destination.
ASP Checkbox Function Tutorial.
| |
|
|
|
DesiMcK
Posts: 447
Joined: 4/26/2004
From: Essex, UK
Status: offline
|
RE: Exporting to Excel - 4/30/2004 12:41:54
This is much more than I expected. Thank you very, very much for this. I will try it later and let you know of my success. Thanks again.
Desi
| |
|
|
|
DesiMcK
Posts: 447
Joined: 4/26/2004
From: Essex, UK
Status: offline
|
RE: Exporting to Excel - 4/30/2004 17:28:14
The instructions were excellent. Even I managed to get the export to work. Now for an ammendment... I have a custom query that sends the results to an asp page. Can I then forward the query to the export asp. For example, when a teacher select Class from a drop down box, the class list appears on a seperate page. Can I put a link on that page that will open the export asp and download the class list. I changed the code to
SQL = "SELECT * FROM Results WHERE (Class = '::Class::')"
but it doesn't work. Is there a way I can achieve what I want?
Desi
| |
|
|
|
DesiMcK
Posts: 447
Joined: 4/26/2004
From: Essex, UK
Status: offline
|
RE: Exporting to Excel - 5/1/2004 11:05:08
Thanks brilliant Spooky, thank you. Unforntunately I do not understand some parts of it. Would you be able to explain this part - ..."),"'","''")&"')"
Ultimately I would like to add to the query string so that several search criteria will be passed to the csv file with a choosen oredr. I have attached one of the more complex SQL statements that I would like to adapt.
SELECT * FROM Results WHERE (Class = '::Class::' AND Gender = '::Gender::' AND IEP = "Yes") ORDER BY ::sortcol:: ::sortorder::,::sortcol2:: ::sortorder2::,::sortcol3:: ::sortorder3::"
Thanks agin for you help,
Desi
| |
|
|
|
frontpagenick
Posts: 1
Joined: 5/2/2004
Status: offline
|
RE: Exporting to Excel - 5/3/2004 3:57:00
Hi
I have developed an ASP programme using front page and if you add a search feature then when you rt hand click to export to Excel no records are returned.
http://www.hunterchasers.co.uk/database/horseinquiry.asp
Has anyone else had this issue and come up with an answer?
Nick
| |
|
|
|
Jessop
Posts: 6
Joined: 7/12/2004
Status: offline
|
RE: Exporting to Excel - 7/12/2004 10:39:21
I am using the following code:
<%
Dim RS
dim DataConn
dim DataCmd
dim datapath
dim SQL
dim rsEMSC
%>
<!--#include File="../Includes/DBOpenClose2.asp"-->
<%
SQL = "SELECT * FROM AuthorizedUsers"
DataCmd.CommandText = SQL
Set RS = server.CreateObject("ADODB.Recordset")
RS.Open DataCmd, , 1, 3
Dim F, Head
For Each F In RS.Fields
Head = Head & ", " & F.Name
Next
Head = Mid(Head,3) & vbCrLf
Response.Addheader "Content-Disposition", "inline; filename=myExportName.csv"
Response.ContentType = "application/download"
Response.Write Head
Response.Write RS.GetString(,,", ",vbCrLf,"")
%>
and when the page completes loading i would expect ONE LONG CSV string to include all the field names and all the record names in the long string. SO I copy and paste the data into a CSV file. But when excell tries to load the csv file it tell me that its going to truncate the string becuase its too long. which i understand because there is a ton of data in the access database. What I don't understand is I am using the vbcrlf and I expect it to do a new line in excel but excel isn't recognizing it. any ideas / suggestions? I had a thought where the browser is stripping out the vbcrlf's and its not when i view source it looks like i would expect but in the main broswer its showing one long string. Thanks in advance and have a great day.
| |
|
|
|
JohnH
Posts: 43
Joined: 12/1/2004
Status: offline
|
RE: Exporting to Excel - 7/7/2005 4:24:02
I am using the code rdouglass recommends above and it works great. The only problem I am having is when I export dates. I am in the UK and have the age old problem of displaying dates in the dd/mm/yyyy format and not the mm/dd/yyyy format. Does anyone have any ideas on how I can achieve this within the code?
My existing code is:
"CREATION_DATE AS 'CREATION DATE', " & _
Many thanks,
John
< Message edited by JohnH -- 7/7/2005 4:56:40 >
| |
|
|
|
rubyaim
Posts: 757
Joined: 6/22/2005
Status: offline
|
RE: Exporting to Excel - 7/7/2005 19:06:28
Hi John, at the top of my export pages I've found that using the LCID actually works well.
At the top of the page I have:
Session.lcid = 3081
Am in Australia, I think for the UK it is '2057'.
This may not be the best way to handle it, but as it's actually working I've not been tempted to play around with it at all.
Sally
_____________________________
Sally
| |
|
|
|
JohnH
Posts: 43
Joined: 12/1/2004
Status: offline
|
RE: Exporting to Excel - 7/28/2005 6:49:41
Thanks for responding Sally. I have tried using the Session.lcid = 2057 at the top of my page but it did nothing. Any other ideas?
Thanks John
| |
|
|
|
JohnH
Posts: 43
Joined: 12/1/2004
Status: offline
|
RE: Exporting to Excel - 7/29/2005 9:52:17
Can anyone else help, my code is below
<%
Session.lcid = 2057
%>
<%
Dim DSN, RS, SQL, Conn
SQL = "SELECT A.STAFFNO AS 'Staff number', +ISNULL(FIRSTNAME,'')+' '+ISNULL(SURNAME,'') AS 'Full Name', IDNUMBER AS 'Employee ID', B.LOCATION AS 'Location', EMPLOYMENT AS 'Employer Name',INDUCTION AS 'Plant Induction', DONOTUSE AS 'Banned From Site',DLEXPIRY AS 'Driving License Expiry' FROM MASTER A, IMERYSCOMPETENCE B WHERE A.STAFFNO = B.STAFFNO AND A.STAFFNO LIKE '%"&Replace(Request.Querystring("staff_number"),"'","''")&"%' ORDER BY SURNAME"
set Conn = server.createobject("ADODB.Connection")
Conn.Open "PROVIDER=SQLOLEDB;" & _
"DATA SOURCE=server;DATABASE=COMPLIANCE;" & _
"USER ID=sa;PASSWORD=password;"
Set RS = Conn.Execute (SQL)
Dim F, Head
For Each F In RS.Fields
Head = Head & ", " & F.Name
Next
Head = Mid(Head,3) & vbCrLf
Response.Addheader "Content-Disposition", "inline; filename=Export.csv"
Response.ContentType = "application/download"
Response.Write Head
Response.Write RS.GetString(,,", ",vbCrLf,"")
Set RS = nothing
Conn.close
%>
| |
|
|
|
sentinel
Posts: 570
Joined: 5/4/2005
From: Chicago, Illinois
Status: offline
|
RE: Exporting to Excel - 8/10/2005 17:21:11
I get an SYLK : Format Invalid Error.
The first column in my database is "ID". I tried to make it lowercase but it had no effect.
| |
|
|
|
davith2
Posts: 2
Joined: 8/16/2005
Status: offline
|
RE: Exporting to Excel - 8/16/2005 10:49:53
Does anyone know how I can use a button to export results from a SQL Database to Excel. I have a Form that I created that pulls information from the database and and displays it on a webpage, I want to have a button on that same results page that will (with the results still showing on the page), export the results that I have already called to Excel. I have tried using
Response.ContentType = "appliaction/vnd.ms-excel"
Response.AddHeader "Content-Disposition","attachment; filename=results.xls"
%>
and it works great, but the problem is it automatically exports to Excel and then there is no longer any results on the webpage. I would like to see the results and then have the choice if I want to export the same results to Excel with a push botton or link.
| |
|
|
|
rdouglass
Posts: 9280
From: Biddeford, ME USA
Status: offline
|
RE: Exporting to Excel - 8/17/2005 13:13:48
quote:
I would like to see the results and then have the choice if I want to export the same results to Excel
Hi and Welcome to OutFront.
I have used a small form with just a hidden field and a button called "Export". The hidden field contains the same query that the page you're viewing used to generate the table. I call the hidden field "exportQuery" and POST the form to the ASP page that holds the Excel export code like above.
Then, in the export code, instead of this:
SQL = "SELECT * FROM AuthorizedUsers"
use something like this:
SQL = Request.form("exportQuery")
What you're using on the page that displays results generally will dictate how you get that query in the form. Myself, I generally use mySQL for a variable so my form field would look like:
<input type="hidden" name="exportQuery" value="<%=mySQL%>">
See what I'm doing? I'm just passing the exact same SQL to the export scriipt using a hidden field. That works fine unless you get some huge SQL statement but I think the actual size of the string is quite large - 10K characters or so IIRC.
That help any?
_____________________________
Don't take you're eye off your final destination.
ASP Checkbox Function Tutorial.
| |
|
|
|
rdouglass
Posts: 9280
From: Biddeford, ME USA
Status: offline
|
RE: Exporting to Excel - 8/17/2005 13:20:52
quote:
I get an SYLK : Format Invalid Error.
One quick thing I'd try is changing this:
"inline; filename=Export.csv"
to this:
"inline; filename=Export.xls"
You may get funny results but I suspect you're getting the error from some text fields that have either special characters or quotes in them. See, the CSV files we're generating are using double quotes as a text delimiter and a SYLK file interprets it the same IIRC. If you have quotes in the data itself, that'll usually cause errors like that.
A quick way to check would be to do a simple replace script at least to rule that out:
Response.Write RS.GetString(,,", ",vbCrLf,"")
becomes:
Response.Write (replace(RS.GetString(,,", ",vbCrLf,""),CHR(034),""))
That'll just remove all double quotes from the data.
That any help?
_____________________________
Don't take you're eye off your final destination.
ASP Checkbox Function Tutorial.
| |
|
|
|
sentinel
Posts: 570
Joined: 5/4/2005
From: Chicago, Illinois
Status: offline
|
RE: Exporting to Excel - 8/22/2005 10:54:16
Using the export query in a hidden field. WHat is the best way to put the SQL statement into a variable and call it from the hidden field?
<%
varMysql="SELECT * FROM asset where physical_location = 'mount prospect'"
%>
<%=Mysql%>
Something like that?
| |
|
|
|
ChitownPro
Posts: 4
Joined: 9/8/2003
From: Chicago IL
Status: offline
|
RE: Exporting to Excel - 1/12/2010 15:34:21
Realizing that I'm jumping into this four and a half years after the last post...
Apparently some of my data fields in the table have embedded comma's - is there a way to enclose every field in double qoutes to handle this situation?
CP
_____________________________
"It's a hundred and six miles to Chicago. We got a full tank of gas, half a pack of cigarettes, it's dark, and we're wearing sunglasses."
| |
|
 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 
ky 