ASP将Excel导入到Access数据库的方法实例

Excel中的数据导入到Access中,前提是在Access中的 表 关于同志近三年现实表现材料材料类招标技术评分表图表与交易pdf视力表打印pdf用图表说话 pdf 已经建好。 dim conn dim conn2 set conn=CreateObject("ADODB.Connection") conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Jet OLEDB:Database Password=;Data Source=c:\book1.mdb" set conn2=CreateObject("ADODB.Connection") conn2.Open "Provider=Microsoft.Jet.OLEDB.4.0;Jet OLEDB:Database Password=;Extended properties=Excel 5.0;Data Source=c:\book1.xls" sql = "select * FROM [Sheet1$]" set rs = conn2.execute(sql) while not rs.eof sql = "insert into xxx([a],[bb],[c],[d]) values('"& fixsql(rs(0)) &"','"& fixsql(rs(1)) &"','"& fixsql(rs(2)) &"','"& fixsql(rs(3)) &"')" conn.execute(sql) rs.movenext wend conn.close set conn = nothing conn2.close set conn2 = nothing function fixsql(str) dim newstr newstr = str if isnull(newstr) then newstr = "" else newstr = replace(newstr,"'","'") end if fixsql = newstr end function 导入到Sql Server数据库中时,如果Excel文件和数据库不在同一台服务器上时,请参考上面的代码。在同一机器上可以参考下面代码(不需要先把表建表,程序会自己动建表,用Excel 中的第一行数据做为表的字段名): dim conn set conn=CreateObject("ADODB.Connection") conn.Open ("driver={SQL Server};server=localhost;uid=sa;pwd=sa;database=hwtemp;") sql = "select * into newtable FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0','Data Source=""c:\book1.xls"";User ID=Admin;Password=;Extended properties=Excel 5.0')...[Sheet1$] " conn.execute(sql) conn.close set conn = nothing 以下代码优点,不用打开EXCEL进程,不会出现EXCEL进程无法结束而死机.缺点:可能会出现导入空格的现像. <% sub dataIntoSqlServer_ceritificate(strFileName,strSheetName,myConn) '定义 dim myConnection dim strName dim rsXsl,rsSql dim str_Xsl,str_Sql dim myConn_Xsl dim cmd dim i,j dim maxId strName=strFileName set myConnection=server.createobject("adodb.connection") set rsXsl=Server.Createobject("ADODB.Recordset") set rsSql=Server.CreateObject("ADODB.Recordset") set cmd=Server.CreateObject("ADODB.Command") cmd.ActiveConnection=myConn myConn_Xsl="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strName & ";Extended Properties=Excel 8.0" 'myConn_Xsl="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Application("ASP_Directory") & "chapter05\database\" & strName & ";Extended Properties=Excel 8.0" '打开连接 myConnection.open myConn_Xsl '打开表 str_Xsl="select * from ["& strSheetName &"$]" rsXsl.open str_Xsl,myConnection,1,1 Do While not rsXsl.eof '取出最大值 str_Sql="select Max(id) as maxId from new" rsSql.open str_Sql,myConn,1,3 If Not rsSql.Eof Then If not isNull(rsSql("maxId")) Then maxId=Clng(rsSql("maxId"))+1 Else maxId=1 End if else maxId=1 End if rsSql.close'//关闭对象 '加入数据库 strqbsr="" yssr="" str_Sql="insert into new values("&maxId&",'"&rsXsl(1)&"','"&rsXsl(2)&"','"&rsXsl(3)&"','"&rsXsl(4)&"','"&rsXsl(5)&"','"&rsXsl (6)&"','"&rsXsl(7)&"','"&rsXsl(8)&"','"&rsXsl(9)&"','"&rsXsl(10)&"','"&rsXsl(11)&"','"&rsXsl(12)&"','"& rsXsl(13)&"','"&rsXsl(14)&"','"&rsXsl(15)&"','"&rsXsl(16)&"','"&strqbsr&"','"&yssr&"')" cmd.CommandT ext=str_Sql cmd.Execute() ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' j=j+1 rsXsl.movenext Loop response.write "共导入" & j-1 & "条记录.
" response.write "确定" set rsXsl=nothing set rsSql=nothing set myconnection=nothing set cmd=nothing end sub file1="'"&request.form("filename2")&"'" strtj=mid(request.form("filename2"),instrrev(file1,"\"),(instrrev(file1,".")-instrrev(file1,"\")-1)) if file1="" then response.write "请选择您要导入的Excel表!

" %> 上一页 response.end end if 'respons.write "'"&request.form("filename")&"'" myconn="DRIVER={SQL SERVER};SERVER=(local);uid=sa;pwd=sa;DA TABASE=qjgsj_data" dataIntoSqlServer_ceritificate file1,""&strtj&"",myconn %>