使用存储过程将Excel导出到SQL表(Excel名称作为参数) [英] Export Excel into SQL table using Store procedure (Excel Name as Parameter)
问题描述
使用ExcelName作为参数
Use ExcelName as Parameter
create proc Excel
(
@ExcelName varchar(100)
)
As
Begin
if OBJECT_ID('temp') is not null
drop table temp
select * INTO Excel from openrowset ('Microsoft.ACE.OLEDB.12.0','Excel 12.0;Database=d:\Sharedd\'''+@ExcelName+''.XLSX,[Sheet1$])
End
或
使用动态查询。我得到如下错误。
如何解决错误:
(
消息7399,16级,状态1,行1
链接服务器(null)的OLE DB提供程序Microsoft.ACE.OLEDB.12.0报告错误。提供商没有提供有关错误的任何信息。
消息7303,级别16,状态1,行1
无法初始化OLE DB提供程序的数据源对象Microsoft .ACE.OLEDB.12.0for linked server(null)。
)在Srcipt下面
or
use dynamic query.I got error as below.
How to Solve the Error:
(
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" reported an error. The provider did not give any information about the error.
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)".
) in Below Srcipt
--Exec SP_Excel 'File7.XLSB'
Alter PROC SP_Excel
(@ExcelName VARCHAR(100)
)
As
Begin
Declare @SQL VARCHAR(1000)
if OBJECT_ID('Table1') is not null
drop table Table1
SET @SQL= 'select * INTO Table1 from openrowset (''Microsoft.ACE.OLEDB.12.0'',''Excel 12.0;Database=d:\Shared\EXCEL\'+@ExcelName+''',[Details$])'
EXEC(@SQL)
End
推荐答案
)
结束
或
使用动态查询。我收到如下错误。
如何解决错误:
(
消息73 99,级别16,状态1,行1
链接服务器(null)的OLE DB提供程序Microsoft.ACE.OLEDB.12.0报告错误。提供商没有提供有关错误的任何信息。
消息7303,级别16,状态1,行1
无法初始化OLE DB提供程序的数据源对象Microsoft .ACE.OLEDB.12.0for linked server(null)。
)在Srcipt下面
or
use dynamic query.I got error as below.
How to Solve the Error:
(
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" reported an error. The provider did not give any information about the error.
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)".
) in Below Srcipt
--Exec SP_Excel 'File7.XLSB'
Alter PROC SP_Excel
(@ExcelName VARCHAR(100)
)
As
Begin
Declare @SQL VARCHAR(1000)
if OBJECT_ID('Table1') is not null
drop table Table1
SET @SQL= 'select * INTO Table1 from openrowset (''Microsoft.ACE.OLEDB.12.0'',''Excel 12.0;Database=d:\Shared\EXCEL\'+@ExcelName+''',[Details
)'
EXEC ( @ SQL )
结束
在这两个示例中,您将删除稍后尝试插入数据的表。
INSERT [ ^ ]声明错误。
In both examples you delete table in which later you''re trying to insert data.
INSERT[^] statement is wrong.
Alter PROC SP_Excel
@ExcelName VARCHAR(255),
@SheetName VARCHAR(50)
AS
BEGIN
DECLARE @fExt VARCHAR(3)
SET @fExt = RIGHT(@ExcelName, 4)
IF (@fExt = '.xls')
BEGIN
INSERT INTO Table1 SELECT * FROM OPENROWSET ('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=D:\Shared\EXCEL\' + @ExcelName + '', 'SELECT * FROM [' + @SheetName +'
这篇关于使用存储过程将Excel导出到SQL表(Excel名称作为参数)的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!