Using Excel to import the contents of a SQL Server database table may not seem like the most logical method for reporting on the contents of a table. In most cases, you’ll likely just run a
SELECT * against the table in question and copy/paste the contents into an Excel document when needed. However, there are cases where this might be a good option. For one, if the table is quite large and consist of tens of thousands or more rows, it may be easier to simply have Excel populate the spreadsheet with the data instead of running the query and copy/pasting the data.
Another reason this route might be a good option is if an end user is routinely wanting a data dump of an entire table. Once a connection is configured within Excel it is saved and can be quickly and easily utilized to retrieve the data in the table whenever needed. Though the same results could be accomplished by creating an SSRS report, it’s good to have several options available when trying to determine the quickest way to get data to the user.
The steps outlined below will walk you through setting up a SQL Server connection within Excel and importing the contents of a database table into a spreadsheet.
The first step is it open a new Excel worksheet and navigate to the Data tab. Once on the Data tab click the button labeled From Other Sources. The drop-down will list a variety of sources that can be selected for importing data into Excel. In this case I am wanting to import data directly from a SQL Server table so I will select the option highlighted below, From SQL Server.
The Data Connection Wizard window will be displayed. Here is where I will specify the SQL Server as well as the log on credentials. In the example below I am connecting to
lab-sql2012 and using Windows Authentication. If I were configuring this data connection for an end user who’s credentials did not have sufficient access to connect to the database, the best option would be to create a service account in SQL Server, assign the necessary permissions and then specify the service account credentials by selecting Use the following User Name and Password.
Once the server name and log on credentials have been specified, click Next.
The next screen is where I will specify the database and table or tables from which I will export the data. In the screenshot below I have selected the
TSQL2012 database and the
HR.Employees table. There is also the option to select multiple tables to export by clicking the check box next to Enable selection of multiple tables.
Once all tables have been selected click Next.
The next window allows me to save the data connection. This is important as it will save the connection within Excel and allow me to reuse the connection in the future if needed. Though I usually keep the default File Name and Friendly Name , this can be changed to more logically describe the connection if desired. The Friendly Name is what will appear in the Existing Connections drop-down on the Data tab in Excel.
There is also an option to Always attempt to use this file to refresh data. Though not checked in the screenshot below, checking this will ensure that any updates to the connection file will always be used by all workbooks that use the connection file.
Once all desired changes have been made click Finish to finalize the connection setup.
The final screen is where I will configure how the data is going to be displayed in the Excel workbook. In this case I simply want to export the data as a table. Since I only selected a single table to export I can leave the default location as Existing worksheet. By default, whichever cell was highlighted when the Data Connection Wizard was launched will be displayed. In this case it was cell
A1. If I were to select New worksheet, Excel would ignore the existing worksheet and create a new worksheet for the data.
If I had selected multiple tables to export and then selected New worksheet, a separate worksheet would be created for each table export. This is important to know as it makes organizing the data by table much easier when exporting multiple tables.
Finally, when you’re ready to import the data into the Excel workbook click OK.
The screenshot below illustrates the data that was imported into Excel from the
TSQL2012.HR.Employees table. The full contents of the table were imported and each column header matches the table column header in the database.
As mentioned above, there are other suitable methods for getting data out of SQL Server and into an Excel worksheet. Though there are limited applications for the method outlined above, this method does provide a secure way to allow end users to import the contents of a database table directly into Excel without the need for IT intervention. That alone is a good reason to have this option in your arsenal.