《Excel数据导入到Access Sql》由会员分享,可在线阅读,更多相关《Excel数据导入到Access Sql(2页珍藏版)》请在金锄头文库上搜索。
1、Excel数据导入到Access,Sql Server中示例代码2008-08-18 09:50将Excel中的数据导入到Access中,前提是在Access中的表已经建好。dim conndim conn2set conn=CreateObject(ADODB.Connection)conn.Open Provider=Microsoft.Jet.OLEDB.4.0;Jet OLEDB:Database Password=;Data Source=c:book1.mdbset conn2=CreateObject(ADODB.Connection)conn2.Open Provider=Mi
2、crosoft.Jet.OLEDB.4.0;Jet OLEDB:Database Password=;Extended properties=Excel 5.0;Data Source=c:book1.xlssql = SELECT * FROM Sheet1$ set rs = conn2.execute(sql)while not rs.eofsql = insert into xxx(a,b,c,d) values(& fixsql(rs(0) &,& fixsql(rs(1) &,& fixsql(rs(2) &,& fixsql(rs(3) &)conn.execute(sql)rs
3、.movenextwendconn.closeset conn = nothingconn2.closeset conn2 = nothingfunction fixsql(str)dim newstrnewstr = strif isnull(newstr) thennewstr = elsenewstr = replace(newstr,)end iffixsql = newstrend function导入到Sql Server数据库中时,如果Excel文件和数据库不在同一台服务器上时,请参考上面的代码。在同一机器上可以参考下面代码(不需要先把表建表,程序会自己动建表,用Excel中的第
4、一行数据做为表的字段名):dim connset 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.closeset conn = nothing