Page 1 of 2 12 Last
  • Jump to page:
    #1
  1. Moderator From Beyond
    ASP Mastermind (5000+ posts)

    Join Date
    Sep 2004
    Location
    Israel
    Posts
    31,135
    Rep Power
    2922

    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") & "; "
    
    'if using xslx (Excel 2007) use this instead:
    'objConn.Open "Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};"&_
    '	"DBQ=" & Server.MapPath("excelfile.xlsx") & ";"
    
    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>

    Comments on this post

    • kanenas.net agrees
    Last edited by Shadow Wizard; July 20th, 2010 at 06:41 AM. Reason: added support for Excel 2007 (xlsx)
  2. #2
  3. No Profile Picture
    Registered User
    ASP Explorer (0 - 99 posts)

    Join Date
    Sep 2006
    Posts
    2
    Rep Power
    0

    Thumbs up How to read from client side?


    Excellent code.
    How about reading the excel file from client side?
    Originally Posted by Shadow Wizard
    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>
  4. #3
  5. Moderator From Beyond
    ASP Mastermind (5000+ posts)

    Join Date
    Sep 2004
    Location
    Israel
    Posts
    31,135
    Rep Power
    2922
    Originally Posted by kamrag
    Excellent code.
    How about reading the excel file from client side?
    you have to first upload the file to the server, then you can
    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.
  6. #4
  7. No Profile Picture
    Registered User
    ASP Explorer (0 - 99 posts)

    Join Date
    Jan 2007
    Posts
    1
    Rep Power
    0
    Will this code run for any ms excel version or is there any limitation.

    Also will it run on any windows (98, 200, 2003).

    Thanks,
    Rupak
  8. #5
  9. Moderator From Beyond
    ASP Mastermind (5000+ posts)

    Join Date
    Sep 2004
    Location
    Israel
    Posts
    31,135
    Rep Power
    2922
    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.
  10. #6
  11. No Profile Picture
    Registered User
    ASP Explorer (0 - 99 posts)

    Join Date
    Mar 2007
    Posts
    7
    Rep Power
    0

    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
  12. #7
  13. Senior Sarcasm Wizardess
    ASP Mastermind (5000+ posts)

    Join Date
    Feb 2005
    Location
    Dreamland
    Posts
    13,608
    Rep Power
    2462
    what is happening with the above code? what is it doing or not doing? what error messages are you getting?

    Comments on this post

    • Ebaad agrees
    Quote of the Month:
    Planning: Much work remains to be done before we can announce our total failure to make any progress.

    Questions to Ponder:
    If convenience stores are open 24 hours a day, 365 days a year, why are there locks on the doors?

    iif([sarcasm]=true,iif([you have to ask]=true,"didn't work","ha ha ha"),"not sarcasm")
    copyrightę 2008 sbenj69
  14. #8
  15. No Profile Picture
    Registered User
    ASP Explorer (0 - 99 posts)

    Join Date
    Mar 2007
    Posts
    7
    Rep Power
    0

    Classic ASP: read Excel file into Recordset


    Originally Posted by mehere
    what is happening with the above code? what is it doing or not doing? what error messages are you getting?
    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.
  16. #9
  17. Senior Sarcasm Wizardess
    ASP Mastermind (5000+ posts)

    Join Date
    Feb 2005
    Location
    Dreamland
    Posts
    13,608
    Rep Power
    2462
    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.
  18. #10
  19. No Profile Picture
    Registered User
    ASP Explorer (0 - 99 posts)

    Join Date
    Mar 2007
    Posts
    7
    Rep Power
    0
    Originally Posted by mehere
    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.
    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.
  20. #11
  21. No Profile Picture
    Registered User
    ASP Explorer (0 - 99 posts)

    Join Date
    Mar 2007
    Posts
    7
    Rep Power
    0
    Originally Posted by Ebaad
    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.
    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">&nbsp;
    				</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="2" align="center">
    						<input type="submit" name="Submit" value="SUBMIT">
    					</td>
    				</tr>
    			</table>
    			</form>      <p>&nbsp; </p>
    			</td>
    		</tr>
    	</table>	
    </body>
    </html>
    <%
    End If
    %>
    <%
    rsYear.Close()
    Set rsYear = Nothing
    %>
    <%
    rsDatatype.Close()
    Set rsDatatype = Nothing
    %>
    <%
    rsPhytype.Close()
    Set rsPhytype = Nothing
    %>
    <%
    Set rsAdd = Nothing
    'rsAdd.Close()
    %>
  22. #12
  23. No Profile Picture
    Registered User
    ASP Explorer (0 - 99 posts)

    Join Date
    Mar 2007
    Posts
    7
    Rep Power
    0

    Classic ASP: read Excel file into Recordset


    Hello,

    I seem to be really in trouble now, I just added few things in the script that I was working on and now it gives me the following error
    "Multiple Step OLE DB operation generarted errors. Check
    each OLE DB status value, if available no work was done."

    The database gets updated with the values though and I'm running it without any arguments so the initial part of the If statement gets executed.
    Please help,
    Thanks
    Ebaad.

    Code:
    <%@LANGUAGE="VBSCRIPT"%>
    <!--#include file="Connections/mgma.asp" -->
    <%
    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
    %>
    <%
    Dim rsAdd
    Dim rsAdd_numRows
    
    Set rsAdd = Server.CreateObject("ADODB.Recordset")
    rsAdd.ActiveConnection = MM_mgma_STRING
    rsAdd.Source = "SELECT *  FROM tRDATA"
    rsAdd.CursorType = 0
    rsAdd.CursorLocation = 2
    rsAdd.LockType = 3
    rsAdd.Open()
    
    rsAdd_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) - 1
    
    	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\2003_04_05_Phy_Comp.xls") & "; "
    	strSQL = "SELECT * FROM A2:Q10000"
    	Set objRS=objConn.Execute(strSQL)
    	Response.Write("<table 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("</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("</tr>")
    		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">&nbsp;
    				</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="2" align="center">
    						<input type="submit" name="Submit" value="SUBMIT">
    					</td>
    				</tr>
    			</table>
    			</form>      <p>&nbsp; </p>
    		</td>
    	  </tr>
    	</table>	
    </body>
    </html>
    <%
    End If
    %>
    <%
    rsYear.Close()
    Set rsYear = Nothing
    %>
    <%
    rsDatatype.Close()
    Set rsDatatype = Nothing
    %>
    <%
    rsPhytype.Close()
    Set rsPhytype = Nothing
    %>
    <%
    rsAdd.Close()
    Set rsAdd = Nothing
    %>
  24. #13
  25. No Profile Picture
    Registered User
    ASP Explorer (0 - 99 posts)

    Join Date
    May 2007
    Posts
    11
    Rep Power
    0
    I hate to bump an old thread but I have a question related to the content.

    What would the query be if you are wanting to exclude rows that have certain text in a certain column?



    -Steve
  26. #14
  27. Senior Sarcasm Wizardess
    ASP Mastermind (5000+ posts)

    Join Date
    Feb 2005
    Location
    Dreamland
    Posts
    13,608
    Rep Power
    2462
    something like this:
    Code:
    SELECT * FROM tbl_TableName WHERE ceratain_column <> 'certain text'
  28. #15
  29. No Profile Picture
    Registered User
    ASP Explorer (0 - 99 posts)

    Join Date
    May 2007
    Posts
    11
    Rep Power
    0
    Does this still apply when working in excel (Since this thread works with excel)?

    -Steve
Page 1 of 2 12 Last
  • Jump to page:

Similar Threads

  1. Xml file read but html values not coming
    By shaileshk in forum ASP Development
    Replies: 10
    Last Post: June 7th, 2006, 05:50 AM
  2. Get a excel file from client machine
    By satyajit in forum ASP Development
    Replies: 1
    Last Post: April 21st, 2006, 08:25 AM
  3. IIS 6.0 Crashing Daily on Production Server
    By JMelsoner in forum Microsoft IIS
    Replies: 5
    Last Post: April 12th, 2006, 06:43 PM
  4. kindly check what is wrong in checking an excel file open or not
    By kiran_karnati in forum Visual Basic Programming
    Replies: 1
    Last Post: September 17th, 2004, 08:30 AM
  5. How can I read the data from txt file and insert these into sql table
    By Steve Schofield in forum ASP Development
    Replies: 1
    Last Post: February 4th, 2002, 04:51 AM

IMN logo majestic logo threadwatch logo seochat tools logo