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

Advertisements

6 Responses to “Read XLS or MDB file with OpenDataSource – SQL Server 2005”

  1. Anonymous Says:

    Nice………Your small tips is very nice…butthis is a not foolproof solution agings hackers….thanks…take care..vikram

  2. Anonymous Says:

    Nice………Your small tips is very nice…butthis is a not foolproof solution agings hackers….thanks…take care..vikram

  3. Ritesh Shah Says:

    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

  4. Ritesh Shah Says:

    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

  5. rafa Says:

    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.

  6. rafa Says:

    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.


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: