Overview of connecting (importing) data

This reference article discusses importing and connecting data. You will learn about tasks like importing, updating, securing, and managing data in your Excel 2007 worksheets.

In this article


Working with external data connections

The following sections discuss how external data connections work, and how to find edit, manage, and share the connection information with other applications and users.

Top of Page Top of Page

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, 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, which is an operation that can be time-consuming and error-prone. 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 (external data: Data that is stored outside of Excel. Examples include databases created in Access, dBASE, SQL Server, or on a Web server.) into Excel, you need access to the data. If the external data source (data source: A stored set of "source" information used to connect to a database. A data source can include the name and location of the database server, the name of the database driver, and information that the database needs when you log on.) 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 permission, 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.


Connecting to external data sources

Callout 1 There are a variety of data sources that you can connect to: Analysis Services, SQL Server, Microsoft Office Access, other OLAP and relational databases, spreadsheets, and text files.
Callout 2 Many data sources have an associated ODBC driver or OLE DB provider.
Callout 3 A connection file defines all of the information that is needed to access and retrieve data from a data source.
Callout 4 Connection information is copied from a connection file into a workbook and can be edited.
Callout 5 The data is copied into a workbook so that you can use it just as you use data stored directly in the workbook.

Top of Page Top of Page

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 of 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 Microsoft Office SharePoint Server 2007 site. For more information about DCLs, see Publish to Excel Services.

Top of Page Top of Page

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.

Once 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, which is indicated by the file name that is displayed in the Connection File property on the Definition tab. Once 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.

Top of Page Top of Page

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 where external data is coming from, because, for example, 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.

Top of Page Top of Page

Sharing connections

