Geeks With Blogs
Srijith Sarman Time,space and living

God...It's been one year since I started neglecting  this blog. I must post something. Is there any point to wait for making a blog entry until you really invent something new? Then I may not make any post  at all .

This is a small tip which might be usefull for someone. I haven't tested the originality of this topic but this is the thing on which I wasted  some of my time on a precious sunday. I was just trying to load some excel files to their corresponding SQL Server database.

 Data entry? no way!.

I was kind of sure that I could use ADO.NET to do their job. I prefer this over other methods like DTS or SSIS as  both were not my option since I was not having the required installations on my machine. Wrote the code but when I started executing it I was  getting this error.

The 'Microsoft.Jet. OLEDB.4.0' provider is not registered on the local machine.

Hell, Iooking at my code I was not able to see a mistake.  I have seen many comments on net but thought of trying it later. First I need to get the job done as there are lot of alternative ways.

Another option I had in my mind was using linked server. It worked like charm.

I  did not try using enterprise manager and all. We can do the job simply by a query like this.

Insert into mydb.dbo.test
SELECT *  FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0',
'Data Source=test.xls;Extended Properties=Excel 8.0')...[test$]

To execute Ad Hoc Distributed Queries,you may have to configure your sql server accordingly.

The following snippet will do.

EXEC sp_configure 'show advanced options',1
GO
RECONFIGURE
GO
EXEC sp_configure 'Ad Hoc Distributed Queries', 1
GO
reconfigure

A thing to note here is,the excel file should be in the server machine. Otherwise you will get an error like this.

OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error. The provider did not give any information about the error.


Another way of achieving the same result is using OPENROWSET function

 

SELECT *  FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0''Excel 8.0;Database=C:\test.xls','SELECT * FROM [test$]')

Posted on Sunday, December 7, 2008 10:00 PM | Back to top


Comments on this post: A small issue: Loading Excel to Sql Server

No comments posted yet.
Your comment:
 (will show your gravatar)


Copyright © Srijith Sarman | Powered by: GeeksWithBlogs.net