DiigIT | IT Community
No Profile Image
Welcome Guest
New User? Register | Login
SQL
IT Tags
DiigIT » SQL » qna

Excel to SQL server DB

By: rekha singh | 10 May 2010 5:57 pm

 Hi

I want to exprot the data from excel sheet to database table ....
 and i have this code which does it but its giving error

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE PROCEDURE spx_ImportFromExcel 03

@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

END

GO

and when i execute

Exec spx_ImportFromExcel 03 'Sheet1','C: \Documents and
Settings\Avinash\ Desktop\TestUplo ad.xls',' Yes','CaseList'

It gives error saying

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

am i doing anything wrong in this?????? please let me know..

--
Thanks & Regards

Comments

 Where is @FilePath given a value?

By: rekha singh | 10 May 2010
Check this
 
Exec spx_ImportFromExcel 03 'Sheet1','C: \Documents and
Settings\Avinash\ Desktop\TestUplo ad.xls',' Yes','CaseList'
 

Regards 

By: rekha singh | 10 May 2010

I've never tried to do this, but I would make sure that SQL Server can find the file and has permission to open the file in that folder. 

By: rekha singh | 10 May 2010
By: rekha singh | 10 May 2010
Does SQL Server and it would appear IIS have permission to the folder? Is Jet installed on the server? 
By: rekha singh | 10 May 2010

yes Installed... 

By: rekha singh | 10 May 2010

Permissions? 

By: rekha singh | 10 May 2010

Hey  i got it done the code works fine and there was one error while executing the Procedure

Exec spx_ImportFromExcel 03 'Sheet1*$*', 'C:\Documents and
Settings\Avinash\ Desktop\TestUplo ad.xls',' Yes','CaseList'

I missed that Dollar symbol there it gave error :)
 

By: rekha singh | 10 May 2010

Leave a comment

Enter the text in the image
img
Can't read?
Type the characters you see in the picture below.


Close Move