How data journeys through Excel

Here are the main things you can do while data is in the Excel train station:

• Import    You can import data from many different external data sources. These data sources can be on your machine, in the cloud, or half-way around the world. For more information, see Import data from external data sources.

• Power Query    You can use Power Query (previously called Get & Transform) to create queries to shape, transform, and combine data in a variety of ways. You can export your work as a Power Query Template to define a data flow operation in Power Apps. You can even create a data type to supplement linked data types. For more information, see Power Query for Excel Help.

• Security    Data privacy, credentials, and authentication are always an ongoing concern. For more information, see Manage data source settings and permissions and Set privacy levels.

• Refresh    Imported data usually requires a refresh operation to bring in changes, such as additions, updates, and deletes, to Excel. For more information, see Refresh an external data connection in Excel.

• Connections/Properties    Each external data source has assorted connection and property information associated with it that sometimes requires changes depending on your circumstances. For more information, see Manage external data ranges and their properties, Create, edit, and manage connections to external data, and Connection properties.

• Legacy    Traditional methods, such as Legacy Import Wizards and MSQuery, are still available for use. For more information, see Data import and analysis options and Use Microsoft Query to retrieve external data.

The following sections provide more details of what’s going on behind the scenes at this busy, Excel train station.

There are connection, query, and external data range properties. Connection and query properties both contain traditional connection information. In a dialog box title, Connection Properties means there is no query associated with it, but Query Properties means there is. External data range properties control the layout and format of data. All data sources have an External Data Properties dialog box, but data sources that have associated credential and refresh information use the larger External Range Data Properties dialog box.

The following information summarizes the most important dialog boxes, panes, command paths, and corresponding help topics.

Dialog Box or Pane Command Paths	Tabs and tunnels	Main Help topic
Recent sources Data > Recent Sources	(No tabs) Tunnels to Connect > Navigator dialog box	Manage data source settings and permissions
Connection Properties OR Data Connection Wizard Data > Queries & Connections > Connections tab > (right click a connection) > Properties	Usage tab Definition tab Used In tab	Connection properties
Query Properties Data > Existing Connections > (right click a connection) > Edit Connection Properties OR Data > Queries & Connections | Queries tab > (right click a connection) > Properties OR Query > Properties OR Data > Refresh All > Connections (when positioned on a loaded query worksheet)	Usage tab Definition tab Used In tab	Connection properties
Queries & Connections Data > Queries & Connections	Queries tab Connections tab	Connection properties
Existing Connections Data > Existing Connections	Connections tab Tables tab	Connect to external data
External data properties OR External data range properties OR Data > Properties (Disabled if not positioned on a query worksheet)	Used in tab (from Connection Properties dialog box)   Refresh button on the right tunnels to Query Properties	Manage external data ranges and their properties
Connection Properties > Definition tab > Export Connection File OR Query > Export Connection File	(No tabs) Tunnels to  File dialog box Data sources folder	Create, edit, and manage connections to external data

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.

 

The following diagram summarizes the key points about data connections.

1. There are a variety of data sources that you can connect to: Analysis Services, SQL Server, Microsoft Access, other OLAP and relational databases, spreadsheets, and text files.

2. Many data sources have an associated ODBC driver or OLE DB provider.

3. A connection file defines all the information that is needed to access and retrieve data from a data source.

4. Connection information is copied from a connection file into a workbook, and the connection information can easily be edited.

5. The data is copied into a workbook so that you can use it just as you use data stored directly in the workbook.