Connection files are particularly useful for sharing connections on a consistent basis, making them more discoverable, helping to improve their security, 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 Office 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 in the query and select different data for the query to retrieve. 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 (offline cube file: A file you create on your hard disk or a network share to store OLAP source data for a PivotTable or PivotChart report. Offline cube files allow you to keep working when you are not connected to the OLAP server.) (.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.

Top of Page Top of Page

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 (Web query: A query that retrieves data stored on your intranet or the Internet.) 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.

 Note   If you want to share a summary or report that is based on external data, you can give other people a workbook that contains an external data range, or you can create a report template. A report template (report template: An Excel template (.xlt file) that includes one or more queries or PivotTable reports that are based on external data. When you save a report template, Excel saves the query definition but doesn't store the queried data in the template.) lets you save the summary or report without saving the external data so that the file is smaller. The external data is retrieved when a user opens the report template.

Top of Page Top of Page

Understanding data source support in Excel and Excel Services

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 also use and refresh connected data in Excel Services, but there are additional limitations and workarounds that you should be aware of.

Excel data object and data source support

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

Supported data source
Excel
data

object
Creates
External

data

range?
OLE
DB
ODBC Text
file
HTML
file
XML
file
Share-
Point

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

 Note   These files, a text file imported by using the Import Text Wizard, an XML file imported by using an XML Map, and an HTML or XML file imported by using a Web Query do not use an ODBC driver or OLE DB provider to make the connection to the data source.

Excel Services and data source support

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. For more information, see Use a PivotTable report to make external table data available in Excel Services.

Top of Page Top of Page

Understanding Microsoft Data Access Components

Microsoft Data Access Components (MDAC) 2.8 is included with Microsoft Windows Server 2003, Windows Vista, and Windows XP SP2. 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 (OLE DB: A component database architecture that implements efficient network and internet access to many types of data sources, including relational data, mail files, flat files, and spreadsheets.) 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.

Top of Page Top of Page

Using ODBC to connect to data sources

The following sections describe Open Database Connectivity (ODBC) in more detail.

The ODBC architecture

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

Defining connection information

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 Vista, click the Start button and then click Control Panel. Click System and Maintenance, and then click Administrative Tools. In Windows XP and Windows Server, click Start, and then click Control Panel. 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 on only 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.

Top of Page Top of Page

Using OLE DB to connect to data sources

The following sections describe Object Linking and Embedding Database (OLE DB) in more detail.

The OLE DB architecture

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

Defining connection information

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. For more information, see Importing data by using the Data Connection Wizard.
  • Create a blank text file with a .udl file type, and then edit the file, which displays the Data Link Properties dialog box.

Top of Page Top of Page

Refreshing data

When you are connected to an external data source, you can also perform a refresh operation to retrieve the updated data. Each time that you refresh data, you see the most recent version of the data, including any changes that were made to the data since it was last refreshed.

The following illustration explains the basic process of what happens when you refresh data connected to an external data source.


The basic process of refreshing external data

Callout 1 A refresh operation gets up-to-date data.
Callout 2 The connection file defines all of the information that is needed to access and retrieve data from an external data source.
Callout 3 There are a variety of data sources that you can refresh: OLAP, SQL Server, Access, OLE DB, ODBC, spreadsheets, and text files.
Callout 4 Up-to-date data is added to the current workbook.

Excel provides many options for refreshing imported data, including refreshing the data whenever you open the workbook and automatically refreshing data at timed intervals. You can continue to work in Excel while data is being refreshed, and you can also check the status of the refresh while the data is being refreshed.

If your external data source requires a password (password: A word, phrase, or string of characters used to prevent unauthorized access to a database. To gain access to the database, you must enter the password correctly.) to gain access to the data, you can require that the password is entered each time the external data range (external data range: A range of data that is brought into a worksheet but that originates outside of Excel, such as in a database or text file. In Excel, you can format the data or use it in calculations as you would any other data.) is refreshed.

Top of Page Top of Page

Importing data from data sources

Windows XP, Windows Vista, and Microsoft Office supply the ODBC drivers and OLE DB providers that you can use to retrieve data from the following common data sources: Microsoft Office Access, HTML files on the World Wide Web, text files, Microsoft SQL Server, Microsoft SQL Server Analysis Services, and XML files. By using the Data Connection Wizard and Microsoft Query, you can also access many other data sources that have the appropriate OLE DB providers and ODBC drivers, including other Excel worksheets, Microsoft FoxPro, dBASE, Paradox, Oracle, and DB2.

Top of Page Top of Page

Importing data from an Access database

There are several ways to exchange data between Microsoft Office Access and Microsoft Office Excel.

  • To bring data into Excel from Access, you can copy data from an Access datasheet and paste it into an Excel worksheet, connect to an Access database from an Excel worksheet, or export Access data into an Excel worksheet.
  • To bring data into Access from Excel, you can copy data from an Excel worksheet and paste it into an Access datasheet, import an Excel worksheet into an Access table, or link to an Excel worksheet from an Access table.

 Note   The word import has two different meanings between Excel and Access. In Excel, the word import means to make a permanent connection to data that can be refreshed. In Access, the word import means to bring data into Access once, but without a data connection.

Working with Access data in Excel

You may want to work with Access data in an Excel workbook in order to take advantage of the data analysis and charting features, the flexibility in data arrangement and layout, or functions that are not available in Access.

Connecting to Access data from Excel

To bring refreshable Access data into Excel, you can create a connection to the Access database and retrieve all of the data from a table or query. For example, you may want to update an Excel summary budget report that you distribute every month so that it contains the current month's data.

Exporting Access data to Excel

By using the Export Wizard in Access, you can export an Access database object, such as a table, query, form, or selected records in a view into an Excel worksheet. When you perform an export operation, you can save the details for future use and even schedule the export operation to run automatically at specified intervals.

The following are common scenarios for exporting data from Access to Excel:

  • Your department or workgroup uses both Access and Excel to work with data. You store the data in Access databases, but you use Excel to analyze the data and to distribute the results of your analysis. Your team currently exports data to Excel when they need to, but you would like to make this process more efficient.
  • You are a long-time user of Access, but your manager prefers to view reports in Excel. At regular intervals, you do the work of copying the data into Excel, but you would like to automate this process to save yourself time.

For more information about exporting data from Access to Excel, see the Access Help system.

Working with Excel data in Access

You may want to work with Excel data in an Access database to take advantage of Access data management, security, or multiuser features. Although there are many useful features in Access, there are two features that users find particularly useful for their Excel data:

  • Reports     If you are familiar with designing Access reports and you want to summarize and organize your Excel data in this type of report, you can create an Access report. For example, you can create more flexible reports, such as group and summary reports, printed labels, and graphical reports.
  • Forms     If you want to use a form to find or to display data in Excel, you can create an Access form. For example, you can create an Access form to display fields in a different order from the order of columns in your worksheet, or you can view a lengthy row of data more easily on one screen.

For more information about working with Access forms and reports, see the Access Help system.

Linking to Excel data from Access

You can link an Excel range into an Access database as a table. Use this approach when you plan to continue maintaining the range in Excel but also want the range to be available from within Access. You create this type of link from within the Access database, not from Excel.

When you link to an Excel worksheet or a named range, Access creates a new table that is linked to the source cells. Any changes that you make to the source cells in Excel are reflected in the linked table. However, you cannot edit the contents of the corresponding table in Access. If you want to add, edit, or delete data, you must make the changes in the source file.

The following are common scenarios for linking to an Excel worksheet from within Access:

  • You want to continue to keep your data in Excel worksheets, but be able to use the powerful querying and reporting features of Access.
  • Your department or workgroup uses Access, but data from external sources that you work with is in Excel worksheets. You don't want to maintain copies of external data, but you want to be able to work with it in Access.

For more information about linking data from Access to Excel, see the Access Help system.

Importing Excel data into Access

To store data from Excel in an Access database, and then use and maintain the data in Access from then on, you can import the data. When you import data, Access stores the data in a new or existing table without altering the data in Excel. You can import only one worksheet at a time during an import operation. To import data from multiple worksheets, repeat the import operation for each worksheet.

The following are common scenarios for importing Excel data into Access:

  • You are a long-time user of Excel but, going forward, you want to use Access to work with this data. You want to move the data in your Excel worksheets into one or more new Access databases.
  • Your department or workgroup uses Access, but you occasionally receive data in Excel format that must be merged with your Access databases. You want to import these Excel worksheets into your database as you receive them.
  • You use Access to manage your data, but the weekly reports that you receive from the rest of your team are Excel workbooks. You would like to streamline the import process to ensure that data is imported every week at a specific time into your database.

For more information about importing data from Excel to Access, see the Access Help system.

Top of Page Top of Page

Importing data from the Web

Web pages often contain information that is perfect for analysis in Excel. For example, you can analyze stock quotes in Excel by using information that comes directly from a Web page. Depending on your needs, you can retrieve data that is refreshable (that is, you can update the data in Excel with the latest data on the Web page), or you can get data from a Web page and keep it static on the worksheet.

You can use a Web query to retrieve data stored on your intranet or the Internet, such as a single table, multiple tables, or all of the text on a Web page, and analyze the data by using the tools and features in Excel. With the click of a button, you can easily refresh the data with the latest information from the Web page. For example, you can retrieve and update stock quotes from a public Web page, or retrieve and update a table of sales information from a company Web page.

You can import data that originates from a Web page by using the New Web Query dialog box. (On the Data tab, in the Get External Data group, click From Web.) You need access to the World Wide Web (World Wide Web (WWW): The multimedia branch of the Internet that presents not only text, but also graphics, sound, and video. On the Web, users can easily jump from item to item, page to page, or site to site by using hyperlinks.) through your company's intranet or through a modem on your computer or network, or you can make a query against local HTML or XML files.

Top of Page Top of Page

Importing text files

You can use Excel to import data from a text file into a worksheet. (On the Data tab, in the Get External Data group, click From Text.) The Text Import Wizard examines the text file that you are importing and helps you ensure that the data is imported in the way that you want.

There are two ways to import data from a text file by using Excel: You can open the text file in Excel (which does not make a connection to the text file), or you can import the text file as an external data range (which does make a connection to the text file).

There are two commonly used text file formats:

  • Delimited text files (.txt), in which the TAB character (ASCII character code 009) usually separates each field of text.
  • Comma-separated values (CSV) text files (.csv), in which the comma character (,) usually separates each field of text.

You can also change the separator character that is used in both delimited and .csv text files. This may be necessary to make sure that the import or export operation works the way that you want it to.

Top of Page Top of Page

Importing data from Microsoft SQL Server

Microsoft SQL Server is a full-featured, relational database designed for enterprise-wide data solutions that require optimum performance, availability, scalability, and security. In Excel, you can easily connect to a Microsoft SQL Server database. (On the Data tab, in the Get External Data group, click From Other Sources, and then click From SQL Server.)

When you connect to a Microsoft SQL Server database, the Data Connection Wizard displays three pages:

  • Page 1: Connect to Database Server     Use this page to specify the server and the way that you log on to the database server.
  • Page 2: Select Database and Table    Use this page to specify the database, and table or query.
  • Page 3: Save Data File and Connection    Use this page to specify and describe the connection file and search phrases for locating the file.

Top of Page Top of Page

Importing data from Microsoft SQL Server Analysis Services

Analysis Services (a component of Microsoft SQL Server) that supports business intelligence and is a foundation for Online Analytical Processing (OLAP) (OLAP: A database technology that has been optimized for querying and reporting, instead of processing transactions. OLAP data is organized hierarchically and stored in cubes instead of tables.)analysis systems, Key Performance Indicator (KPI) scorecards, data mining, and dashboard reporting systems. In Excel, you can easily connect to an an Analysis Services OLAP database by using an OLAP provider. (On the Data tab, in the Get External Data group, click From Other Sources, and then click From Analysis Services.) An OLAP provider is a set of software that provides access to a particular type of OLAP database. This software can include a data source driver and other client software that is necessary to connect to a database. You must use a PivotTable (PivotTable report: An interactive, crosstabulated Excel report that summarizes and analyzes data, such as database records, from various sources, including ones that are external to Excel.) report to connect to an OLAP Provider.

You can also access OLAP data when you are disconnected from the OLAP data source. An offline cube file (offline cube file: A file you create on your hard disk or a network share to store OLAP source data for a PivotTable or PivotChart report. Offline cube files allow you to keep working when you are not connected to the OLAP server.) is a file with a .cub extension that stores a portion of the source data (source data: The list or table that's used to create a PivotTable or PivotChart report. Source data can be taken from an Excel list or range, an external database or cube, or another PivotTable report.) from an OLAP server database. Use an offline cube file to continue to make changes to PivotTable and PivotChart reports (PivotChart report: A chart that provides interactive analysis of data, like a PivotTable report. You can change views of data, see different levels of detail, or reorganize the chart layout by dragging fields and by showing or hiding items in fields.) when the server is unavailable or when you are disconnected from the network.

When you connect to a Microsoft SQL Server Analysis Services, the Data Connection Wizard displays three pages:

  • Page 1: Connect to Database Server     Use this page to specify the server and the way that you log on to the database server.
  • Page 2: Select Database and Table    Use this page to specify the database and cube.
  • Page 3: Save Data File and Connection    Use this page to specify and describe the connection file and search phrases for locating the file.

Top of Page Top of Page

Importing XML data

Office Excel makes it easy to import Extensible Markup Language (XML) (Extensible Markup Language (XML): A condensed form of Standard Generalized Markup Language (SGML) that enables developers to create customized tags that offer flexibility in organizing and presenting information.) data that is created from other databases and applications, map XML elements from an XML schema (XML Schema: A formal specification, written in XML, that defines the structure of an XML document, including element names and rich data types, which elements can appear in combination, and which attributes are available for each element.) to worksheet cells, and export revised XML data for interaction with other databases and applications. Think of these new XML features as turning Excel into an XML data file generator with a familiar user interface.

By using XML maps, you can easily add, identify, and extract specific pieces of business data from Excel documents. For example, an invoice that contains the name and address of a customer, or a report that contains last quarter's financial results are no longer just static reports. You can easily import this information from databases and applications, revise it and export it to the same or other databases and applications.

Key XML scenarios

The following are key scenarios that the XML features are designed to address:

  • Extend the functionality of existing Excel templates by mapping XML elements onto existing cells. This makes it easier to get XML data into and out of your templates without having to redesign them.
  • Use XML data as input to your existing calculation models by mapping XML elements onto existing spreadsheets.
  • Import XML data files into a new workbook.
  • Import XML data from a Web service into your Excel worksheet.
  • Export data in mapped cells to XML data files independent from other data in the workbook.

The basic process of using XML data in Excel

The following diagram shows how the different files and operations work together when you use XML with Excel. Essentially, there are five phases to the process.

Overview of how Excel works with XML data

Step 1 Adding an XML schema file (.xsd) to a workbook.

Step 2 Mapping XML schema elements to individual cells or XML lists.

Step 3 Importing an XML data file (.xml) and binding the XML elements to mapped cells.

Step 4 Entering data, moving mapped cells, and leveraging Excel functionality, while preserving XML structure and definitions.

Step 5 Exporting revised data from mapped cells to an XML data file.

Using XML data

When you import the contents of an XML data file into an existing XML map in your workbook, you bind the data from the file to an XML map stored in your workbook. This means that each data element in the XML data file has a corresponding element in the XML schema that you mapped from an XML Schema file or inferred schema. Each XML map can only have one XML data binding, and an XML data binding is bound to all of the mappings that were created from a single XML map.

You can display the XML Map Properties dialog box (On the Developer tab, in the XML group, click Map Properties.), where there are three options, all on by default, that you can set or clear to control the behavior of an XML data binding:

  • Validate data against schema for import and export    Specifies if Excel validates data against the XML map when importing data. Set this option when you want to ensure that the XML data you import conforms to the XML schema.
  • Overwrite existing data with new data    Specifies if data is overwritten when you import data. Set this option when you want to replace the current data with new data, for example, when up-to-date data is contained in the new XML data file.
  • Append new data to existing XML lists    Specifies if the contents of the data source are appended to the existing data on the worksheet. Set this option, for example, when you are consolidating data from several similar XML data files into an XML list, or you do not want to overwrite the contents of a cell that contains a function.

Top of Page Top of Page

Importing data by using the Data Connection Wizard

You can use the Data Connection Wizard to connect to an OLE DB and ODBC external data source that has already been defined. To open the Data Connection Wizard, on the Data tab, in the Get External Data group, click From Other Sources, and then click From Data Connection Wizard.

If you choose the Other/advanced data source option in the Data Connection Wizard, you can view a list of available OLE DB providers in the Data Link Properties dialog box. In addition, the Microsoft OLE DB Provider for ODBC Drivers also allows access to ODBC data sources. For more information on using each tab in this dialog box, click Help in the Data Link Properties dialog box.

In general, to define connection information in the Data Link Properties dialog box, do the following :

  • Click the Provider tab, select the OLE DB provider, and then click Next, to display the Connection tab, to enter specific connection information for that OLE DB provider.

Each OLE DB provider defines specific connection information. For example, Microsoft OLE DB Provider for SQL Server requires a server name, server location, and a user name, and you may want to define additional information, such as a password or whether you want to use Microsoft Windows integrated security.

  • Click the Advanced tab to provide additional information, such as network settings and access permissions.
  • Click the All tab to define initialization properties for that OLE DB provider.

 Note   You cannot filter or join data in the Data Connection Wizard.

Top of Page Top of Page

Importing data by using Microsoft Query

You can also use Microsoft Query to import data. (On the Data tab, in the Get External Data group, click From Other Sources, and then click From Microsoft Query.) You use Microsoft Query to set up ODBC data sources to retrieve data. In Microsoft Query, you can use the Query Wizard to create a simple query (query: In Query or Access, a means of finding the records that answer a particular question you ask about the data stored in a database.), or you can use advanced criteria in Query to create a more complex query and do the following:

Microsoft Query provides a simple front end, easily accessible from within Excel, to perform these specialized query tasks.

Top of Page Top of Page

Importing data programmatically and by using functions

If you are a developer, there are several approaches within Excel that you can take to import data:

For more information about creating Visual Basic for Applications, see Visual Basic Help.

Top of Page Top of Page

Making data access more secure

When you connect to an external data source or refresh the data, it is important to be aware of potential security issues and to know what you can do about these security issues. Use the following guidelines and best practices to help secure your data.

Top of Page Top of Page

Storing data connections in a trusted location

A data connection file often contains one or more queries that are used to refresh external data. By replacing this file, a user who has malicious intent can design a query to access confidential information and distribute it to other users or perform other harmful actions. Therefore, it is important to ensure the following:

  • The connection file was written by a reliable individual.
  • The connection file is secure and comes from a trusted location.

To help improve security, connections to external data may be disabled on your computer. To connect to data when you open a workbook, you must activate data connections by using the Trust Center bar, or by putting the workbook in a trusted location. For more information, see Create, remove, or change a trusted location for your files, Add, remove, or view a trusted publisher, and View my security settings in the Trust Center.

Top of Page Top of Page

Using credentials in a safe manner

Accessing an external data source usually requires credentials (such as a user name and a password) that are used to authenticate the user. Make sure that these credentials are provided to you in a safe and secure manner and that you do not inadvertently reveal these credentials to others.

Use strong passwords that combine uppercase and lowercase letters, numbers, and symbols. Weak passwords don't mix these elements. Strong password: Y6dh!et5. Weak password: House27. Passwords should be 8 or more characters in length. A pass phrase that uses 14 or more characters is better. For more information, see Help protect your personal information with strong passwords.

It is critical that you remember your password. If you forget your password, Microsoft cannot retrieve it. Store the passwords that you write down in a secure place away from the information that they help protect.

Avoid saving logon information when connecting to data sources. This information can be stored as plain text in the workbook and the connection file, and a malicious user can access the information to compromise the security of the data source.

When possible, use Windows Authentication (also referred to as a trusted connection), which uses a Windows user account to connect to SQL Server. When a user connects through a Windows user account, SQL Server uses information in the Windows operating system to validate the account name and password. Before you can use Windows Authentication, a server administrator must configure SQL Server to use this mode of authentication. If Windows Authentication is not available, avoid saving users' logon information. It is more secure for users to enter their logon information each time that they log on.

Top of Page Top of Page

Publishing to Excel Services securely

When you connect to a data source, you can use the Excel Services Authentication Settings dialog box to choose a method of authentication when you access the data source in Excel Services. You can select one of the following options to log on to the data source:

  • Windows Authentication    Select this option to use the Windows user name and password of the current user. This is the most secure method, but it can impact performance when there are many users.
  • SSO    Select this option to use Single Sign On, and then enter the appropriate identification string in the SSO ID text box. A site administrator can configure a SharePoint site to use a Single Sign On database where a user name and password can be stored. This method can be the most efficient when there are many users.
  • None    Select this option to save the user name and password in the connection file.

 Note   The authentication setting is only used by Excel Services, and not by Microsoft Office Excel. If you want to ensure that the same data is accessed whether you open the workbook in Excel or Excel Services, make sure that the authentication setting in Excel is the same.

To improve the security of your connections, use a Data Connection Library (DCL). A DCL is a special SharePoint document library that can be defined as a trusted location library, and that makes it easy to store, secure, share, and manage ODC files. For example, an administrator may need to move a database from a test server to a production server, or update a query that accesses the data. By using one ODC file saved in a DCL, administration of this connection information is much easier and the user's access to data is more convenient, because all workbooks use the same connection file, and a refresh operation (whether on the client or server computer) gets up-to-date changes to that connection file. You can even set up Office SharePoint Server and a user's client computer to automatically detect changes to the connection file and use the most up-to-date version of that connection file. For more information, see Office SharePoint Server 2007 Central Administration Help.

Top of Page Top of Page

Issues about connecting to data

The following sections discuss several issues that you may encounter when you connect to external data

Top of Page Top of Page

Issue: I run out of disk space when I try to import data.

If you run out of disk space when you connect to external data, consider doing the following:

Check and increase available disk space     To free some space on your hard disk, try emptying the Recycle Bin, backing up unneeded files and then removing them from your hard disk, or removing Windows components that you don't use. For more information about freeing hard disk space, see Windows Help.

Strategies that you can try when disk space is limited     If you have only a limited amount of space available on your hard disk, try the following:

Microsoft Query    Do one or more of the following:

Data Connection Wizard     Although you cannot limit the data through the Data Connection Wizard, you may be able to limit the data from the data source by defining a query in the data source. If you don't have the ability or permission to define a query at the data source, contact your data administrator.

Top of Page Top of Page

Issue: Where is the OLAP Cube Wizard?

The OLAP Cube Wizard in Microsoft Query, which created an OLAP Cube from a Jet database, is no longer available in Office Excel.

Top of Page Top of Page

Issue: What happened to data retrieval services?

Introduced in Microsoft Office 2003, data retrieval services use a Universal Data Connection (UDC) file (.udcx) to access Web Services and query different data sources.

In the Data Connection Wizard on the Welcome to the Data Connection Wizard page, you can select the Microsoft Business Solutions or Data retrieval services data source options. The Data retrieval services option allows you to choose two data retrieval services: Windows SharePoint Services lists, which retrieves data from lists and document libraries on a server running Windows SharePoint Services, and Microsoft SQL Server, which retrieves data from Microsoft SQL Server 2000 or later.

Although the Data Connection Wizard no longer supports editing UDC connection files, Excel continues to support existing data retrieval services connections, and you can still refresh those connections.

Top of Page Top of Page

 
 
Applies to:
Excel 2007