Import Excel sheet - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Import Excel sheet

Author

Topic

yaman
Posting Yak Master

213 Posts

Posted - 2009-09-08 : 07:30:15

Hello Sir ,

I am trying import data excel sheet into My database .
It return 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)".
"

My Stp is :-

CREATE PROCEDURE spx_ImportFromExcel07
@SheetName varchar(20),
@FilePath varchar(100),
@HDR varchar(3),
@TableName varchar(50)

AS

BEGIN

DECLARE @SQL nvarchar(1000)
IF OBJECT_ID (@TableName,'U') IS NOT NULL

SET @SQL = 'INSERT INTO ' + @TableName + ' SELECT * FROM OPENDATASOURCE'

ELSE

SET @SQL = 'SELECT * INTO ' + @TableName + ' FROM OPENDATASOURCE'

SET @SQL = @SQL + '(''Microsoft.ACE.OLEDB.12.0'',''Data Source='

SET @SQL = @SQL + @FilePath + ';Extended Properties=''''Excel 12.0;HDR='

SET @SQL = @SQL + @HDR + ''''''')...['

SET @SQL = @SQL + @SheetName + ']'

EXEC sp_executesql @SQL

END

Please help me out sir .

Yaman

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2009-09-08 : 08:24:37

What does this output? Is it correct?


CREATE PROCEDURE spx_ImportFromExcel07 
@SheetName varchar(20), 
@FilePath varchar(100), 
@HDR varchar(3), 
@TableName varchar(50) 

AS 

BEGIN 

DECLARE @SQL nvarchar(1000) 
IF OBJECT_ID (@TableName,'U') IS NOT NULL 

SET @SQL = 'INSERT INTO ' + @TableName + ' SELECT * FROM OPENDATASOURCE' 

ELSE 

SET @SQL = 'SELECT * INTO ' + @TableName + ' FROM OPENDATASOURCE' 



SET @SQL = @SQL + '(''Microsoft.ACE.OLEDB.12.0'',''Data Source=' 

SET @SQL = @SQL + @FilePath + ';Extended Properties=''''Excel 12.0;HDR=' 

SET @SQL = @SQL + @HDR + ''''''')...[' 

SET @SQL = @SQL + @SheetName + ']' 

select @SQL 

END

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources