Understanding the basics of data connections

Data in an Excel workbook can come from two different locations. The data may be stored directly in the workbook, or it may be stored in an external data source, such as a text file, a database, or an Online Analytical Processing (OLAP) cube. This external data source is connected to the workbook through a data connection, which is a set of information that describes how to locate, log in to, and access the external data source.

The main benefit of connecting to external data is that you can periodically analyze this data without repeatedly copying the data to your workbook, which is an operation that can be time consuming and prone to error. After connecting to external data, you can also automatically refresh (or update) your Excel workbooks from the original data source whenever the data source is updated with new information.

Connection information is stored in the workbook and can also be stored in a connection file, such as an Office Data Connection (ODC) file (.odc) or a Data Source Name file (.dsn).

To bring external data into Excel, you need access to the data. If the external data source that you want to access is not on your local computer, you may need to contact the administrator of the database for a password, user permissions, or other connection information. If the data source is a database, make sure that the database is not opened in exclusive mode. If the data source is a text file or a spreadsheet, make sure that another user does not have it open for exclusive access.

Many data sources also require an ODBC driver or OLE DB provider to coordinate the flow of data between Excel, the connection file, and the data source.

Finding connections

To find connection files, use the Existing Connections dialog box. (On the Data tab, in the Get External Data group, click Existing Connections.) Using this dialog box, you can see the following types of connections:

• Connections in the workbook    

  This list displays all the current connections in the workbook. The list is created from connections that you already defined, that you created by using the Select Data Source dialog box of the Data Connection Wizard, or from connections that you previously selected as a connection from this dialog box.

• Connection files on your computer    

  This list is created from the My Data Sources folder that is usually stored in the My Documents (Windows XP) or Documents (Windows Vista) folder.

• Connection files on the network    

  This list can be created from the following:

  • A set of folders on your local network, the location of which can be deployed across the network as part of the deployment of Microsoft Office group policies.

  • An Excel Services Data Connection Library (DCL) on a SharePoint Foundation site. 

• Editing connection properties

  You can also use Excel as a connection file editor to create and edit connections to external data sources that are stored in a workbook or in a connection file. If you don’t find the connection that you want, you can create a connection by clicking Browse for More to display the Select Data Source dialog box, and then clicking New Source to start the Data Connection Wizard.

  After you create the connection, you can use the Connection Properties dialog box (On the Data tab, in the Get External Data group, click Properties.) to control various settings for connections to external data sources, and to use, reuse, or switch connection files.

  If you use a connection file to connect to a data source, Excel copies the connection information from the connection file into the Excel workbook. When you make changes by using the Connection Properties dialog box, you are editing the data connection information that is stored in the current Excel workbook and not the original data connection file that may have been used to create the connection (indicated by the file name that is displayed in the Connection File property on the Definition tab). After you edit the connection information (with the exception of the Connection Name and Connection Description properties), the link to the connection file is removed and the Connection File property is cleared.

  To ensure that the connection file is always used when a data source is refreshed, click Always attempt to use this file to refresh this data on the Definition tab. Selecting this check box ensures that updates to the connection file will always be used by all workbooks that use that connection file, which must also have this property set.

  Managing connections

  By using the Workbook Connections dialog box, you can easily manage these connections, including creating, editing, and deleting them. (On the Data tab, in the Get External Data group, click Connections.) You can use this dialog box to do the following:

  • Create, edit, refresh, and delete connections that are in use in the workbook.

  • Verify the source of external data. You may want to do this in case the connection was defined by another user.

  • Show where each connection is used in the current workbook.

  • Diagnose an error message about connections to external data.

  • Redirect a connection to a different server or data source, or replace the connection file for an existing connection.

  • Make it easy to create and share connection files with users.

  Sharing connections

  Connection files are particularly useful for sharing connections on a consistent basis, making connections more discoverable, helping to improve security of connections, and facilitating data source administration. The best way to share connection files is to put them in a secure and trusted location, such as a network folder or SharePoint library, where users can read the file but only designated users can modify the file.

  Using ODC files

  You can create Office Data Connection (ODC) files (.odc) by connecting to external data through the Select Data Source dialog box or by using the Data Connection Wizard to connect to new data sources. An ODC file uses custom HTML and XML tags to store the connection information. You can easily view or edit the contents of the file in Excel.

  You can share connection files with other people to give them the same access that you have to an external data source. Other users don’t need to set up a data source to open the connection file, but they may need to install the ODBC driver or OLE DB provider required to access the external data on their computer.

  ODC files are the recommended method for connecting to data and sharing data. You can easily convert other traditional connection files (DSN, UDL, and query files) to an ODC file by opening the connection file and then clicking the Export Connection File button on the Definition tab of the Connection Properties dialog box.

  Using query files

  Query files are text files that contain data source information, including the name of the server where the data is located and the connection information that you provide when you create a data source. Query files are a traditional way for sharing queries with other Excel users.

  Using .dqy query files    You can use Microsoft Query to save .dqy files that contain queries for data from relational databases or text files. When you open these files in Microsoft Query, you can view the data returned by the query and modify the query to retrieve different results. You can save a .dqy file for any query that you create, either by using the Query Wizard or directly in Microsoft Query.

  Using .oqy query files    You can save .oqy files to connect to data in an OLAP database, either on a server or in an offline cube file (.cub). When you use the Multi-Dimensional Connection Wizard in Microsoft Query to create a data source for an OLAP database or cube, an .oqy file is created automatically. Because OLAP databases aren’t organized in records or tables, you can’t create queries or .dqy files to access these databases.

  Using .rqy query files    Excel can open query files in .rqy format to support OLE DB data source drivers that use this format. For more information, see the documentation for your driver.

  Using .qry query files    Microsoft Query can open and save query files in .qry format for use with earlier versions of Microsoft Query that cannot open .dqy files. If you have a query file in .qry format that you want to use in Excel, open the file in Microsoft Query, and then save it as a .dqy file. For information about saving .dqy files, see Microsoft Query Help.

  Using .iqy Web query files    Excel can open .iqy Web query files to retrieve data from the Web.

  Using external data ranges and properties

  An external data range (also called a query table) is a defined name or table name that defines the location of the data brought into a worksheet. When you connect to external data, Excel automatically creates an external data range. The only exception to this is a PivotTable report connected to a data source, which does not create an external data range. In Excel, you can format and lay out an external data range or use it in calculations, as with any other data.

  Excel automatically names an external data range as follows:

  • External data ranges from Office Data Connection (ODC) files are given the same name as the file name.

  • External data ranges from databases are named with the name of the query. By default Query_from_source is the name of the data source that you used to create the query.

  • External data ranges from text files are named with the text file name.

  • External data ranges from Web queries are named with the name of the Web page from which the data was retrieved.

  If your worksheet has more than one external data range from the same source, the ranges are numbered. For example, MyText, MyText_1, MyText_2, and so on.

  An external data range has additional properties (not to be confused with connection properties) that you can use to control the data, such as the preservation of cell formatting and column width. You can change these external data range properties by clicking Properties in the Connections group on the Data tab, and then making your changes in the External Data Range Properties or External Data Properties dialog boxes.