import excel to sql error

Question

0.00/5 (No votes)

See more:

Hi friends
This is my stored procedure

SQL

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.

Posted 24-Aug-12 3:39am

baskaran chellasamy

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Christian Graus · Answer 1 · 2012-08-24T03:54:00

Solution 1

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.

Posted 24-Aug-12 3:54am

Christian Graus

Comments

[no name] 24-Aug-12 10:03am

What a wonderous thing this "google" must be. Someone should write an article.... :-)

Christian Graus 24-Aug-12 10:05am

You know, I think someone has.... :P

baskaran chellasamy 24-Aug-12 10:18am

I check with this tutorial.And i found there is msexcl40.dll on two location.
1.C:\Windows\System32 The exact place to for this dll
2.C:\Windows\winsxs\x86_microsoft-windows-m..components-jetexcel_31bf3856ad364e35_6.1.7600.16385_none_1de500b1a390aab9

Is any problem will occur ragarding with these two location
thanks for advance

Christian Graus 24-Aug-12 10:21am

My guess is that this means the issue is that you don't have Excel installed

baskaran chellasamy 24-Aug-12 10:35am

sorry friends this is working.
<pre>create procedure sp_exceltest
as
set nocount on
declare @sql varchar(2000)
begin
set @sql='select * from OPENROWSET(''Microsoft.jet.OLEDB.4.0'',''Excel 8.0;Database=D:\testing.xls;HDR=YES'',''select * from [Sheet1$]'')'
exec(@sql)
end</pre>
But the above is not working.please find if i made with any syntax error

Christian Graus 24-Aug-12 10:40am

The error is in @datavar, actually I think your error may be in how that whole line is formatted. I think @datavar is being passed as a string, not evaluated. Try this: do select @sql instead of exec @sql and check the return value to see what your string is. I bet it's broken

baskaran chellasamy 24-Aug-12 10:47am

SELECT * FROM OPENROWSET('Microsoft.jet.OLEDB.4.0','+@datavar+;HDR=YES','SELECT Date,Day,Reason FROM [Sheet1$]')
This is the string.

Christian Graus 24-Aug-12 10:49am

Notice anything ? Like I said, you're not added @datavar, you're just passing it. So, fix the code there and you will be fine. You have quotes missing. set @sql = 'SELECT * FROM OPENROWSET(''Microsoft.jet.OLEDB.4.0'','''+@datavar+';HDR=YES'',''SELECT Date,Day,Reason FROM [Sheet1$]'')'

baskaran chellasamy 24-Aug-12 11:01am

thank you very much.
it took hours to solve this the problem
now its working.

Christian Graus 24-Aug-12 11:10am

Glad to help.