Ask a Question related to Dreamweaver AppDev, Design and Development.
-
samkry #1
extract record from database to notepad or excel
Hi..
Have anyone can help me how to extract data from my db to notepad or excel..? is there any tools in Dw to do this?
samkry Guest
-
using excel as database
:confused;I have to use excel as one of the database in order to update a table in oracle based on the data in excel. Pls is there anyone to help... -
How to extract multilingual to MS Excel
How can I extract multilingual data into MS Excel application. At the moment the data extracted into Excel file is not readable. Currently using... -
Extract Data from Excel-files
Hello everyone, Finally I get to use perl at work! =) I am to facing the following problem: There is a folder on a file-server in our network... -
Fixed Length Text Extract, Write to Excel
Hello All, I am trying to work with the code I have to extract fields from a text file report, and write the values into excel. I am having... -
Is PHP able to extract data out of an Excel spreadsheet?
The only form of database we use is an Excel database. Otherwise is it possible to import the relevant spreadsheet data into say MySQL? Perhaps it... -
CarlGrint #2 Re: extract record from database to notepad or excel
To generate an Excel page from an ASP page, simply add the following 2 lines at
the end of your ASP coding, make sure it is the last code befor eyour HTML tag.
<%
Response.ContentType = "application/vnd.ms-excel"
Response.AddHeader "Content-Disposition","attachment;filename.xls"
%>
Originally posted by: samkry
Hi..
Have anyone can help me how to extract data from my db to notepad or excel..?
is there any tools in Dw to do this?
CarlGrint Guest
-
CMBergin #3 Re: extract record from database to notepad or excel
I'm pretty sure it should be the first thing in the page, before any
response.write or HTML output.
"CarlGrint" <webforumsuser@macromedia.com> wrote in message
news:cuv4er$67u$1@forums.macromedia.com...lines at> To generate an Excel page from an ASP page, simply add the following 2tag.> the end of your ASP coding, make sure it is the last code befor eyour HTMLexcel..?>
> <%
> Response.ContentType = "application/vnd.ms-excel"
> Response.AddHeader "Content-Disposition","attachment;filename.xls"
> %>
>
> Originally posted by: samkry
> Hi..
> Have anyone can help me how to extract data from my db to notepad or> is there any tools in Dw to do this?
>
>
>
CMBergin Guest
-
samkry #4 Re: extract record from database to notepad or excel
Hi.
i'm not sure how to put the scripts on my pages..
let say if i just created new pages with only recordset or command
storedprocedure that have sql statment , how can I call it to extract the data
to excel?
can i just create a link like "extract data to excel?" so whenever user click
at this word DW will create an excel file saved to my c drive??
samkry Guest
-
CMBergin #5 Re: extract record from database to notepad or excel
No. What you can do is create an HTML page with an appropriate header so
that Excel 97 or later will open the file and interpret your HTML table as
an Excel spreadsheet. You'll have to save the file yourself through the
File->Save menu choice, or by setting the content-disposition to attachment
(as in Carl's example) so that IE will prompt to save or open the file
instead of just opening it.
It's easy - add those two lines to the top of your page.
"samkry" <webforumsuser@macromedia.com> wrote in message
news:cuvpal$96u$1@forums.macromedia.com...data> Hi.
> i'm not sure how to put the scripts on my pages..
> let say if i just created new pages with only recordset or command
> storedprocedure that have sql statment , how can I call it to extract theclick> to excel?
> can i just create a link like "extract data to excel?" so whenever user> at this word DW will create an excel file saved to my c drive??
>
CMBergin Guest
-
samkry #6 Re: extract record from database to notepad or excel
Guys..
is this correct, sorry I'm still blur, so now how could i create a record set
to call a table column in my db then extract out the data to excel sheet?
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<title>Untitled Document</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<%
Response.ContentType = "application/vnd.ms-excel"
Response.AddHeader "Content-Disposition","attachment;filename.xls"
%>
</head>
<body>
<table width="94%">
<tr>
<td>Report For Database</td>
<td> </td>
</tr>
<tr>
<td> </td>
<td> </td>
</tr>
</table>
<table width="94%">
<tr>
<td> </td>
<td> </td>
<td> </td>
</tr>
<tr>
<td> </td>
<td> </td>
<td> </td>
</tr>
<tr>
<td> </td>
<td> </td>
<td> </td>
</tr>
<tr>
<td> </td>
<td> </td>
<td> </td>
</tr>
<tr>
<td> </td>
<td> </td>
<td> </td>
</tr>
</table>
<p> </p>
</body>
</html>
samkry Guest
-
CMBergin #7 Re: extract record from database to notepad or excel
Create the recordset, put the data in that table, and use a repeat region.
These are all built-in behaviors.
The rest of the page is already set up properly. For completeness, you may
want to change the meta tag Content-Type as well so that
content="vnd.ms-excel" instead of "text/html; charset=iso-8859-1"
"samkry" <webforumsuser@macromedia.com> wrote in message
news:cvffl6$55k$1@forums.macromedia.com...set> Guys..
> is this correct, sorry I'm still blur, so now how could i create a record> to call a table column in my db then extract out the data to excel sheet?
>
> <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
> <html>
> <head>
> <title>Untitled Document</title>
> <meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
> <%
> Response.ContentType = "application/vnd.ms-excel"
> Response.AddHeader "Content-Disposition","attachment;filename.xls"
> %>
> </head>
>
> <body>
> <table width="94%">
> <tr>
> <td>Report For Database</td>
> <td> </td>
> </tr>
> <tr>
> <td> </td>
> <td> </td>
> </tr>
> </table>
> <table width="94%">
> <tr>
> <td> </td>
> <td> </td>
> <td> </td>
> </tr>
> <tr>
> <td> </td>
> <td> </td>
> <td> </td>
> </tr>
> <tr>
> <td> </td>
> <td> </td>
> <td> </td>
> </tr>
> <tr>
> <td> </td>
> <td> </td>
> <td> </td>
> </tr>
> <tr>
> <td> </td>
> <td> </td>
> <td> </td>
> </tr>
> </table>
> <p> </p>
> </body>
> </html>
>
>
CMBergin Guest
-
samkry #8 Re: extract record from database to notepad or excel
Hi..
I manage to view all the values needed by creating the recordset, when I click
to create a report to excel, IE just display all the data without asking me to
save them to excel..
below is my scripts..
<%@LANGUAGE="VBSCRIPT" CODEPAGE="1252"%>
<!--#include file="Connections/FFAS.asp" -->
<%
Dim RecordsetRpt__MMColParam
RecordsetRpt__MMColParam = "1"
If (Request.QueryString("VarDel") <> "") Then
RecordsetRpt__MMColParam = Request.QueryString("VarDel")
End If
%>
<%
Dim RecordsetRpt
Dim RecordsetRpt_numRows
Set RecordsetRpt = Server.CreateObject("ADODB.Recordset")
RecordsetRpt.ActiveConnection = MM_FFAS_STRING
RecordsetRpt.Source = "SELECT m.msn,m.dpyprodnum,imei.systemserialnumber as
imei,ieee.systemserialnumber as ieee, spck.simlockcode as spck
,nsck.simlockcode as nsck,nck.simlockcode as
nck,m.palletnumber,m.deliverynumber FROM individual m inner join esn imei
on m.msn=imei.msn inner join esn ieee on m.msn=ieee.msn inner join
simlockcode spck on m.msn=spck.msn inner join simlockcode nsck on
m.msn=nsck.msn inner join simlockcode nck on m.msn=nck.msn WHERE
imei.systemserialname='imei3' and ieee.systemserialname='ieee' and
spck.simcodename='spck' and nsck.simcodename='nsck' and nck.simcodename='nck'
and m.Deliverynumber= '" + Replace(RecordsetRpt__MMColParam, "'", "''") + "'
ORDER BY m.msn"
RecordsetRpt.CursorType = 0
RecordsetRpt.CursorLocation = 2
RecordsetRpt.LockType = 1
RecordsetRpt.Open()
RecordsetRpt_numRows = 0
%>
<%
Dim Repeat1__numRows
Dim Repeat1__index
Repeat1__numRows = -1
Repeat1__index = 0
RecordsetRpt_numRows = RecordsetRpt_numRows + Repeat1__numRows
%>
<html>
<head>
<title>Untitled Document</title>
<meta http-equiv="Content-Type" Response.ContentType =
"application/vnd.ms-excel"<response.AddHeader "Content-Disposition","attachment;filename.xls"></head>>
<body>
<table width="94%">
<tr>
<td> </td>
</tr>
<tr>
<td> </td>
</tr>
</table>
<p> </p>
<table border="1">
<tr>
<td>msn</td>
<td>dpyprodnum</td>
<td>imei</td>
<td>ieee</td>
<td>spck</td>
<td>nsck</td>
<td>nck</td>
<td>palletnumber</td>
<td>deliverynumber</td>
</tr>
<% While ((Repeat1__numRows <> 0) AND (NOT RecordsetRpt.EOF)) %>
<tr>
<td><%=(RecordsetRpt.Fields.Item("msn").Value)%> </td>
<td><%=(RecordsetRpt.Fields.Item("dpyprodnum").Val ue)%></td>
<td><%=(RecordsetRpt.Fields.Item("imei").Value)% ></td>
<td><%=(RecordsetRpt.Fields.Item("ieee").Value)% ></td>
<td><%=(RecordsetRpt.Fields.Item("spck").Value)% ></td>
<td><%=(RecordsetRpt.Fields.Item("nsck").Value)% ></td>
<td><%=(RecordsetRpt.Fields.Item("nck").Value)%> </td>
<td><%=(RecordsetRpt.Fields.Item("palletnumber").V alue)%></td>
<td><%=(RecordsetRpt.Fields.Item("deliverynumber") .Value)%></td>
</tr>
<%
Repeat1__index=Repeat1__index+1
Repeat1__numRows=Repeat1__numRows-1
RecordsetRpt.MoveNext()
Wend
%>
</table>
<p> </p>
</body>
</html>
<%
RecordsetRpt.Close()
Set RecordsetRpt = Nothing
%>
Text
samkry Guest
-
CMBergin #9 Re: extract record from database to notepad or excel
Because your response headers aren't in there. Why did you take them out?
You also need to learn the difference between server-side and client-side
code, and HTML and ASP.
But for now, your updated page:
<%@LANGUAGE="VBSCRIPT" CODEPAGE="1252"%>
<%
Response.ContentType = "application/vnd.ms-excel"
Response.AddHeader "content-disposition","attachment;
filename=desiredFilename.xls"
%>
<!--#include file="Connections/FFAS.asp" -->
<%
Dim RecordsetRpt__MMColParam
RecordsetRpt__MMColParam = "1"
If (Request.QueryString("VarDel") <> "") Then
RecordsetRpt__MMColParam = Request.QueryString("VarDel")
End If
%>
<%
Dim RecordsetRpt
Dim RecordsetRpt_numRows
Set RecordsetRpt = Server.CreateObject("ADODB.Recordset")
RecordsetRpt.ActiveConnection = MM_FFAS_STRING
RecordsetRpt.Source = "SELECT m.msn,m.dpyprodnum,imei.systemserialnumber as
imei,ieee.systemserialnumber as ieee, spck.simlockcode as spck
,nsck.simlockcode as nsck,nck.simlockcode as
nck,m.palletnumber,m.deliverynumber FROM individual m inner join esn imei
on m.msn=imei.msn inner join esn ieee on m.msn=ieee.msn inner join
simlockcode spck on m.msn=spck.msn inner join simlockcode nsck on
m.msn=nsck.msn inner join simlockcode nck on m.msn=nck.msn WHERE
imei.systemserialname='imei3' and ieee.systemserialname='ieee' and
spck.simcodename='spck' and nsck.simcodename='nsck' and
nck.simcodename='nck'
and m.Deliverynumber= '" + Replace(RecordsetRpt__MMColParam, "'", "''") +
"'
ORDER BY m.msn"
RecordsetRpt.CursorType = 0
RecordsetRpt.CursorLocation = 2
RecordsetRpt.LockType = 1
RecordsetRpt.Open()
RecordsetRpt_numRows = 0
%>
<%
Dim Repeat1__numRows
Dim Repeat1__index
Repeat1__numRows = -1
Repeat1__index = 0
RecordsetRpt_numRows = RecordsetRpt_numRows + Repeat1__numRows
%>
<html>
<head>
<title>Untitled Document</title>
<meta http-equiv="Content-Type" content="application/vnd.ms-excel">
</head>
<body>
<table border="1">
<tr>
<td>msn</td>
<td>dpyprodnum</td>
<td>imei</td>
<td>ieee</td>
<td>spck</td>
<td>nsck</td>
<td>nck</td>
<td>palletnumber</td>
<td>deliverynumber</td>
</tr>
<% While ((Repeat1__numRows <> 0) AND (NOT RecordsetRpt.EOF)) %>
<tr>
<td><%=(RecordsetRpt.Fields.Item("msn").Value)%> </td>
<td><%=(RecordsetRpt.Fields.Item("dpyprodnum").Val ue)%></td>
<td><%=(RecordsetRpt.Fields.Item("imei").Value)% ></td>
<td><%=(RecordsetRpt.Fields.Item("ieee").Value)% ></td>
<td><%=(RecordsetRpt.Fields.Item("spck").Value)% ></td>
<td><%=(RecordsetRpt.Fields.Item("nsck").Value)% ></td>
<td><%=(RecordsetRpt.Fields.Item("nck").Value)%> </td>
<td><%=(RecordsetRpt.Fields.Item("palletnumber").V alue)%></td>
<td><%=(RecordsetRpt.Fields.Item("deliverynumber") .Value)%></td>
</tr>
<%
Repeat1__index=Repeat1__index+1
Repeat1__numRows=Repeat1__numRows-1
RecordsetRpt.MoveNext()
Wend
%>
</table>
</body>
</html>
<%
RecordsetRpt.Close()
Set RecordsetRpt = Nothing
%>
Text
"samkry" <webforumsuser@macromedia.com> wrote in message
news:cvhimt$hh6$1@forums.macromedia.com...click> Hi..
> I manage to view all the values needed by creating the recordset, when Ime to> to create a report to excel, IE just display all the data without askingas> save them to excel..
> below is my scripts..
>
> <%@LANGUAGE="VBSCRIPT" CODEPAGE="1252"%>
> <!--#include file="Connections/FFAS.asp" -->
> <%
> Dim RecordsetRpt__MMColParam
> RecordsetRpt__MMColParam = "1"
> If (Request.QueryString("VarDel") <> "") Then
> RecordsetRpt__MMColParam = Request.QueryString("VarDel")
> End If
> %>
> <%
> Dim RecordsetRpt
> Dim RecordsetRpt_numRows
>
> Set RecordsetRpt = Server.CreateObject("ADODB.Recordset")
> RecordsetRpt.ActiveConnection = MM_FFAS_STRING
> RecordsetRpt.Source = "SELECT m.msn,m.dpyprodnum,imei.systemserialnumberimei> imei,ieee.systemserialnumber as ieee, spck.simlockcode as spck
> ,nsck.simlockcode as nsck,nck.simlockcode as
> nck,m.palletnumber,m.deliverynumber FROM individual m inner join esnnck.simcodename='nck'> on m.msn=imei.msn inner join esn ieee on m.msn=ieee.msn inner join
> simlockcode spck on m.msn=spck.msn inner join simlockcode nsck on
> m.msn=nsck.msn inner join simlockcode nck on m.msn=nck.msn WHERE
> imei.systemserialname='imei3' and ieee.systemserialname='ieee' and
> spck.simcodename='spck' and nsck.simcodename='nsck' and"'> and m.Deliverynumber= '" + Replace(RecordsetRpt__MMColParam, "'", "''") +> ORDER BY m.msn"
> RecordsetRpt.CursorType = 0
> RecordsetRpt.CursorLocation = 2
> RecordsetRpt.LockType = 1
> RecordsetRpt.Open()
>
> RecordsetRpt_numRows = 0
> %>
> <%
> Dim Repeat1__numRows
> Dim Repeat1__index
>
> Repeat1__numRows = -1
> Repeat1__index = 0
> RecordsetRpt_numRows = RecordsetRpt_numRows + Repeat1__numRows
> %>
> <html>
> <head>
> <title>Untitled Document</title>
> <meta http-equiv="Content-Type" Response.ContentType =
> "application/vnd.ms-excel"> <response.AddHeader "Content-Disposition","attachment;filename.xls"> >> </head>> >
>
> <body>
> <table width="94%">
> <tr>
> <td> </td>
> </tr>
> <tr>
> <td> </td>
> </tr>
> </table>
> <p> </p>
>
>
> <table border="1">
> <tr>
> <td>msn</td>
> <td>dpyprodnum</td>
> <td>imei</td>
> <td>ieee</td>
> <td>spck</td>
> <td>nsck</td>
> <td>nck</td>
> <td>palletnumber</td>
> <td>deliverynumber</td>
> </tr>
> <% While ((Repeat1__numRows <> 0) AND (NOT RecordsetRpt.EOF)) %>
> <tr>
> <td><%=(RecordsetRpt.Fields.Item("msn").Value)%> </td>
> <td><%=(RecordsetRpt.Fields.Item("dpyprodnum").Val ue)%></td>
> <td><%=(RecordsetRpt.Fields.Item("imei").Value)% ></td>
> <td><%=(RecordsetRpt.Fields.Item("ieee").Value)% ></td>
> <td><%=(RecordsetRpt.Fields.Item("spck").Value)% ></td>
> <td><%=(RecordsetRpt.Fields.Item("nsck").Value)% ></td>
> <td><%=(RecordsetRpt.Fields.Item("nck").Value)%> </td>
> <td><%=(RecordsetRpt.Fields.Item("palletnumber").V alue)%></td>
> <td><%=(RecordsetRpt.Fields.Item("deliverynumber") .Value)%></td>
> </tr>
> <%
> Repeat1__index=Repeat1__index+1
> Repeat1__numRows=Repeat1__numRows-1
> RecordsetRpt.MoveNext()
> Wend
> %>
> </table>
> <p> </p>
> </body>
> </html>
> <%
> RecordsetRpt.Close()
> Set RecordsetRpt = Nothing
> %>
> Text
>
CMBergin Guest
-
samkry #10 Re: extract record from database to notepad or excel
Thanks..
I able to display page successfully, but one think that I need to ask is when
I extracted the value out to excel file all numbers that lead with 0 infront
will be truncated, as u know that with excel we can fromat the cell to text so
it won't deleted the value, so how can aviod from my data from being truncated?
samkry Guest
-
CMBergin #11 Re: extract record from database to notepad or excel
This is a little more difficult.
First, try putting an apostrophe (') in front of the value to see if that
will make Excel interpret the value as text instead of a number. That might
solve your problem.
If not, try casting the value from the database as text (i.e., SELECT
CAST(MyField AS VARCHAR) AS MyField FROM ...)
If not, post back with what database system you're using and we'll try to
figure something out.
"samkry" <webforumsuser@macromedia.com> wrote in message
news:cvi91p$nul$1@forums.macromedia.com...when> Thanks..
> I able to display page successfully, but one think that I need to ask isinfront> I extracted the value out to excel file all numbers that lead with 0text so> will be truncated, as u know that with excel we can fromat the cell totruncated?> it won't deleted the value, so how can aviod from my data from being>
CMBergin Guest
Reply With QuoteSimilar Questions and Discussions
Posting Permissions
- You may not post new threads
- You may post replies
- You may not post attachments
- You may not edit your posts
