|
|||||||||
|
|||||||||
|
|||||||||
 |
|||||||||
| |
||
|
|
|
| |||||||||
 |
|
|
«
Previous Thread
|
Next Thread
»
|
Thread Tools | Search this Thread | Rate Thread | Display Modes |
ASP Free Forums Sponsor:
|
|
|
|
#1
November 8th, 2006, 05:29 AM
|
||||
|
||||
|
Classic ASP: read Excel file into Recordset
Use the below code to read from Excel file as if it was database.
note, Excel must be installed on the Server in order for this code to work, and the IUSR account must have permissions over the Excel file. Code:
<% Option Explicit %>
<html>
<body>
<%
Dim objConn, objRS, strSQL
Dim x, curValue
Set objConn = Server.CreateObject("ADODB.Connection")
objConn.Open "DRIVER={Microsoft Excel Driver (*.xls)}; IMEX=1; HDR=NO; "&_
"Excel 8.0; DBQ=" & Server.MapPath("excelfile.xls") & "; "
strSQL = "SELECT * FROM A1:Q10000"
Set objRS=objConn.Execute(strSQL)
Response.Write("<table border=""1"">")
Response.Write("<tr>")
For x=0 To objRS.Fields.Count-1
Response.Write("<th>" & objRS.Fields(x).Name & "</th>")
Next
Response.Write("</tr>")
Do Until objRS.EOF
Response.Write("<tr>")
For x=0 To objRS.Fields.Count-1
curValue = objRS.Fields(x).Value
If IsNull(curValue) Then
curValue="N/A"
End If
curValue = CStr(curValue)
Response.Write("<td>" & curValue & "</td>")
Next
Response.Write("</tr>")
objRS.MoveNext
Loop
objRS.Close
Response.Write("</table>")
objConn.Close
Set objRS=Nothing
Set objConn=Nothing
%>
</body>
</html>
|
|
#2
January 16th, 2007, 12:28 AM
|
|||
|
|||
How to read from client side?
Excellent code.
How about reading the excel file from client side? Quote:
|
|
#3
January 16th, 2007, 01:11 AM
|
||||
|
||||
|
Quote:
use the code provided above. to upload the file, look for something known as "ASP Upload". you got pure ASP upload script here in the Code Bank as well.
|
|
#5
January 18th, 2007, 05:49 AM
|
||||
|
||||
|
the code requires Office2003 to be installed on the Server.
as far as I know, it would read all versions of Excel files. "will it run on any windows"? the question is meaningless. this is ASP code, it's not windows application. and it's not outputting Excel to the screen, it's putting its contents into recordset and then display the contents as pure text. anyhow, no window here.
|
|
#6
March 13th, 2007, 12:20 PM
|
|||
|
|||
|
Classic ASP: read Excel file into Recordset
Hi Shadow,
The code worked for me right away, but what my main goal is to read an excel file and append an access db with the values from the excel file. To achieve this functionality I think I have to have two recerdset opened at the same time and one from the excel file that I'm trying to read and the other from the database that I'm trying to wirte. I have tried to get this accomplished with several different variations of code but havent been successful so far, I would really appreciate if you can help me with this. Thanks, Ebaad. Below is one of the versions of code that I tried to make it work. Code:
<%@LANGUAGE="VBSCRIPT"%>
<% Option Explicit %>
<!--#include file="Connections/mgma.asp" -->
<%
Dim rsAdd
Dim rsAdd_numRows
Set rsAdd = Server.CreateObject("ADODB.Recordset")
rsAdd.ActiveConnection = MM_mgma_STRING
rsAdd.Source = "SELECT * FROM tACADRAWDATA"
rsAdd.CursorType = 0
rsAdd.CursorLocation = 2
rsAdd.LockType = 3
rsAdd.Open()
rsAdd.Addnew
rsAdd_numRows = 0
%>
<html>
<body>
<%
Dim objConn, objRS, strSQL
Dim objConn2, objRS2, strSQL2
Dim x, curValue, y, arrMy, n, z
Set objConn = Server.CreateObject("ADODB.Connection")
objConn.Open "DRIVER={Microsoft Excel Driver (*.xls)}; IMEX=1; HDR=NO; "&_
"Excel 8.0; DBQ=" & Server.MapPath("Academic_data.xls") & "; "
strSQL = "SELECT * FROM A2:Q10000"
Set objRS=objConn.Execute(strSQL)
'Set objConn2 = Server.CreateObject("ADODB.Connection")
'objConn2.Provider="Microsoft.Jet.OLEDB.4.0"
'objConn2.Open "C:\Inetpub\wwwroot\MGMA\dMGMA.mdb"
'Set objRS2 = Server.CreateObject("ADODB.recordset")
'strSQL2 = "SELECT * FROM tACADRAWDATA"
'objRS2.Open strSQL2, objConn2, 2 ,3
'objRS2.Addnew
Response.Write("<table border=""0"">")
Response.Write("<tr>")
For x=1 To objRS.Fields.Count
Response.Write("<th>" & rsAdd.Fields(x).Name & "</th>")
Next
Response.Write("</tr>")
Do Until objRS.EOF
Do Until rsAdd.EOF
Response.Write("<tr>")
For x=0 To objRS.Fields.Count-1
curValue = objRS.Fields(x).Value
If IsNull(curValue) Then
curValue=" "
End If
curValue = CStr(curValue)
y = y &","& curValue
Response.Write("<td>" & curValue & "</td>")
Next
Response.Write("</tr>")
y = y &","& "2006"
arrMy = split(y, ",")
For x=1 to rsAdd.Fields.Count-1
rsAdd.Fields(x).value=arrMy(x)
next
rsAdd.update
rsAdd.MoveNext
Loop
objRS.MoveNext
Loop
objRS.Close
Response.Write("</table>")
objConn.Close
Set objRS=Nothing
Set objConn=Nothing
%>
</body>
</html>
<%
rsAdd.Close()
Set rsAdd = Nothing
%>
<%
'rsAcad.Close()
'Set rsAcad = Nothing
|
|
#7
March 13th, 2007, 12:27 PM
|
||||
|
||||
|
what is happening with the above code? what is it doing or not doing? what error messages are you getting?
__________________
Come JOIN the party!!! Quote of the Month: Retirement: Because you've given so much of yourself to the company that you don't have anything left we can use. Questions to Ponder: What do you do when you see an endangered animal eating an endangered plant? iif([sarcasm]=true,iif([you have to ask]=true,"didn't work","ha ha ha"),"not sarcasm") copyright© 2008 sbenj69
|
|
#8
March 13th, 2007, 12:36 PM
|
|||
|
|||
|
Classic ASP: read Excel file into Recordset
Quote:
Wow, Thanks I really appreciate your response, the thing is that it is not updating the record in the access database and want me to have anothoer rs open for updating the code in access database. When I do that I just could not figure it out that how to biuld an algorithm wich will read a whole row or record from excel in to an array and update it in to the access database and then read the subsequent recrord and continue until the eof of the excel file. I really need some guidance for this. Thanks, Ebaad.
|
|
#9
March 13th, 2007, 12:49 PM
|
||||
|
||||
|
try this:
Code:
Do Until objRS.EOF
rsAdd.MoveFirst
Response.Write("<tr>")
For x=0 To objRS.Fields.Count-1
curValue = objRS.Fields(x).Value
If IsNull(curValue) Then
curValue=" "
End If
curValue = CStr(curValue)
y = y &","& curValue
Response.Write("<td>" & curValue & "</td>")
Next
Response.Write("</tr>")
y = y &","& "2006"
arrMy = split(y, ",")
rsAdd.AddNew
For x=1 to rsAdd.Fields.Count-1
rsAdd.Fields(x).value=arrMy(x)
next
rsAdd.update
objRS.MoveNext
Loop
i usually just use insert & update statements, i'm shooting in the dark here.
|
|
#10
March 13th, 2007, 01:18 PM
|
|||
|
|||
|
Quote:
That is exactly I think what I was missing I tried something like this in an earlier version but I did not know how to increment the rs.add after updating, while keep incrementing the objRS in excel. I am trying it right now and will let you know the results. Thanks again for all the help, this is something that is the basis of a report first I have to get these values in to the adtabase and then calculate some data for a report for my boss. Thanks, Ebaad.
|
|
#11
March 14th, 2007, 05:55 PM
|
|||
|
|||
|
Quote:
Hi Mehere, The code that you sent worked perfectly, I just had to comment out the MoveFirst and AddNew line. Thanks very much to that, but now I'm trying to use the usedrange.Rows.Count to count up to the last completed row before an empty row in the excel file. But it is ging me error and asking for an object. If You can please look into it I will really appreciate that since it will automate the loading of the files with out any prior formatting Thanks, Ebaad. Code:
<%@LANGUAGE="VBSCRIPT"%>
<!--#include file="Connections/mgma.asp" -->
<%
Dim rsAdd
Dim rsAdd_numRows
Set rsAdd = Server.CreateObject("ADODB.Recordset")
rsAdd.ActiveConnection = MM_mgma_STRING
rsAdd.Source = "SELECT * FROM tACADRAWDATA"
rsAdd.CursorType = 0
rsAdd.CursorLocation = 2
rsAdd.LockType = 3
rsAdd.Open()
rsAdd.AddNew
rsAdd_numRows = 0
%>
<%
Dim rsYear
Dim rsYear_numRows
Set rsYear = Server.CreateObject("ADODB.Recordset")
rsYear.ActiveConnection = MM_mgma_STRING
rsYear.Source = "SELECT * FROM tYEAR"
rsYear.CursorType = 0
rsYear.CursorLocation = 2
rsYear.LockType = 1
rsYear.Open()
rsYear_numRows = 0
%>
<%
Dim rsDatatype
Dim rsDatatype_numRows
Set rsDatatype = Server.CreateObject("ADODB.Recordset")
rsDatatype.ActiveConnection = MM_mgma_STRING
rsDatatype.Source = "SELECT * FROM tDATATYPE"
rsDatatype.CursorType = 0
rsDatatype.CursorLocation = 2
rsDatatype.LockType = 1
rsDatatype.Open()
rsDatatype_numRows = 0
%>
<%
Dim rsPhytype
Dim rsPhytype_numRows
Set rsPhytype = Server.CreateObject("ADODB.Recordset")
rsPhytype.ActiveConnection = MM_mgma_STRING
rsPhytype.Source = "SELECT * FROM tPHYTYPE"
rsPhytype.CursorType = 0
rsPhytype.CursorLocation = 2
rsPhytype.LockType = 1
rsPhytype.Open()
rsPhytype_numRows = 0
%>
<html>
<link href="mgma.css" rel="stylesheet" type="text/css">
<body>
<%
Dim form_val
form_val = Request.Querystring("key_word")
if (form_val <> "") Then
Dim objConn, objRS, strSQL
Dim xIndex, curValue, arrMy, arrTemp, varYear, varTotrows
Dim temp_date
temp_date = now()
varYear = YEAR(now)
Set objConn = Server.CreateObject("ADODB.Connection")
objConn.Open "DRIVER={Microsoft Excel Driver (*.xls)}; IMEX=1; HDR=NO; "&_
"Excel 8.0; DBQ=" & Server.MapPath("EXCEL_REPORTS\Academic_data.xls") & "; "
strSQL = "SELECT * FROM A2:Q10000"
Set objRS=objConn.Execute(strSQL)
varTotrows = objRS.UsedRange.Row.Count
Response.Write("<table valign=center width=100% cellpadding=0 cellspacing=0>")
Response.Write("<tr>")
For x=0 To objRS.Fields.Count-1
Response.Write("<th>" & objRS.Fields(x).Name & "</th>")
Next
Response.Write("<th>YEAR</th>")
Response.Write("</tr>")
Do Until objRS.EOF
'rsAdd.MoveFirst
Response.Write("<tr>")
For xIndex=0 To objRS.Fields.Count-1
curValue = objRS.Fields(xIndex).Value
If IsNull(curValue) Then
curValue="N/A"
End If
arrTemp = arrTemp & "," & curValue
curValue = CStr(curValue)
Response.Write("<td>" & curValue & "</td>")
Next
Response.Write("<td>")
Response.Write(year(now()))
Response.Write("</td>")
Response.Write("</tr>")
arrTemp = arrTemp &","& varYear
arrMy = split(arrTemp, ",")
For xIndex=1 to rsAdd.Fields.Count-1
rsAdd.Fields(xIndex).value=arrMy(xIndex)
next
arrTemp = ""
'rsAdd.AddNew
objRS.MoveNext
Loop
objRS.Close
Response.Write("</table>")
objConn.Close
Set objRS=Nothing
Set objConn=Nothing
else
%>
<table width="90%" border="0" cellpadding="0">
<tr>
<td width="15%" height="156" valign="top">
<table width="130" border="0" cellpadding="5">
<tr>
<td align="left" scope="col">LOAD NEW FILE
</td>
</tr>
<tr>
<td align="left" scope="col">DISPLAY RAW DATA
</td>
</tr>
<tr>
<th scope="col">
</th>
</tr>
</table>
</td>
<td width="85%" valign="top">
<P>
PLEASE ENTER THE FILE THAT YOU WANT TO LOAD IN TO THE DATABASE.
</P>
<form name="form1" method="post" action="readxls.asp?key_word=UPLOAD_DB">
<table width="80%" border="0" align="center" cellpadding="5">
<tr>
<td width="50%" scope="col">
<span class="style1">
<label>SELECT PHYSICIAN TYPE </label>
</span> </td>
<td width="50%" scope="col">
<select name="select" class="select-type1">
<%
While (NOT rsPhytype.EOF)
%>
<option value="<%=(rsPhytype.Fields.Item("PHYTYPE").Value)%>"><%=(rsPhytype.Fields.Item("PHYTYPE").Value)%>
</option>
<%
rsPhytype.MoveNext()
Wend
If (rsPhytype.CursorType > 0) Then
rsPhytype.MoveFirst
Else
rsPhytype.Requery
End If
%>
</select>
</td>
</tr>
<tr>
<td>
<span class="style1">
<label>SELECT COMPENSATION / WORK RVU DATA
</label>
</span>
</td>
<td>
<select name="select" class="select-type1">
<%
While (NOT rsDatatype.EOF)
%>
<option value="<%=(rsDatatype.Fields.Item("DATATYPE").Value)%>"><%=(rsDatatype.Fields.Item("DATATYPE").Value)%>
</option>
<%
rsDatatype.MoveNext()
Wend
If (rsDatatype.CursorType > 0) Then
rsDatatype.MoveFirst
Else
rsDatatype.Requery
End If
%>
</select>
</td>
</tr>
<tr>
<td>
<span class="style1">
<label>SELECT YEAR
</label>
</span>
</td>
<td>
<select name="select" class="select-type1">
<%
While (NOT rsYear.EOF)
%>
<option value="<%=(rsYear.Fields.Item("YEAR").Value)%>"><%=(rsYear.Fields.Item("YEAR").Value)%>
</option>
<%
rsYear.MoveNext()
Wend
If (rsYear.CursorType > 0) Then
rsYear.MoveFirst
Else
rsYear.Requery
End If
%>
</select>
</td>
</tr>
<tr>
<td colspan=" |
Del.icio.us
Digg
Google
Spurl
Blink
Furl
Simpy
Y! MyWeb
