access数据库中数据从excel到access批量导入代码
发布网友
发布时间：2022-04-08 18:49
共3个回答
热心网友
时间：2022-04-08 20:18
exceldb=SavePath&FileName '获取传递过来的值
Dim StrConnect,ccid,excelstr,rsc
ccid=0
'Excel连接驱动
excelstr="provider=Microsoft.Jet.OLEDB.4.0; Data Source="&server.mappath(exceldb)&";Extended Properties=Excel 8.0"
'excelstr="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.MapPath( "book1.xls" )&";Extended Properties='Excel 8.0;HDR=NO';" '可以读取第一行记录
set StrConnect=CreateObject("ADODB.Connection")
StrConnect.Open excelstr
adSchemaTables=20
set rst = StrConnect.OpenSchema(adSchemaTables)
'注意 表名一定要以下边这种格试 "[表名$]" 书写
Set rsc = Server.CreateObject("ADODB.Recordset")
Sqlc="select * from ["&rst("Table_Name").Value&"]"
rsc.Open Sqlc,StrConnect,2,2
if rsc.bof and rsc.eof then
Response.write "<script language='javascript'>" & chr(13)
Response.write "alert('导入数据失败,没有数据可以导入,请检查您的Excel文件!');history.go(-1);" & Chr(13)
Response.write "</script>" & Chr(13)
Response.End
else
sql="select [id] from [content] where [dn] like '"&yy&"%' or ([Gdate]='"&trim(rsc(0))&"' and [gtime]='"&trim(rsc(1))&"' and [buyerid]='"&trim(rsc(29))&"')"
set rss=conn.execute(sql)
if not(rss.bof and rss.eof) then
Response.write "<script language='javascript'>" & chr(13)
Response.write "alert('导入数据失败,数据库已存在此数据!');history.go(-1);" & Chr(13)
Response.write "</script>" & Chr(13)
Response.End
else
dim rs,sq
rsc.movefirst
do while not rsc.eof
ccid=ccid+1
dim mm
mm=ccid
for a=1 to 5
if len(mm)<5 then
mm="0"&cstr(mm)
else
mm=yy&mm
exit for
end if
next
dim nn,kk
set rsn=conn.execute("select [img_url],[p_name],[p_num] from [BuNengSan] where [p_name]='"&strkill(trim(rsc(19)))&"'")
if rsn.bof and rsn.eof then
nn="未知"
kk="upproct/defeat.gif"
else
nn=rsn("p_num")
kk=rsn("img_url")
end if
rsn.close
set rsn=nothing
dim jj
if rsc(8)<0 then
jj=0
else
jj=1
end if
dim aa
if trim(rsc(36))="" or isnull(trim(rsc(36))) then
aa=0
else
aa=trim(rsc(36))
end If
Dim dd
If trim(rsc(46))="" Or IsNull(trim(rsc(46))) Then
dd="未知"
Else
If InStr(trim(rsc(46)),",")>0 Then
dd=trim(rsc(46))
Else
dd=Trim(Replace(trim(rsc(46)),"A",""))
End if
End if
sq="select * from [content] where ID is Null"
set rs=server.createobject("adodb.recordset")
rs.open sq,conn,1,3
rs.addnew
rs("dn")=trim(mm)
rs("sku")=trim(nn)
rs("img_url")=trim(kk)
rs("Gdate")=trim(rsc(0))
rs("gtime")=trim(rsc(1))
rs("Gname")=strkill(trim(rsc(3)))
rs("Status")=trim(rsc(5))
rs("cross")=cint(jj)
rs("pname")=strkill(trim(rsc(19)))
rs("note")=trim(rsc(11))
rs("Email")=trim(rsc(12))
rs("IID")=dd
rs("qc")="未指定"
rs("buyerid")=trim(rsc(29))
rs("pnum")=cint(aa)
rs("add1")=trim(rsc(38))
rs("add2")=trim(rsc(39))
rs("add3")=trim(rsc(40))
rs("add4")=trim(rsc(41))
rs("add5")=trim(rsc(42))
rs("cadd")=trim(rsc(43))
rs.update
rsc.movenext
loop
Response.write "<script language='javascript'>" & chr(13)
Response.write "alert('导入数据成功!');" & Chr(13)
'Response.write "window.opener.location.reload();"&Chr(13)
Response.write "window.opener.location.href=window.opener.location.href;"&Chr(13)
Response.write "window.close();"&Chr(13)
Response.write "</script>" & Chr(13)
Response.End
end if
rss.close
set rss=nothing
end if
rsc.close
set rsc=nothing
%>
这是我写的代码,我用的是可以上传的,所以我那个excel表是动态的,你如果只在本地用就自己改一下就行了
热心网友
时间：2022-04-08 21:36
<%
Dim cn,oConn,connstr
'打开XLS.
Set cn = Server.CreateObject("ADODB.Connection")
cn.Provider = "Microsoft.Jet.OLEDB.4.0 "
cn.ConnectionString = "Data Source=" & Server.MapPath("data.xls") & ";" & _
"Extended Properties=Excel 8.0;"
cn.Open
'打开MDB.
connstr="DBQ="+server.MapPath("data.mdb")+";DefaultDir=;DRIVER={Microsoft Access Driver (*.mdb)};"
set oConn=server.CreateObject("ADODB.CONNECTION")
oConn.open connstr
'读取数据.
set rsRead=server.CreateObject("ADODB.Recordset")
rsRead.Open "select * from [Sheet1$]",cn,1,1
do until rsRead.EOF
'写入数据库.
oConn.Execute("Insert into users(userid,password)Values('"& rsRead.Fields("userid")&"','" &rsRead.Fields("password") & "')" )
rsRead.MoveNext
loop
%>
数据库data.mdb
表users
id,uesrid,password
Excel文件data.xls
数据
userid password
wang w521
zhange z254
hong h112
ASP读取EXCEL注事项!
'i)将Excel97或Excel2000生成的XLS文件（book）看成一个数据库，其中的每一个工作表(sheet)看成数据库表
'ii)ADO假设Excel中的第一行为字段名.所以你定义的范围中必须要包括第一行的内容
'iii)Excel中的行标题（即字段名）不能够包含数字. Excel的驱动在遇到这种问题时就会出错的。例如你的行标题名为“F1”
'iiii)如果你的Excel电子表格中某一列同时包含了文本和数字的话,那么Excel的ODBC驱动将不能够正常, 处理这一行的数据类型，你必须要保证该列的数据类型一致
热心网友
时间：2022-04-08 23:11
参考资料：http://www.accessbbs.cn/bbs/thread-19612-1-1.html
