Read XLS or MDB file with OpenDataSource – SQL Server 2005
Recently, I had a task to transfer data from MDB file to SQL-Server 2005 so I googling little bit and found very good way of doing so. I liked the way and felt it cool so I am sharing with you guys. Before you start this article, Please see my previous article to enable “Ad hoc distributed queries” with sp_configure. Once you enable it. Please create one XLS file, named Emp.XLS, with three columns in “sheet1”.
ID
Name
Dept
This article is moved at following location. sorry for the incovenience.
http://blog.extreme-advice.com/2009/03/02/read-xls-or-mdb-file-with-opendatasource-sql-server-2005/
Ritesh's Blog for SQL-SERVER & .NET 
June 25, 2009 at 10:04 am
In Excel File reading it shows error near …[Sheet1$].
I think thr is wrong syntax. Please tell correct syntax.
June 25, 2009 at 10:10 am
what error are you facing? I just checked this and working absolutely fine.
SELECT * FROM
OPENDATASOURCE (’Microsoft.Jet.OLEDB.4.0′, ‘Data Source=C:\email.xls;Extended Properties=Excel 8.0′)…[Sheet1$]
do remember, you have to have three dots before [sheet1$]
June 25, 2009 at 10:39 am
Oh Thanks Ritesh, Its working fine.
thanks
June 25, 2009 at 10:11 am
please put your further comment at
http://www.sqlhub.com/2009/03/read-xls-or-mdb-file-with.html
as SQLHub is my permenent site and I used to support there most of the time.
June 25, 2009 at 10:10 am
what error are you facing? I just checked this and working absolutely fine.
SELECT * FROM
OPENDATASOURCE (‘Microsoft.Jet.OLEDB.4.0’, ‘Data Source=C:\email.xls;Extended Properties=Excel 8.0’)…[Sheet1$]
do remember, you have to have three dots before [sheet1$]
October 15, 2009 at 9:15 pm
Hi,
I am getting this:
“Msg 7403, Level 16, State 1, Line 1
The OLE DB provider “Microsoft.Jet.OLEDB.4.0″ has not been registered.”
where do I register it?
NR
June 14, 2010 at 4:43 am
Great post.. thanks for sharing 😀
May 16, 2011 at 3:27 pm
It is great post for me. i solve my problem with this reply. but still i am confuse about how to read data from another sql server database using select command.
Thanks in advance.
May 16, 2011 at 3:44 pm
if you have created linked server than you can use four part naming convention for each object from one server to another….
May 16, 2011 at 3:44 pm
if you have created linked server than you can use four part naming convention for each object from one server to another….
May 18, 2011 at 4:39 pm
Yes I create a new linked server name is “Dynamic”. And I have sql file name Company.mdf it is located on my local drive F:\Delta Folder. so what can i do for further? I hope you have any solution for me.
Thank you again Ritesh
November 13, 2012 at 10:55 pm
SELECT *
from
OPENDATASOURCE (‘Microsoft.Jet.OLEDB.4.0’ ,
‘Data Source=C:\COS-20121108-20121108.mdb;User ID=Admin;Password=’).. qbcsa.empcar
ME SALE ERROR
El proveedor OLE DB “Microsoft.Jet.OLEDB.4.0” para el servidor vinculado “(null)” devolvió el mensaje “Error no especificado”.
Mens. 7303, Nivel 16, Estado 1, Línea 1
No se puede inicializar el objeto de origen de datos del proveedor OLE DB “Microsoft.Jet.OLEDB.4.0” para el servidor vinculado “(null)”.
February 1, 2013 at 10:25 am
This is a topic that is close to my heart… Best wishes!
Exactly where are your contact details though?