Sql 导入 EXCEL 通用存储过程

Sql 导入 EXCEL 通用存储过程

/*

--下面两句在创建存储过程时必须先执行,否则不能创建本存储过程,或者调用出错

SET ANSI_WARNINGS ON--返回警告

GO

SET ANSI_NULLS ON-- 指定在对空值使用等于   (=)   和不等于   (<>)   比较运算符时，这些运算符的   SQL-92   遵从行为。

GO

--将Excel数据导入到sql数据库

--版权 : 启程 letwego.cn

--调用实例

EXEC spExcelOutIn

@strOptions = 'In',

@strWhere = 'WHERE 房号>0',--导入/导出查询条件(包括Where 关键字)

@strExcelPath = 'd:/test.xls',--Excel的绝对路径

@strExcelSheetName = 'Sheet2',--Excel里要导入的工作表名称 如Sheet2,注意,后面不要加上$

@strExcelFiled = '单元编号,单元名称,房号,建筑面积,楼层,楼层名称,楼阁编号,业主',--Excel工作表的字段

 

@strSqlTableName = 'TExcelOutIn',--Sql数据库导入表名

@strSqlFiled = '单元编号,单元名称,房号编号,建筑面积,楼层,楼层名称,楼阁编号,业主编号'--Sql表的字段

*/

 

 

CREATE PROCEDURE spExcelOutIn

 

@strOptions varchar(200) = NULL,

@strWhere nvarchar(4000) = NULL,--导入/导出查询条件(包括Where 关键字)

 

@strExcelPath nvarchar(1000) = NULL,--Excel的绝对路径

@strExcelSheetName nvarchar(50) = NULL,--Excel里要导入的工作表名称 如Sheet2,注意,后面不要加上$

@strExcelFiled nvarchar(1000) = NULL,--Excel工作表的字段

 

@strSqlTableName nvarchar(100) = NULL,--Sql数据库导入/导出表名

@strSqlFiled nvarchar(1000) = NULL--Sql表的字段

 

AS

 

DECLARE @strSql nvarchar(4000)

 

IF OBJECT_ID('tempdb..##Temp') IS NOT NULL DROP TABLE ##Temp

 

 

/* 导入 */

IF @strOptions='In' AND NOT @strExcelPath IS NULL BEGIN

 

SET @strExcelPath = '''Microsoft.Jet.OLEDB.4.0'',''Data Source="' + @strExcelPath + '";User ID=Admin;Password=;Extended properties=Excel 5.0'''

 

--将数据存放到临时表(一)

SET @strSql =

'SELECT ' + @strExcelFiled +

' INTO ##Temp

FROM

OpenDataSource('+ @strExcelPath +')...'+ @strExcelSheetName +'$ '

+@strWhere

 

--PRINT(@strSql)

EXEC(@strSql)

 

--将数据从临时表导入到sql数据库表(二,分两步可以做更多的处理)

SET @strSql = 'INSERT INTO ' + @strSqlTableName + '('+ @strSqlFiled +') SELECT * FROM ##Temp'

--PRINT(@strSql)

EXEC(@strSql)

 

--SELECT * FROM ##Temp

DROP TABLE ##Temp

 

END

 

/* 导出 */

IF @strOptions='Out' AND NOT @strExcelPath IS NULL BEGIN

 

SET @strExcelPath = ''

--导成类似（不是真正的Excel，是文本格式）Excel的文件,这里是固定的，真正用时需要修改相关参数

--EXEC master..xp_cmdshell 'bcp "SELECT * FROM wy_福州分公司.dbo.TExcelOutIn" queryout C:/test.xls -c -S"(local)" -U"sa" -P"123"'

END

GO

 

------------------------------------分割线---------------------------------------------

 
 

  
  create
  
   
  
  procedure
  
   import 
( 

  
  @tablename
  
   
  
  varchar
  
  (
  
  100
  
  ), 

  
  @filepath
  
   
  
  varchar
  
  (
  
  100
  
  ) 
) 

  
  as
  
   

  
  declare
  
   
  
  @s
  
   
  
  varchar
  
  (
  
  8000
  
  )

  
  set
  
   
  
  @s
  
   
  
  =
  
  '
  
  INSERT INTO
  
  '
  
   
  
  +
  
   
  
  @tablename
  
  +
  
  '
  
   SELECT * FROM OPENROWSET 
(
  
  ''
  
  MICROSOFT.JET.OLEDB.4.0
  
  ''
  
  ,
  
  ''
  
  Excel 8.0; HDR=YES; Database =
  
  '
  
  +
  
  @filepath
  
  +
  
  '
  
  ,sheet1$)
  
  '
  
  

  
  exec
  
  (
  
  @s
  
  )