Express Essentials: Excel Integration

SQL Server Express has a big advantage over rival “lite” databases: It boasts strong ties to Microsoft Office applications. One of the best examples of this relationship is found in Microsoft Excel 2007. Excel 2007 can seamlessly connect to SQL Server Express databases, pulling relational data out of SQL Server and into Excel for reporting or ad-hoc data analysis.

You can connect Excel 2007 to SQL Server Express using the Data tab on the Ribbon. When you open the Data tab, you’ll see a Get External Data section that contains the links From Access, From Web, From Text, or From Other Sources. To connect Excel 2007 to SQL Server Express you need to click the From Other Sources icon and then from the pop-up menu select the From SQL Server option. The fact that there is an option labeled From SQL Server, rather than some generic external database option, illustrates one aspect of the tight integration between Excel 2007 and SQL Server.

Selecting the From SQL Server option starts Excel 2007’s Data Connection Wizard. The first screen on the Data Connection Wizard prompts you for a SQL Server name and also for your log-on credentials. If you installed SQL Server Express using the default values, then you would enter the values of SQLEXPRESS and select the "Use Windows Authentication log-on" option and then click Next. This will display the "Select Database and Table dialog" where you use the dropdown menu to select the database that you want to connect to. For this example, select the Northwind sample database, and then choose the Current Product List table from the available list, and then click Next to display the "Save Data Connection File and Finish" dialog. The Data Connection Wizard will automatically create and name the connection, appending the extension of .odc to the file name. You can optionally add a description for the connection and a more user-friendly name. Clicking the Finish button displays the Import Data dialog, which allows you to select how you want the data to be imported into Excel: You can choose between Table, PivotTable Report and PivotChart, and PivotTable Report. The Table option populates the rows and columns of the worksheet beginning with the column that you specify. The PivotTable Report option displays the PivotTable builder, which lets you build a PivotTable by dragging and dropping columns from the Field List. Like its name implies, the PivotTable and PivotTable Report adds both a PivotTable Chart and the PivotTable to the worksheet.

Once you’ve built a data connection you can easily rerun it by selecting the Existing Connections option from the Ribbon’s Data tab. Excel 2007’s tight integration with SQL Server makes it very easy to access SQL Server Express data from Excel 2007.