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 路径与文件名.