Read XLS or MDB file with OpenDataSource - SQL Server 2005

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

Also create one MDB with Employee table and same three fields as above. After finishing above stuff, we will be moving to heart of this article. We will do three things in this article.

1.) We will read data from Excel file.

2.) We will read data from MDB file

3.) We will create temporary table in SQL-Server 2005 from Excel file’s table.

You can get result set from excel file with following query.

SELECT * FROM OPENDATASOURCE ('Microsoft.Jet.OLEDB.4.0', 'Data Source=\\192.168.1.1\tempexcel\Emp.xls;Extended Properties=Excel 8.0')...[Sheet1$]

You can use local path or UNC path as per your need. Above query will open Emp.XLS file and will get the result set available in Sheet1 of that excel file.

Now we are going to read MDB’s table from within SQL-Server with following query.

SELECT * FROM OPENDATASOURCE ('Microsoft.Jet.OLEDB.4.0', 'Data Source=\\192.168.1.1\tempMDB\Test012709.mdb')...[Employee]

Above query will open MDB file and read its Employee table and return the record set in SQL-Server.

Now at last, we will create one temporary table from Excel file.

SELECT * into #tblTempEmployee FROM OPENDATASOURCE ('Microsoft.Jet.OLEDB.4.0', 'Data Source=\\192.168.1.1\tempexcel\Emp.xls;Extended Properties=Excel 8.0')...[Sheet1$]

This will create temporary #tblTempEmployee table in your current database.

Note: Don’t forget to change path given in above examples.

Reference: Ritesh Shah