baskaran chellasamy

baskaran chellasamy

  • NA
  • 114
  • 150.2k

Error in import excel to sql query

Aug 23 2012 2:09 AM
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


Answers (2)