在SQL Server中查询Excel数据（sp_OpenExcel）

wojkxlq

　　开始：
　　在工作中经常要把Excel的数据转换成SQL导入脚本，这里写了一个存储过程sp_OpenExcel，实现查询Excel文件并生成Insert脚本。
　　sp_OpenExcel:


View Code


Use master
Go
if object_ID('[sp_OpenExcel]') is not null
Drop Procedure [sp_OpenExcel]
Go
Create Proc sp_OpenExcel
(
@ExcelFile nvarchar(1024),
@Sheet nvarchar(512),
@Cols nvarchar(1024)=Null,
@Where nvarchar(1024)=Null,
@InsertTable nvarchar(512)=Null,
@IsCreateScript bit=0
)
As
Set Nocount On
Declare @sql nvarchar(4000)
If Isnull(@Sheet,'')=''
Set @Sheet='Sheet1'
If Isnull(@Cols,'')=''
Set @Cols='*'
If Isnull(@Where,'')=''
Set @Where=''
Else
Set @Where='Where '+@Where
Set @sql='Use Test '+Char(13)+Char(10)
If Isnull(@InsertTable,'')''
Set @sql=@sql+'If object_id('+Quotename(@InsertTable,'''')+') Is Not Null Drop Table '+Quotename(@InsertTable)+Char(13)+Char(10)
Set @sql=@sql+'Set Nocount Off '+Char(13)+Char(10)
If @IsCreateScript =1
Set @sql=@sql+'Set Nocount On '+Char(13)+Char(10)

Set @sql=@sql+'Select '+@Cols+Char(13)+Char(10)

If Isnull(@InsertTable,'')''
Set @sql=@sql+'Into '+@InsertTable+Char(13)+Char(10)
-- Office 2007 & 2010
Set @sql=@sql+'    From Openrowset(''Microsoft.ACE.OLEDB.12.0'',''EXCEL 12.0;HDR=YES;IMEX=1; DATABASE='+@ExcelFile+''',['+@Sheet+'$])a '+Char(13)+Char(10)+@Where+Char(13)+Char(10)
-- Office 97-2003
--Set @sql=@sql+'    From Openrowset(''Provider=Microsoft.Jet.OLEDB.4.0'',''EXCEL 8.0;HDR=YES;IMEX=1; DATABASE='+@ExcelFile+''',['+@Sheet+'$])a '+Char(13)+Char(10)+@Where+Char(13)+Char(10)

Set @sql=@sql+'Set Nocount On '+Char(13)+Char(10)
If Isnull(@IsCreateScript,0)=1
Set @sql=@sql+'Exec sp_insertsql '+Quotename(@InsertTable)+Char(13)+Char(10)
If Isnull(@InsertTable,'')'' And @IsCreateScript=1
Set @sql=@sql+'Drop Table '+Quotename(@InsertTable)+Char(13)+Char(10)
Exec(@sql)
Go
　　存储过程sp_OpenExcel中生成Insert脚本是借助另外一个存储过程sp_InsertSQL来实现：
　　sp_InsertSQL:


View Code


Use master
Go
if object_ID('[sp_InsertSQL]') is not null
Drop Procedure [sp_InsertSQL]
Go
/*生成Insert语句V2.1 Andy 2012-12-7
V2.1 修改了smalldatetime & datetime类型的处理
在V1.0版本的基础上纠正了插入数据感觉慢的问题。
*/
Create Proc sp_InsertSQL
(
@object1    sysname,
@object2    sysname=null
)
As
/*
@object1 源表名
@object2 目标表名
*/
Set Nocount On
Declare @SqlInsert    nvarchar(4000),
@SqlSelect    nvarchar(4000),
@SqlPrint    nvarchar(4000),
@Enter        nvarchar(2),
@Rows        int,
@i            int,
@PrintMaxRows int
If object_id(@object1) Is Null
Begin
Raiserror 50001 N'无效的表名!'
Return
End
If Isnull(@object2,'')=''
Set @object2='#'+@object1
If object_id('tempdb..#Sql') Is Not Null
Drop Table #Sql

Set @Enter=Char(13)+Char(10)
Select    @SqlInsert=Isnull(@SqlInsert+',','Insert Into '+@object2+' (')+Quotename(Name),
@SqlSelect=Isnull(@SqlSelect+'+'',''',' Select ')+'+'+
Case
When xtype In(34,48,52,56,59,60,62,104,106,108,122,127,165,173,189) Then 'Isnull(Rtrim('+Quotename(name)+'),''Null'')'+@Enter
When xtype =58 Then 'Isnull(''''''''+Convert(nchar(16),'+Quotename(name)+',121)+'''''''',''Null'')'+@Enter --smalldatetime
When xtype =61 Then 'Isnull(''''''''+Convert(nchar(23),'+Quotename(name)+',121)+'''''''',''Null'')'+@Enter --datetime
When xtype In(35,36,167,175,241) Then 'Isnull(''N''''''+Rtrim('+Quotename(name)+')+'''''''',''Null'')'+@Enter
When xtype In(98,99,231,239,231)Then 'Isnull(''N''''''+Rtrim(Replace('+Quotename(name)+','''''''',''''''''''''))+'''''''',''Null'')'+@Enter
End
From syscolumns
Where id=object_id(@object1)
Order By colid
Set @SqlInsert=@SqlInsert+')'
Create Table #Sql(ID int Identity(1,1) Primary Key,Sql nvarchar(4000))

Insert Into #Sql
Exec (@SqlSelect+' As Sql From '+@object1)
Set @Rows=@@Rowcount
If Exists(Select 1 From syscolumns Where id=object_id(@object1) And Colstat=1)
Print 'Set Identity_Insert '+Quotename(@object2) +' On'
Print N'Begin Try'+@Enter+Char(9)+'Begin Tran'+@Enter+Char(9)+@SqlInsert
Set @i=0
Set @PrintMaxRows=50
While @i0
Begin
Print (Char(9)+@SqlInsert)
End
If @i%@PrintMaxRows @PrintMaxRows-1 And @i