To find connection files, use the Existing Connections dialog box. (Select Data > 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 Documents folder.

• Connection files on the network    

  This list can be created from 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, or a SharePoint library. 

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 (Select Data > Queries & Connections > Connections tab > (right click a connection) > Properties) to control various settings for connections to external data sources, and to use, reuse, or switch connection files.

Note    Sometimes, the Connection Properties dialog box is named the Query Properties dialog box when there is a query created in Power Query (formerly called Get & Transform) associated with it.

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.

By using the Connections dialog box, you can easily manage these connections, including creating, editing, and deleting them (Select Data > Queries & Connections > Connections tab > (right click a connection) > Properties.) 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.

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. For more information, see Share data with ODC.

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. For more information, see Export to Excel from SharePoint.

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.

There are several data objects (such as an external data range and PivotTable report) that you can use to connect to different data sources. However, the type of data source that you can connect to is different between each data object.

You can use and refresh connected data in Excel Services. As with any external data source, you may need to authenticate your access. For more information, see Refresh an external data connection in Excel. For more information about credentials, see Excel Services Authentication Settings.

The following table summarizes which data sources are supported for each data object in Excel.

Excel data object	Creates External data range?		OLE DB	ODBC	Text file	HTML file	XML file	SharePoint list
Import Text Wizard	Yes		No	No	Yes	No	No	No
PivotTable report (non-OLAP)	No		Yes	Yes	Yes	No	No	Yes
PivotTable report (OLAP)	No		Yes	No	No	No	No	No
Excel Table	Yes		Yes	Yes	No	No	Yes	Yes
XML Map	Yes		No	No	No	No	Yes	No
Web Query	Yes		No	No	No	Yes	Yes	No
Data Connection Wizard	Yes		Yes	Yes	Yes	Yes	Yes	Yes
Microsoft Query	Yes		No	Yes	Yes	No	No	No

Excel Services workaround for Excel tables and named ranges

If you want to display an Excel workbook in Excel Services, you can connect to and refresh data, but you must use a PivotTable report. Excel Services does not support external data ranges, which means that Excel Services does not support an Excel Table connected to a data source, a Web query, an XML map, or Microsoft Query.

However, you can work around this limitation by using a PivotTable to connect to the data source, and then design and layout the PivotTable as a two-dimensional table without levels, groups, or subtotals so that all desired row and column values are displayed. 

Let's take a trip down database memory lane.

About MDAC, OLE DB, and OBC

First of all, apologies for all the acronyms. Microsoft Data Access Components (MDAC) 2.8 is included with Microsoft Windows . With MDAC, you can connect to and use data from a wide variety of relational and nonrelational data sources. You can connect to many different data sources by using Open Database Connectivity (ODBC) drivers or OLE DB providers, which are either built and shipped by Microsoft or developed by various third parties. When you install Microsoft Office, additional ODBC drivers and OLE DB providers are added to your computer.

To see a complete list of OLE DB providers installed on your computer, display the Data Link Properties dialog box from a Data Link file, and then click the Provider tab.

To see a complete list of ODBC providers installed on your computer, display the ODBC Database Administrator dialog box, and then click the Drivers tab.

You can also use ODBC drivers and OLE DB providers from other manufacturers to get information from sources other than Microsoft data sources, including other types of ODBC and OLE DB databases. For information about installing these ODBC drivers or OLE DB providers, check the documentation for the database, or contact your database vendor.

Using ODBC to connect to data sources

In the ODBC architecture, an application (such as Excel) connects to the ODBC Driver Manager, which in turn uses a specific ODBC driver (such as the Microsoft SQL ODBC driver) to connect to a data source (such as a Microsoft SQL Server database).

To connect to ODBC data sources, do the following:

1. Ensure that the appropriate ODBC driver is installed on the computer that contains the data source.

2. Define a data source name (DSN) by using either the ODBC Data Source Administrator to store the connection information in the registry or a DSN file, or a connect string in Microsoft Visual Basic code to pass the connection information directly to the ODBC Driver Manager.

   To define a data source, in Windows click the Start button and then click Control Panel. Click System and Maintenance, and then click Administrative Tools. Click Performance and Maintenance, click Administrative Tools. and then click Data Sources (ODBC). For more information about the different options, click the Help button in each dialog box.

Machine data sources

Machine data sources store connection information in the registry, on a specific computer, with a user-defined name. You can use machine data sources only on the computer they are defined on. There are two types of machine data sources — user and system. User data sources can be used only by the current user and are visible only to that user. System data sources can be used by all users on a computer and are visible to all users on the computer.

A machine data source is especially useful when you want to provide added security, because it helps ensure that only users who are logged on can view a machine data source, and a machine data source cannot be copied by a remote user to another computer.

File data sources

File data sources (also called DSN files) store connection information in a text file, not the registry, and are generally more flexible to use than machine data sources. For example, you can copy a file data source to any computer with the correct ODBC driver, so that your application can rely on consistent and accurate connection information to all the computers it uses. Or you can place the file data source on a single server, share it between many computers on the network, and easily maintain the connection information in one location.

A file data source can also be unshareable. An unshareable file data source resides on a single computer and points to a machine data source. You can use unshareable file data sources to access existing machine data sources from file data sources.

Using OLE DB to connect to data sources

In the OLE DB architecture, the application that accesses the data is called a data consumer (such as Excel), and the program that allows native access to the data is called a database provider (such as Microsoft OLE DB Provider for SQL Server).

A Universal Data Link file (.udl) contains the connection information that a data consumer uses to access a data source through the OLE DB provider of that data source. You can create the connection information by doing one of the following:

• In the Data Connection Wizard, use the Data Link Properties dialog box to define a data link for an OLE DB provider. 

• Create a blank text file with a .udl file name extension, and then edit the file, which displays the Data Link Properties dialog box.