Many organizations use SQL Server to handle their database needs but find at least some of their essential business data is currently hiding inside Excel spreadsheets. It’s a common problem; departments want their information where it’s easy to create and maintain it, IT folks want data to be centrally maintained where it can all be accessed and backed up.
Thankfully it appears that Microsoft has learned of these cases and worked up a solution, because it is actually possible to use an Excel sheet as an SQL Server database table, acting somewhat like a kind of attached database.
A Working Example
Let’s use, as an imaginary example, calculating the interest rate of a loan, which (presumably) depends on a lot of factors, some of which change daily, and some depend on the person requesting the loan.
Our imaginary firm’s actuary specialist has created a very complicated Excel workbook that does all the work, and he can then consult the final spreadsheet in which there’s a table with the load rate as a factor of the person’s age and income level.
We want to connect it to the in-house SQL Server and use it as the load rate table. Here’s an example we created if you want to follow along.
Adding the Workbook as an External Server
We’ll use the easiest method of attaching the file as a data source; you can check out more ways to do so in this article at the MSDN site.
To add this file as a data source to SQL Server, we’ll fire up the SQL Server Management Studio:
-
- Open the ‘Server Objects’ node and select ‘Linked Servers’
- Right-click the ‘Linked Server’ node and select ‘New Linked Server…’
-
- Fill in the New Linked Server dialog. Put in the same values as in the image, except for the Linked Server box (which will be the name of the ‘database’ later), and the Data Source box, which should contain a full path to the Excel file:
- Click OK
That’s it! Opening the ‘Linked Servers’ node, you can see the EXCEL_RATE database there, with the three ‘tables’ (actually, worksheets):
Querying Your Information
Now we can easily access the information using regular SQL queries. The name of the table is comprised of the server name – the one we gave it when attaching it – then three periods, the sheet name, and a dollar sign.
In our sample, the table name is EXCEL_RATE…Rate$ ; here’s the result of a query to return all the values in the table:
Warning
One caveat is that changing a value in the Excel sheet will not perform re-calculations unless the Excel file is opened and saved in Excel. Also note that you cannot use the table while the Excel file is opened in Excel (unless it’s in Read-Only mode).
Summary
Using this technique can be a great way of re-using existing business data contained in Excel. It allows staff and colleagues to use an inputting interface they are familiar with, and all the “what-if” and analysis tools that Excel provides, while still integrating that data in the corporate databases.
That said it is best suited for scenarios where you need to retrieve existing values as it is problematic if you want to use your Excel formulas to recalculate new information that is added or updated by the SQL Server.
P.S.
Don’t forget to check out our PDF To Excel Converter. It can save you a lot of precious time you now spend on retyping PDF data.
