Using and Reusing Data Connections
Opening an Entire Access Table in Excel
Working with Data in Text Files
Using Microsoft Query to Import Data
Using a Web Query to Return Internet Data
MICROSOFT Excel 2010 is a superb tool for analyzing data, but before you can do any analysis, you have to get the data into Excel. In many cases, the information you need resides somewhere “outside”—on a server, on a Web site, in an XML file, or perhaps in a database program such as Oracle or Microsoft Access. Excel 2010 supports a wide variety of data formats, including SQL Server (and SQL Server Analysis Services), Access, dBase, FoxPro, Oracle, Paradox, and various kinds of text files. We’ll look at some techniques for retrieving external data in this chapter.
An Office Data Connection (.odc) file is a small XML file that records information about how a workbook connects to an external data source. Such information can include the location and type of the external data, a query specification (if the connection is designed to retrieve a subset of the external source), and details about how to log on to the external server. ODC files are designed to facilitate the reuse of external connections.
Often the simplest way to import data from an external source is to execute an ODC file—a connection that you or someone else has already established. To see what connections are available, click the Data tab on the ribbon, and then click Existing Connections. A dialog box comparable to the one shown in Figure 24-1 appears.
In Figure 24-1, the Show list, at the top of the dialog box, is set to display all available connection files. You can use this list to restrict the dialog box to connections that are already open on your computer, connections that are already in use in the current workbook, or connections that are available on your network. If a connection file that you’re looking for doesn’t appear in the Existing Connections dialog box, click Browse For More. This invokes the Windows search facility, which gathers connection files from various locations on your computer.
Figure 24-1. The Existing Connections dialog box lists connection files that are already established for you.
You can distinguish the various types of connection files that appear in the Existing Connections dialog box by their types of icons. In the examples that follow, we assume that you’re opening one that looks like Northwind 2007 Customers in Figure 24-1. This connection file enables Excel to import data from an Access database. If you open one of the Web query connections (the three included in Excel 2010 begin with “MSN MoneyCentral Investor”), the dialog boxes you see are somewhat different from the ones described here. (For more about Web queries, see Using a Web Query to Return Internet Data on page 849.)
To open a connection file, double-click it in the Existing Connections dialog box. The Import Data dialog box, shown in Figure 24-2, appears. In this dialog box, you indicate where you want the data to go and whether you want an ordinary table or a PivotTable.
If you accept the default settings, Excel creates a table at the current cell location. (For information about creating a PivotTable, see Chapter 23.) The resulting table behaves like any other Excel table (see Chapter 22), except for a crucial difference: The table remains linked to its external source, letting you refresh the data (update it with any changes that have occurred in the external source) on demand or at regular time intervals.
Figure 24-2. By default, Excel renders imported data as a table. By using the Import Data dialog box, you can create a PivotTable (or PivotTable and PivotChart) instead.
Note
If on opening an external data file you see a warning message like this:
click Enable (provided you’re certain the file is safe, of course). To eliminate these messages globally, click File, Options, Trust Center. Then click the Trust Center Settings button. In the Trust Center, click External Content. Finally, select Enable All Data Connections (Not Recommended).
To specify how you want the data refreshed, you can click Properties in the Import Data dialog box (see Figure 24-2). Alternatively, after the table (or PivotTable) has been created, select a cell within it, click the Data tab, and then click Properties. In the External Data Properties dialog box that appears, click the Connection Properties button (to the right of the Name box), as shown next.
These steps bring you to the Connection Properties dialog box, shown in Figure 24-3. Your refresh options appear on the Usage tab in this dialog box.
The check boxes in the Refresh Control area in this dialog box are not mutually exclusive. You can have Excel refresh your data whenever you open the file as well as at regular time intervals. The Enable Background Refresh check box, selected by default, means you can do other work in Excel while the refresh is in progress. Note that this option is not available with online analytical processing (OLAP) queries.
If you select the Refresh Data When Opening The File check box, an additional option to remove the data from your worksheet when you close the file becomes available. You might as well select this check box because Excel is going to refresh the data when you reopen the file anyway.
If connecting to your external data requires a password, Excel, by default, requires that you supply the password again whenever you refresh. If that’s a burdensome obligation, click the Definition tab in the Connection Properties dialog box. Then select the Save Password check box.
In addition to requesting a refresh at regular time intervals, you can refresh the data whenever the need arises. Right-click a cell within the table, and then click Refresh. Alternatively, click the Data tab, click the arrow next to Refresh All, and then click Refresh. (Or simply click Refresh All; this refreshes all connections open in the current workbook.)