Read XLS or MDB file with OpenDataSource - SQL Server 2005

Monday, March 2, 2009 |

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

3 comments:

Anonymous said...

Nice.........

Your small tips is very nice...but
this is a not foolproof solution agings hackers....

thanks...

take care..

vikram

Ritesh Shah said...

Hi Vikram,

Thanks for your comment, My pure intention was to give example of reading files right from SQL. As I have seen this question in few forums. As long as security concern than Its a two sided blade, one need to learn how to handle it. :)

Ritesh Shah

rafa said...

How can I accomplish the same within visual basic? I know other methods, but I need to open the MSAccess database.table this way in order to join it to another MSAccess database.table. I tried the following code that gives me a "sintax error in FROM clause":

myConnection.Execute("select * from OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0','Data Source= e:\local.mdb')...inventory"

I'm using visual basic 6.

Best regards.

Rafa.