在SQL Server中查询Excel数据(sp_OpenExcel)
开始:
在工作中经常要把Excel的数据转换成SQL导入脚本,这里写了一个存储过程sp_OpenExcel,实现查询Excel文件并生成Insert脚本。
sp_OpenExcel:
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
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:
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
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:
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
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:
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
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
另,我们在实际的环境中,应用可能有些不同,可根据实际来修改对应的位置。我这里不能保证它的功能是最好,只要它能解决实际问题,方便实用、提高工作效率就好。
(完)