Recently, I had replied to post on the #sqlhelp Twitter hashtag regarding configuring a Linked Server to an Excel file using the GUI in Management Studio. This is very much possible. I use a linked server to pull data from .xls file on a 64-bit SQL Server instance for an application that I maintain.
Using the 64-bit ACE provider, you can now do this. The data source which is masked in the above picture is the location of the Excel file with the full file path.
Once you have this configured, you can access the Linked Server catalogs by expanding the Linked Server in Object Explorer. Each table listed in the catalog is actually an Excel sheet.
This is fairly simple task but since this isn’t an explicit example out there for this, I thought I would do a quick post on the same.
Addedum: April 4th, 2010. After my colleague, Evan pointed out the server side support policy for ACE.
Disclaimer: The ACE redistributable link does mention the following:
The Access Database Engine 2010 Redistributable is not intended:
- As a replacement for the Jet OLEDB Provider in server-side applications.
- To be used within a service program or web application that relies on a Windows service.