About importing data

You can import data to Excel from most data sources by pointing to Import External Data on the Data menu, clicking Import Data, and then choosing the data you want to import in the Select Data Source dialog box.

The Data Connection Wizard, available when you click New Source in the Select Data Source dialog box, makes it possible to import data from external data connections not available from the Select Data Source dialog box. These sources may include OLE DB data sources (including OLAP cubes and exchange servers) and any data sources a system administrator supplies. You cannot filter or join data in the Data Connection Wizard.

The default connection method when you import data using the Data Connection Wizard is through OLE DB providers. The resulting .odc (office data connection) files can be opened for viewing in Internet Explorer and edited in Excel, Notepad, and other Microsoft Office applications if the file doesn't point to an OLAP data source.

The Data Connection Wizard also provides access to a data source called a data retrieval service. A data retrieval service is a Web service installed on Windows SharePoint Services for connecting to and retrieving data. To use a data retrieval service, a client application, such as Excel, sends a query request over HTTP to the data retrieval service on Windows SharePoint Services. The data retrieval service sends that request to the data source, and then passes the data that is returned to it back to the client application as XML. Importing data from a data retrieval service in Excel automatically creates a databound XML list in your worksheet. After adding a databound XML list to your worksheet, you can use the commands on the XML submenu of the Data menu or the XML and List tool bar to refresh data, edit the query, or set the properties of the XML map associated with the XML list.

A default installation of Windows SharePoint Services provides a data retrieval service for connecting to data in SharePoint lists. A SharePoint site administrator can install the Microsoft Office Web Parts and Components to add additional data retrieval services for Microsoft SQL Server and Microsoft Business Solutions. The installation program for Microsoft Office Web Parts and Components is available on the Downloads on Microsoft Office Online.

Note  To bring external data into Microsoft Excel, you must have access to the data. If the external data source you want to access is not on your local computer, you might need to contact the administrator of the database for a password, user permission, or other connection information.

In most cases, you can import data by using the Import Data command as described in the section above. Use Query or another program only if you need to perform specialized query tasks such as the following:

• Filter rows or columns of data before they are brought into Excel.
• Create a parameter query.
• Sort data before it is brought into Excel.
• Join multiple tables.

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

You can use Query to set up ODBC data sources to retrieve data. In Query, you can use the Query Wizard to create a simple query, or you can use advanced criteria in Query to create a more complex query. You can access Query from Excel, or you can create a query from within the PivotTable and PivotChart Wizard.

You can also use Dynamic Data Exchange (DDE) with Query. For more information about DDE, see Query Help.

To import data using Query, you must first:

• Install Query    Query, including the Query Wizard, is an optional feature for Excel. Under most circumstances, you are prompted to install Query when you point to Import External Data on the Data menu and click New Database Query.

• Install ODBC drivers    An ODBC driver is required to retrieve data in relational databases, text files, or Excel using Query. When you install Query, you automatically install a set of ODBC drivers. If you use a driver other than one installed with Query, you must install the driver separately.

• Install data source drivers    A data source driver is required to retrieve OLAP source data. Query supports connecting to databases that are created by using SQL Server OLAP Services; when you installed Query, you automatically installed support for this type of OLAP database. To connect to other OLAP databases, you must install a data source driver and client software.

For more information, see Query Help.

You can import data originating from a Web page by pointing to Import External Data on the Data menu and clicking New Web Query. You must have access to the World Wide Web 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 sources.

You can use a Visual Basic for Applications macro to gain access to an external data source.

Depending on the data source, you will use either ActiveX Data Objects or Data Access Objects to retrieve data using VBA.

If you want to use a macro that you created in Excel version 5.0 or earlier, click Add-Ins on the Tools menu, and then make sure the ODBC Add-In check box is selected.

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

Note  While you are recording a macro that includes a query, Excel can't run the query in the background, even if you chose to run it that way. To change the recorded macro so that it runs in the background, edit the macro in the Visual Basic Editor and change the refresh method for the QueryTable object from "BackgroundQuery := False" to "BackgroundQuery := True".

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 it's being refreshed.

If your external data source requires a password to gain access to the data, you can require that the password is entered each time the external data range is refreshed.

When an external data range expands and additional records are returned, Excel can fill formulas in adjacent columns or within the data range so that they remain next to the appropriate data.

You can also choose how to add new data to your worksheet.

Excel automatically names an external data range as follows:

• External data ranges from Office Data Connection (ODC) files are named with the .odc file extension.
• 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 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.

You can also change the name of an external data range in the Data Range Properties dialog box.