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

开始：

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

sp_OpenExcel:

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:

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 @i<@Rows
Begin
    Select @SqlPrint=Char(9)+Char(9)+'Select '+sql  From #Sql Where id=@i+1
    
    If @i%@PrintMaxRows=0 And @i>0
    Begin
        Print (Char(9)+@SqlInsert) 
    End
    If @i%@PrintMaxRows <>@PrintMaxRows-1 And @i<@Rows -1
        Set @SqlPrint =@SqlPrint+' Union All '
 
    Print @SqlPrint
 
    Set @i=@i+1
End
Print N'    Commit Tran'+@Enter+'End Try'+@Enter+'Begin Catch'+@Enter+'Raiserror 50001 N''插入数据过程中发生错误.'' '+@Enter+'Rollback Tran'++@Enter+'End Catch'
If Exists(Select 1 From syscolumns Where id=object_id(@object1) And Colstat=1)
 
Print 'Set Identity_Insert '+Quotename(@object2) +' Off'
 
Drop Table #Sql
 
 
 
Go

 

 sp_OpenExcel调用说明：

Exec sp_OpenExcel
    @ExcelFile = '', -- nvarchar(1024)
    @Sheet = '', -- nvarchar(512)
    @Cols = '',
    @Where = '', -- nvarchar(1024)
    @InsertTable = '', -- nvarchar(512)
    @IsCreateScript = 0 -- bit

参数：

@ExcelFile: 描述文件路径

@Sheet :Excel工作簿名

@Cols :字段列表

@Where : 筛选条件

@InsertTable : 描述要生成Insert语句的临时表名，不用填写“#”

@IsCreateScript: 当为1的时候，而且@InsertTable非空，就生成Insert数据的脚本

调用方法 1：

use tempdb
Go
Exec sp_OpenExcel 
    @ExcelFile = 'E:\Andy\myScript\Item_Relation\bak\英文名.xlsx', -- nvarchar(1024)
    @Sheet = 'Girls', -- nvarchar(512)
    @Cols = '[排名] As Seq,[女生] As Name',
    @Where = '[排名] between 500 and 2000', -- nvarchar(1024)
    @InsertTable = '', -- nvarchar(512)
    @IsCreateScript = 0 -- bit
GO

调用方法 2：

use tempdb
Go
Exec sp_OpenExcel 
    @ExcelFile = 'E:\Andy\myScript\Item_Relation\bak\英文名.xlsx', -- nvarchar(1024)
    @Sheet = 'Girls', -- nvarchar(512)
    @Cols = '[排名] As Seq,[女生] As Name',
    @Where = '[排名] between 500 and 2000', -- nvarchar(1024)
    @InsertTable = 'Excel_Table', -- nvarchar(512)
    @IsCreateScript = 1 -- bit
GO

另，我们在实际的环境中，应用可能有些不同，可根据实际来修改对应的位置。我这里不能保证它的功能是最好，只要它能解决实际问题，方便实用、提高工作效率就好。

 （完）