Script to import excel to sqlserver table

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)

Script to import excel to sqlserver table

Author

Topic

snow12
Yak Posting Veteran

74 Posts

Posted - 2011-12-12 : 17:57:56

Hello:

I have the trouble to write the script to import excel to sqlserver table.

Is there any script to import excel to sqlserver table without using linked server?

Thank you very much

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-12-12 : 18:00:45

There are lots of ways. I typically use bcp if it's in csv format (which Excel supports). You can also use openquery, SSIS, and the import/export wizard.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

snow12
Yak Posting Veteran

74 Posts

Posted - 2011-12-13 : 11:41:15

Thanks for the reply.

Do you have the script to use openrowset or bcp to import excel file? The excel file is not csv format.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-13 : 11:45:07

see

http://support.microsoft.com/kb/321686

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

snow12
Yak Posting Veteran

74 Posts

Posted - 2011-12-13 : 12:12:39

Thanks for the quick response.

I used this script:

Insert into Table Select * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=\\drsql12\School\12122011\WEEKLY_19115_12122011.xls;HDR=YES',
'SELECT * FROM [WEEKLY_19115_12122011$]')

However, I got 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)".

Do you know where I missed? Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-13 : 12:16:03

is the path \\drsql12\School\12122011\WEEKLY_19115_12122011.xls accessible? Is the excel file kept open?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

snow12
Yak Posting Veteran

74 Posts

Posted - 2011-12-13 : 12:54:47

Thanks for the reply.

Sorry, I type the wrong path. I correct it, but I got the another error:

Msg 7357, Level 16, State 2, Line 1
Cannot process the object "SELECT * FROM [WEEKLY_19115_12122011$]". The OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)" indicates that either the object has no columns or the current user does not have permissions on that object.

There is data on the excel file. The excel file is accessible. What is mean not have permissions on that object?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-13 : 13:32:55

does sheet have any data?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-12-13 : 14:06:03

quote:
Originally posted by visakh16

does sheet have any data?

Yes, see the OP's last reply:

quote:
Originally posted by snow12

There is data on the excel file. The excel file is accessible.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

snow12
Yak Posting Veteran

74 Posts

Posted - 2011-12-13 : 16:34:42

Is anyone could help me out? Thanks

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-12-13 : 16:49:56

Try copying the file locally and using the local file in your openrowset. Let us know how that goes.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

snow12
Yak Posting Veteran

74 Posts

Posted - 2011-12-14 : 15:36:30

Dear Tara:

Thank you very much. I put the sheet name wrong.

Insert into Table Select * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=\\drsql12\School\12122011\WEEKLY_19115_12122011.xls;HDR=YES',
'SELECT * FROM [Sheet1$]')
It works now. I am trying change it to use parameter to call procedure.

CREATE PROCEDURE sp_Test
-- Add the parameters for the stored procedure here
AS
BEGIN
declare @File varchar(100)
-- Insert statements for procedure here

Insert into Table Select * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0' ,'Excel 8.0;Database=@File;HDR=YES',
'SELECT * FROM [Sheet1$]')
END
GO

However, I got the error:

OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)" returned message "The Microsoft Jet database engine could not find the object 'Sheet1$'. Make sure the object exists and that you spell its name and the path name correctly.".
Msg 7350, Level 16, State 2, Procedure sp_Rengen_Load, Line 16
Cannot get the column information from OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)".

Do you know where I missed again?

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-15 : 00:11:17

i think you need to use dynamic sql if you want to pass path thorugh @file variable

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

snow12
Yak Posting Veteran

74 Posts

Posted - 2011-12-15 : 12:04:14

Thanks for the response.

Do you have the example code for using dynamic sql to use here?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-15 : 12:17:45

something like


...
EXEC ('Insert into Table Select * FROM OPENROWSET(''Microsoft.Jet.OLEDB.4.0'' ,''Excel 8.0;Database='+ @File + ';HDR=YES'',
''SELECT * FROM [Sheet1$]'')')

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

snow12
Yak Posting Veteran

74 Posts

Posted - 2011-12-15 : 12:57:55

Thanks for quick response. I think that I am getting close now.

How to call the procedure if putting exec in the procedure .

Here is my procedure:

Create PROCEDURE sp_Load
	-- Add the parameters for the stored procedure here
AS
BEGIN
	
	SET NOCOUNT ON;
declare @File varchar(100)
   

EXEC ('Insert into Table Select * FROM OPENROWSET(''Microsoft.Jet.OLEDB.4.0'' ,''Excel 8.0;Database='+ @File + ';HDR=YES'',
''SELECT * FROM [Sheet1$]'')')


END
GO

when I try to call this procedure.

exec sp__Load
@File = '\\drsql12\School\12122011\WEEKLY_19115_12122011.xls'

I got the error:

Msg 8146, Level 16, State 2, Procedure sp_Load, Line 0
Procedure sp_Load has no parameters and arguments were supplied.

X002548
Not Just a Number

15586 Posts

Posted - 2011-12-15 : 13:20:03

[code]
Create PROCEDURE sp_Load
@File varchar(8000)
AS
BEGIN

SET NOCOUNT ON;
declare @sql varchar(8000)

SET @sql = 'Insert into Table Select * FROM OPENROWSET(''Microsoft.Jet.OLEDB.4.0'' ,''Excel 8.0;Database='+ @File + ';HDR=YES'',
''SELECT * FROM [Sheet1$]'')'

PRINT @SQL

EXEC (@SQL)

END
GO
[/code]

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

snow12
Yak Posting Veteran

74 Posts

Posted - 2011-12-15 : 16:10:27

Brett;

You are big helper! Thank you very much!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources