导入Excel到SQL错误 [英] import excel to sql error

问题描述

你好朋友
这是我的存储过程

Hi friends
This is my stored procedure

alter procedure sp_excelforgeneralholidays(@filname nvarchar(max))
 as
 declare @datavar nvarchar(max)
 declare @sql varchar(1000)
 set @datavar  = 'Excel 8.0;Database=' + @filname

 set nocount on
  begin
  --create table #temptable (Date date ,Day varchar(30),Reason varchar(100))
  set @sql = 'SELECT * FROM OPENROWSET(''Microsoft.jet.OLEDB.4.0'',''+@datavar+;HDR=YES'',''SELECT Date,Day,Reason FROM [Sheet1$]'')'

  exec (@sql)
 end

这是我存储过程的执行部分

This is my execution part of my stored procedure

exec sp_excelforgeneralholidays ''D:\testing''

这是错误

This is the error

OLE DB provider "Microsoft.jet.OLEDB.4.0" for linked server "(null)" returned message "Could not find installable ISAM.".
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "Microsoft.jet.OLEDB.4.0" for linked server "(null)".

请检查此存储过程.

please check this stored procedure.

推荐答案

'')' exec ( @ sql ) 结束

'')' exec (@sql) end

这是我存储过程的执行部分

This is my execution part of my stored procedure

exec sp_excelforgeneralholidays ''D:\testing''

这是错误

This is the error

OLE DB provider "Microsoft.jet.OLEDB.4.0" for linked server "(null)" returned message "Could not find installable ISAM.".
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "Microsoft.jet.OLEDB.4.0" for linked server "(null)".

请检查此存储过程.

please check this stored procedure.

我在错误消息中进行了搜索，并发现了此 [ ^ ]，有人说它也解决了这个问题.基本上，使用"Regsvr32 c:\ winnt \ system32 \ msexcl40.dll"(具有正确的dll路径)会重新注册dll并解决问题.

I googled the error message and found this[^], with people saying it solved this issue, too. Basically, using "Regsvr32 c:\winnt\system32\msexcl40.dll" (with the correct path to the dll) reregisters the dll and fixes the issue.

这篇关于导入Excel到SQL错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！