excel的电话号码如何导入到SQL中

// 第一步： // 启用Ad Hoc Distributed Queries： exec sp_configure ' show advanced options ' , 1 reconfigure exec sp_configure ' Ad Hoc Distributed Queries ' , 1 reconfigure go // 第二步： SELECT * into dbo.tabel5 FROM OpenDataSource ( ' Microsoft.Jet.OLEDB.4.0 ' , ' Data Source="D:/book.xls";Extended properties=Excel 5.0 ' )... [ Sheet1$ ] go // 第三步： // 使用完成后，关闭Ad Hoc Distributed Queries： exec sp_configure ' Ad Hoc Distributed Queries ' , 0 reconfigure exec sp_configure ' show advanced options ' , 0 reconfigure go 本文来自CSDN博客，转载请标明出处：http: // blog.csdn.net / sz_bdqn / archive / 2008 / 08 / 05 / 2768350 .aspx

将Excel的数据导入SQL server : 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$ ] -- 实例: SELECT * into newtable FROM OpenDataSource ( ' Microsoft.Jet.OLEDB.4.0 ' , ' Data Source="c:/Finance/account.xls";User ID=Admin;Password=;Extended properties=Excel 5.0 ' )...xactions --

1 、在SQL数据库中直接从Excel里面查询数据： select * from OPENROWSET ( ' MICROSOFT.JET.OLEDB.4.0 ' , ' Excel 5.0;HDR=YES;DATABASE=c:/test.xls ' ,sheet1$) 2 、从Excel文件中,导入数据到SQL数据库中, select * into 表 from OPENROWSET ( ' MICROSOFT.JET.OLEDB.4.0 ' , ' Excel 5.0;HDR=YES;DATABASE=c:/test.xls ' ,sheet1$) 3 、从SQL数据库中,导出数据到Excel(excel存在), insert into OPENROWSET ( ' MICROSOFT.JET.OLEDB.4.0 ' , ' Excel 5.0;HDR=YES;DATABASE=c:/test.xls ' ,sheet1$) select * from 表 4 、从SQL数据库中,导出数据到Excel(excel不存在), -- -- 导出表 EXEC master..xp_cmdshell ' bcp 数据库名.dbo.表名 out "c: est.xls" /c -/S"服务器名" /U"用户名" -P"密码" ' -- -- 导出查询语句 EXEC master..xp_cmdshell ' bcp "SELECT au_fname, au_lname FROM pubs..authors ORDER BY au_lname" queryout "c: est.xls" /c -/S"服务器名" /U"用户名" -P"密码" ' 5 、导入导出的存储过程 -- 下面是导出真正Excel文件的方法:（请将一下所有代码复制到存储过程中） if exists ( select * from dbo.sysobjects where id = object_id (N ' [dbo].[p_exporttb] ' ) and OBJECTPROPERTY (id, N ' IsProcedure ' ) = 1 ) drop procedure [ dbo ] . [ p_exporttb ] GO /**//* --数据导出EXCEL 导出表中的数据到Excel,包含字段名,文件为真正的Excel文件 ,如果文件不存在,将自动创建文件 ,如果表不存在,将自动创建表 基于通用性考虑,仅支持导出标准数据类型 --邹建 2003.10(引用请保留此信息)-- */ /**//* --调用示例 p_exporttb @tbname='地区资料',@path='c:',@fname='aa.xls' -- */ create proc p_exporttb @tbname sysname, -- 要导出的表名 @path nvarchar ( 1000 ), -- 文件存放目录 @fname nvarchar ( 250 ) = '' -- 文件名,默认为表名 as declare @err int , @src nvarchar ( 255 ), @desc nvarchar ( 255 ), @out int declare @obj int , @constr nvarchar ( 1000 ), @sql varchar ( 8000 ), @fdlist varchar ( 8000 ) -- 参数检测 if isnull ( @fname , '' ) = '' set @fname = @tbname + ' .xls ' -- 检查文件是否已经存在 if right ( @path , 1 ) <> '' set @path = @path + '' create table #tb(a bit ,b bit ,c bit ) set @sql = @path + @fname insert into #tb exec master..xp_fileexist @sql -- 数据库创建语句 set @sql = @path + @fname if exists ( select 1 from #tb where a = 1 ) set @constr = ' DRIVER={Microsoft Excel Driver (*.xls)};DSN= '''' ;READONLY=FALSE ' + ' ;CREATE_DB=" ' + @sql + ' ";DBQ= ' + @sql else set @constr = ' Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties="Excel 8.0;HDR=YES ' + ' ;DATABASE= ' + @sql + ' " ' -- 连接数据库 exec @err = sp_oacreate ' adodb.connection ' , @obj out if @err <> 0 goto lberr exec @err = sp_oamethod @obj , ' open ' , null , @constr if @err <> 0 goto lberr /**//* --如果覆盖已经存在的表,就加上下面的语句 --创建之前先删除表/如果存在的话 select @sql='drop table ['+@tbname+']' exec @err=sp_oamethod @obj,'execute',@out out,@sql -- */ -- 创建表的SQL select @sql = '' , @fdlist = '' select @fdlist = @fdlist + ' ,[ ' + a.name + ' ] ' , @sql = @sql + ' ,[ ' + a.name + ' ] ' + case when b.name like ' %char ' then case when a.length > 255 then ' memo ' else ' text( ' + cast (a.length as varchar ) + ' ) ' end when b.name like ' %int ' or b.name = ' bit ' then ' int ' when b.name like ' %datetime ' then ' datetime ' when b.name like ' %money ' then ' money ' when b.name like ' %text ' then ' memo ' else b.name end FROM syscolumns a left join systypes b on a.xtype = b.xusertype where b.name not in ( ' image ' , ' uniqueidentifier ' , ' sql_variant ' , ' varbinary ' , ' binary ' , ' timestamp ' ) and object_id ( @tbname ) = id select @sql = ' create table [ ' + @tbname + ' ]( ' + substring ( @sql , 2 , 8000 ) + ' ) ' , @fdlist = substring ( @fdlist , 2 , 8000 ) exec @err = sp_oamethod @obj , ' execute ' , @out out, @sql if @err <> 0 goto lberr exec @err = sp_oadestroy @obj -- 导入数据 set @sql = ' openrowset( '' MICROSOFT.JET.OLEDB.4.0 '' , '' Excel 8.0;HDR=YES;IMEX=1 ;DATABASE= ' + @path + @fname + ''' ,[ ' + @tbname + ' $]) ' exec ( ' insert into ' + @sql + ' ( ' + @fdlist + ' ) select ' + @fdlist + ' from ' + @tbname ) return lberr: exec sp_oageterrorinfo 0 , @src out, @desc out lbexit: select cast ( @err as varbinary ( 4 )) as 错误号 , @src as 错误源, @desc as 错误描述 select @sql , @constr , @fdlist go if exists ( select * from dbo.sysobjects where id = object_id (N ' [dbo].[p_exporttb] ' ) and OBJECTPROPERTY (id, N ' IsProcedure ' ) = 1 ) drop procedure [ dbo ] . [ p_exporttb ] GO /**//* --数据导出EXCEL 导出查询中的数据到Excel,包含字段名,文件为真正的Excel文件 如果文件不存在,将自动创建文件 如果表不存在,将自动创建表 基于通用性考虑,仅支持导出标准数据类型 --邹建 2003.10(引用请保留此信息)-- */ /**//* --调用示例 p_exporttb @sqlstr='select * from 地区资料' ,@path='c:',@fname='aa.xls',@sheetname='地区资料' -- */ create proc p_exporttb @sqlstr varchar ( 8000 ), -- 查询语句,如果查询语句中使用了order by ,请加上top 100 percent @path nvarchar ( 1000 ), -- 文件存放目录 @fname nvarchar ( 250 ), -- 文件名 @sheetname varchar ( 250 ) = '' -- 要创建的工作表名,默认为文件名 as declare @err int , @src nvarchar ( 255 ), @desc nvarchar ( 255 ), @out int declare @obj int , @constr nvarchar ( 1000 ), @sql varchar ( 8000 ), @fdlist varchar ( 8000 ) -- 参数检测 if isnull ( @fname , '' ) = '' set @fname = ' temp.xls ' if isnull ( @sheetname , '' ) = '' set @sheetname = replace ( @fname , ' . ' , ' # ' ) -- 检查文件是否已经存在 if right ( @path , 1 ) <> '' set @path = @path + '' create table #tb(a bit ,b bit ,c bit ) set @sql = @path + @fname insert into #tb exec master..xp_fileexist @sql -- 数据库创建语句 set @sql = @path + @fname if exists ( select 1 from #tb where a = 1 ) set @constr = ' DRIVER={Microsoft Excel Driver (*.xls)};DSN= '''' ;READONLY=FALSE ' + ' ;CREATE_DB=" ' + @sql + ' ";DBQ= ' + @sql else set @constr = ' Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties="Excel 8.0;HDR=YES ' + ' ;DATABASE= ' + @sql + ' " ' -- 连接数据库 exec @err = sp_oacreate ' adodb.connection ' , @obj out if @err <> 0 goto lberr exec @err = sp_oamethod @obj , ' open ' , null , @constr if @err <> 0 goto lberr -- 创建表的SQL declare @tbname sysname set @tbname = ' ##tmp_ ' + convert ( varchar ( 38 ), newid ()) set @sql = ' select * into [ ' + @tbname + ' ] from( ' + @sqlstr + ' ) a ' exec ( @sql ) select @sql = '' , @fdlist = '' select @fdlist = @fdlist + ' ,[ ' + a.name + ' ] ' , @sql = @sql + ' ,[ ' + a.name + ' ] ' + case when b.name like ' %char ' then case when a.length > 255 then ' memo ' else ' text( ' + cast (a.length as varchar ) + ' ) ' end when b.name like ' %int ' or b.name = ' bit ' then ' int ' when b.name like ' %datetime ' then ' datetime ' when b.name like ' %money ' then ' money ' when b.name like ' %text ' then ' memo ' else b.name end FROM tempdb..syscolumns a left join tempdb..systypes b on a.xtype = b.xusertype where b.name not in ( ' image ' , ' uniqueidentifier ' , ' sql_variant ' , ' varbinary ' , ' binary ' , ' timestamp ' ) and a.id = ( select id from tempdb..sysobjects where name = @tbname ) if @@rowcount = 0 return select @sql = ' create table [ ' + @sheetname + ' ]( ' + substring ( @sql , 2 , 8000 ) + ' ) ' , @fdlist = substring ( @fdlist , 2 , 8000 ) exec @err = sp_oamethod @obj , ' execute ' , @out out, @sql if @err <> 0 goto lberr exec @err = sp_oadestroy @obj -- 导入数据 set @sql = ' openrowset( '' MICROSOFT.JET.OLEDB.4.0 '' , '' Excel 8.0;HDR=YES ;DATABASE= ' + @path + @fname + ''' ,[ ' + @sheetname + ' $]) ' exec ( ' insert into ' + @sql + ' ( ' + @fdlist + ' ) select ' + @fdlist + ' from [ ' + @tbname + ' ] ' ) set @sql = ' drop table [ ' + @tbname + ' ] ' exec ( @sql ) return lberr: exec sp_oageterrorinfo 0 , @src out, @desc out lbexit: select cast ( @err as varbinary ( 4 )) as 错误号 , @src as 错误源, @desc as 错误描述 select @sql , @constr , @fdlist go 0 0 0 (请您对文章做出评价)

select * into 表名 from OPENROWSET ( ' MICROSOFT.JET.OLEDB.4.0 ' , ' Excel 5.0;HDR=YES;DATABASE=C:/EXCEL文件名.xls ' ,sheet1$) -- C:/EXCEL文件名.xls 路径与文件名.