Read Excel File into Access Database 1

ebaad · Mar 18, 2007

Hi,

Just wanted to ask for some help regarding the following piece of code, I'm trying to read from the excel file and update the data into and access database. Reading from excel file works fine and I can display it on the web in HTML but when I try to update the recordset in the access database it gives me an error.
Following is the error and the code, I would really appreciate if you can help me with this.
ERROR:
Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E21)
Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.
/mgma/loadxls.asp, line 58

CODE:

<%@LANGUAGE="VBSCRIPT" CODEPAGE="1252"%>

<%
Dim rsAdd
Dim rsAdd_numRows

Set rsAdd = Server.CreateObject("ADODB.Recordset")
rsAdd.ActiveConnection = MM_mgma_STRING
rsAdd.Source = "SELECT * FROM tRDATA"
'rsAdd.CursorType = 2
rsAdd.CursorLocation = 2
rsAdd.LockType = 3
rsAdd.Open()
'rsAdd.AddNew

rsAdd_numRows = 0
%>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "

http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="

http://www.w3.org/1999/xhtml">

<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
<title>Untitled Document</title>
</head>
<body>
<%
Dim objConn, objRS, strSQL
Dim x, curValue, y

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 A1:Q10000"
Set objRS=objConn.Execute(strSQL)
%>
<table border="0">
<tr>
<%
For x=0 To objRS.Fields.Count-1
Response.Write("<th>" & objRS.Fields(x).Name & "</th>")
Next
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>")
arrMy = split(y, ",")
'+++++++++++++++++++
rsAdd.AddNew
For x=1 to rsAdd.Fields.Count-1
rsAdd.Fields(x).value=arrMy(x)
next
rsAdd.Update
'+++++++++++++++++++
objRS.MoveNext
y = " "
Loop
%>
</tr>
</table>
</body>

</html>
<%
rsAdd.Close
Set rsAdd = Nothing
%>

ebaad · Mar 19, 2007

Finally I figured out how to do this with the least effort,

<%@LANGUAGE="VBSCRIPT" CODEPAGE="1252"%>

<%
'DECLARE THE ACCESS DATABASE CONNECTION
Dim rsAdd
Dim rsAdd_numRows

Set rsAdd = Server.CreateObject("ADODB.Recordset")
rsAdd.ActiveConnection = MM_mgma_STRING
rsAdd.Source = "SELECT * FROM tRDATA"
rsAdd.CursorLocation = 2
rsAdd.LockType = 3
rsAdd.Open()

rsAdd_numRows = 0
%>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "

http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="

http://www.w3.org/1999/xhtml">

<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
<title>IMPORT EXCEL FILE IN ACCESS DATABASE</title>
</head>
<body>
<%
Dim objConn, objRS, strSQL
Dim xIndex

'DECLARE THE EXCEL WORKSHEET CONNECTION
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_Acad_Comp.xls") & "; "

'SET QUERY TO SELECT THE DATA CELLS FROM THE EXCEL WORKSHEET
'THIS CAN BE DONE IN VARIOUS DIFFERENT WAYS, BUT THIS WORKED
'FOR MY PURPOSE
strSQL = "SELECT * FROM A1:Q10000"

'EXECUTE THE QUERY TO THE EXCEL WORKSHEET
Set objRS=objConn.Execute(strSQL)
%>



<table border="0">
<tr>
<%
'EXTRACT THE HEADER VALUES TO DISPLAY ON THE WEBPAGE
For xIndex=0 To objRS.Fields.Count-1
Response.Write("<th>" & objRS.Fields(xIndex).Name & "</th>")
Next

'ITERATE THROUGH THE EXCEL WORKSHEET AND UPDATE INTO THE ACCESS DATABASE
'NOTE THE VALUE OF xIndex IS DIFFERENT FOR BOTH ACCESS AND EXCEL ARRAY
'THIS IS TO TAKE IN ACCOUNT THE PRIMARY KEY VALUE FIELD IN ACCESS IF IT IS
'SET TO AUTO NUMBER
Do Until objRS.EOF
If IsNull(objRS.Fields(0).Value) Then Exit Do
rsAdd.AddNew
Response.Write("<tr>")
For xIndex=0 To objRS.Fields.Count-1
curValue = objRS.Fields(xIndex).Value
Response.Write("<td>" & objRS.Fields(xIndex).Value & "</td>")
rsAdd.Fields(xIndex+1).Value = objRS.Fields(xIndex).Value
Next
Response.Write("</tr>")
rsAdd.Update
objRS.MoveNext
Loop
%>
</tr>
</table>
</body>

</html>


<%
rsAdd.Close
Set rsAdd = Nothing
%>

Tarwn · Mar 19, 2007

Good job, I like seeing it when people who find solutions to their problems bring them back to share with the community.

A couple suggestions:
1) Inside your loop your assigning the value from the recordset to a variable curValue. Why not use that in the next two lines instead of going back and re-referencing the recordset?
2) You should look into the GetRows() method of the recordset object. This method exports a two-dimensional array of values from the recordset and is much faster to handle in a loop then a standard recordset. In this situation I would use it for the Excel recordset. This would also remove the need for the curValue variable.

Are you planning on expanding this to import all of the files in the directory? You could use a FileSystemObject to loop through the files and grab the ones that match a pattern (like *.xls) and then process them all in a loop. If you have a lot of files this could speed things up, for only a few files I would suggest a manual copy and paste (unless you want the practice with FSO and deleting data out of your DB from test runs).

Again, good job and figuring out the solution on your own, and thanks for posting it back for later posters to be able to reference,
-T

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Read Excel File into Access Database 1

ebaad

Technical User

ebaad

Technical User

Tarwn

Programmer

Similar threads

Part and Inventory Search

Sponsor