Doubt in Importing data from Excel to sql 2005

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

SQL Server 2005 Forums

Transact-SQL (2005)

Doubt in Importing data from Excel to sql 2005

Author

Topic

ganeshkumar08
Posting Yak Master

187 Posts

Posted - 2010-04-13 : 02:28:53

Hi,
This is my SP
-- sp_ImportFromExcel03 'Sheet1','D:\testing.xls','YES','Test'
ALTER PROCEDURE [dbo].[sp_ImportFromExcel03]
@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.Jet.OLEDB.4.0'',''Data Source='
SET @SQL = @SQL + @FilePath + ';Extended Properties=''''Excel 8.0;HDR='
SET @SQL = @SQL + @HDR + ''''''')...['
SET @SQL = @SQL + @SheetName + ']'
EXEC sp_executesql @SQL
-- Print @SQL
END

After executing
sp_ImportFromExcel03 'Sheet1','D:\testing.xls','YES','Test'

I am getting below error.

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)".

How can i resolve it.

Thanks
Ganesh

Solutions are easy. Understanding the problem, now, that's the hard part

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-04-13 : 02:59:37

More trobleshooting
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=49926

Madhivanan

Failing to plan is Planning to fail

Sachin.Nand

2937 Posts

Posted - 2010-04-13 : 03:12:14

quote:
Originally posted by madhivanan

More trobleshooting
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=49926

Madhivanan

Failing to plan is Planning to fail

Wow thats a hell lot of troubleshooting of 25 pages.

PBUH

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-04-13 : 04:58:24

quote:
Originally posted by Idera

quote:
Originally posted by madhivanan

More trobleshooting
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=49926

Madhivanan

Failing to plan is Planning to fail

Wow thats a hell lot of troubleshooting of 25 pages.

PBUH

People have different types of problems when trying to exporting to EXCEL. That link has informations to almost all the problems

Madhivanan

Failing to plan is Planning to fail

ganeshkumar08
Posting Yak Master

187 Posts

Posted - 2010-04-13 : 05:11:12

Hi Madhivanan,
I didnt get any clarification from
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=49926

Solutions are easy. Understanding the problem, now, that's the hard part

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-04-13 : 07:02:48

What does this return?

Print @SQL

Madhivanan

Failing to plan is Planning to fail

ganeshkumar08
Posting Yak Master

187 Posts

Posted - 2010-04-14 : 02:48:49

Print @SQL will return

INSERT INTO Test SELECT * FROM
OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0','Data Source=D:\testing.xls;Extended Properties=''Excel 8.0;HDR=YES''')...[Sheet1]

Solutions are easy. Understanding the problem, now, that's the hard part

ganeshkumar08
Posting Yak Master

187 Posts

Posted - 2010-04-14 : 07:17:57

I got solution.

We need to put the excel file in one folder. We should not save or copy excel file in Hard Drives(C:\, D:\,....)

we have to save as DriveName:\FolderName\ExcelFileName.xls
i.e, c:\TestFolder\Test.xls

Solutions are easy. Understanding the problem, now, that's the hard part

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-04-15 : 02:36:43

quote:
Originally posted by ganeshkumar08

I got solution.

We need to put the excel file in one folder. We should not save or copy excel file in Hard Drives(C:\, D:\,....)

we have to save as DriveName:\FolderName\ExcelFileName.xls
i.e, c:\TestFolder\Test.xls

Solutions are easy. Understanding the problem, now, that's the hard part

Ok. Thanks for the feedback

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources