Chapter 23: Working with External Data


Microsoft Office Excel 2007 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 Office Access. Office Excel 2007 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.

Using and Reusing Data Connections

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 either 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 in the Get External Data group. A dialog box comparable to the one shown in Figure 23-1 appears.

image from book
Figure 23-1: The Existing Connections dialog box lists connection files that are already established for you.

In Figure 23-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 will invoke the Windows Vista search facility, which will gather connection files from various locations on your computer.

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'll assume that you're opening one that looks like Northwind 2007 Customers in Figure 23-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 2007 begin with "MSN MoneyCentral Investor"), the dialog boxes you see will be somewhat different from the ones described here. (For more about Web queries, see "Using a Web Query to Return Internet Data" on page 779.)

To open a connection file, double-click it in the Existing Connections dialog box. The Import Data dialog box, shown in Figure 23-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.

image from book
Figure 23-2: By default, Excel renders imported data as a table. Using the Import Data dialog box, you can create a PivotTable (or PivotTable and PivotChart) instead.

If you accept the default settings, Excel creates a table at the current cell location. (For information about creating a PivotTable, see Chapter 22, "Analyzing Data with PivotTable Reports.") The resulting table behaves like any other Excel table (see Chapter 21, "Managing Information in Tables"), 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.

Setting Refresh Options

To specify how you want the data refreshed, you can click Properties in the Import Data dialog box (see Figure 23-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):

image from book

These steps bring you to the Connection Properties dialog box, shown in Figure 23-3. Your refresh options appear on the Usage tab in this dialog box.

image from book
Figure 23-3: You can set your connection to refresh the imported data at regular time intervals.

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 as well, because Excel is going to refresh the data when you reopen the file anyway.

Requiring or Not Requiring a Password to Refresh

If connecting to your external data requires a password, Excel, by default, will require 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 clear the Save Password check box.

Refreshing on Demand

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.)



Microsoft Office Excel 2007 Inside Out
MicrosoftВ® Office ExcelВ® 2007 Inside Out (Inside Out (Microsoft))
ISBN: 073562321X
EAN: 2147483647
Year: 2004
Pages: 260

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net