Error in Import excel file to sql server

Question

0.00/5 (No votes)

See more:

Hi friends
This is my query for getting dyanamic excel file to sql

create procedure sp_excelforgeneralholidays(@filname nvarchar(max))
 as 
 declare @datavar varchar(200)
 declare @sql varchar(500)
 set @datavar  = 'Excel 8.0;DATABASE=' + @filname
 
 set nocount on
  begin
  set @sql = 'SELECT * FROM OPENROWSET(''Microsoft.jet.OLEDB.4.0'','''+@datavar +''', ''SELECT Data,Day,Reason FROM [Sheet1$]'')'
  exec (@sql)
 end

and This is the execution process

exec sp_excelforgeneralholidays 'D:\baskaran\\baskarbook1'

this is my excel file

Date Day Reason
26-Jan Thursday Republic Day
5-Feb Sunday Milad-Un-Nabi ( Id-E-Milad )
(Birthday of Prophet Mohammad)
20-Feb Monday Maha Shivratri
8-Mar Thursday Holi
5-Apr Thursday Mahavir Jayanthi
6-Apr Friday Good Friday
1-May Tuesday May Day
6-May Sunday Buddha Purnima
10-Aug Friday Krishna Janmastami
15-Aug Wednesday Independence Day
20-Aug Monday Id-Ul-Fitr
19-Sep Wednesday Ganesh Chaturthi
2-Oct Tuesday Mahatma Gandhi Jayanthi
24-Oct Wednesday Vijaya Dashami
My table field is

Holidayid    Date    Day    Reason    createddatetime    updateddatetime    adminid

The error is

Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "Microsoft.jet.OLEDB.4.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.jet.OLEDB.4.0" for linked server "(null)".

i dont know how to solve this error.please help me

Posted 22-Aug-12 20:14pm

baskaran chellasamy

Add a Solution

2 solutions

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)

Prasad_Kulkarni · Answer 1 · 2012-08-22T20:35:00

Solution 1

Please refer some similar QA:
Import excel data[^]
Linked Server using Microsoft.Jet.OLEDB.4.0 problem[^]
Linked Server using Microsoft.Jet.OLEDB.4.0 problem [^]

Posted 22-Aug-12 20:35pm

Prasad_Kulkarni

Comments

baskaran chellasamy 23-Aug-12 2:56am

Is this my query is right.
<pre>create procedure sp_excelforgeneralholidays(@filname nvarchar(max))
as
declare @datavar varchar(200)
declare @sql varchar(500)
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 * into #temptable FROM OPENROWSET(''Microsoft.jet.OLEDB.4.0'','''+@datavar +''',''SELECT Data,Day,Reason FROM [Sheet1$]'')'
exec (@sql)
end</pre>

Excution part is
exec sp_excelforgeneralholidays'D:\baskaran\baskarbook1'
This baskarbook1 contain single excel sheet.

M.Sravan Kumar · Answer 2 · 2012-08-22T20:59:00

Solution 2

hi,
i hope below link helpful for u
http://dotnetslackers.com/articles/sql/Importing-MS-Excel-data-to-SQL-Server-2008.aspx[^]

Posted 22-Aug-12 20:59pm

M.Sravan Kumar

Comments

baskaran chellasamy 23-Aug-12 3:51am

I want to right in coding. and please tell me what the error in my query that was updated from above.
<pre>
alter procedure sp_excelforgeneralholidays(@filname nvarchar(max))
as
declare @datavar varchar(200)
declare @sql varchar(500)
set @datavar = 'Excel 8.0;DATABASE=' + @filname

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

exec (@sql)
end
</pre>
and the error is 'incorrect syntax near insert'