Use Microsoft Query to retrieve external data

Try Office 2010 In Excel 2010, external data connections are easier to work with and more secure.
Read an article or try Office 2010!

You can use Microsoft Query to retrieve data from external sources. By using Microsoft Query to retrieve data from your corporate databases (database: A collection of data related to a particular subject or purpose. Within a database, information about a particular entity, such as an employee or order, is categorized into tables, records, and fields.) and files, you don't have to retype the data that you want to analyze in Excel. You can also refresh your Excel reports and summaries automatically from the original source database whenever the database is updated with new information.

What do you want to do?


Learn more about Microsoft Query

Using Microsoft Query, you can connect to external data sources, select data from those external sources, import that data into your worksheet, and refresh the data as needed to keep your worksheet data synchronized with the data in the external sources.

Types of databases that you can access     You can retrieve data from several types of databases, including Microsoft Office Access, Microsoft SQL Server, and Microsoft SQL Server OLAP Services. You can also retrieve data from Excel workbooks and from text files.

Microsoft Office provides drivers that you can use to retrieve data from the following data sources (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.):

You can use also ODBC drivers (Open Database Connectivity (ODBC) driver: A program file used to connect to a particular database. Each database program, such as Access or dBASE, or database management system, such as SQL Server, requires a different driver.) or data source drivers (data source driver: A program file used to connect to a specific database. Each database program or management system requires a different driver.) from other manufacturers to retrieve information from data sources that are not listed here, including other types of OLAP databases. For information about installing an ODBC driver or data source driver that is not listed here, check the documentation for the database, or contact your database vendor.

Selecting data from a database     You retrieve data from a database by creating a query, which is a question that you ask about data stored in an external database. For example, if your data is stored in an Access database, you might want to know the sales figures for a specific product by region. You can retrieve a part of the data by selecting only the data for the product and region that you want to analyze.

With Microsoft Query, you can select the columns of data that you want and import only that data into Excel.

Updating your worksheet in one operation     Once you have external data in an Excel workbook, whenever your database changes, you can refresh (refresh: To update data from an external data source. Each time you refresh data, you see the most recent version of the information in the database, including any changes that were made to the data.) the data to update your analysis — without having to re-create your summary reports and charts. For example, you can create a monthly sales summary and refresh it every month when the new sales figures come in.

How Microsoft Query uses data sources     After you set up a data source for a particular database, you can use it whenever you want to create a query to select and retrieve data from that database — without having to retype all of the connection information. Microsoft Query uses the data source to connect to the external database and to show you what data is available. After you create your query and return the data to Excel, Microsoft Query provides the Excel workbook with both the query and data source information so that you can reconnect to the database when you want to refresh the data.

Diagram of how Query uses data sources

Using Microsoft Query to import data     to import external data into Excel with Microsoft Query, follow these basic steps, each of which is described in more detail in the following sections.

  1. Connect to a data source.
  2. Use the Query Wizard to define a query.
  3. Work with the data in Excel.

Top of Page Top of Page

Connect to a data source

What is a data source?     A data source is a stored set of information that allows Excel and Microsoft Query to connect to an external database. When you use Microsoft Query to set up a data source, you give the data source a name, and then supply the name and the location of the database or server, the type of database, and your logon and password information. The information also includes the name of an OBDC driver or a data source driver, which is a program that makes connections to a specific type of database.

To set up a data source by using Microsoft Query:

  1. On the Data tab, in the Get External Data group, click From Other Sources, and then click From Microsoft Query.
  2. Do one of the following:
  3. Double-click <New Data Source>.

-or-

Click <New Data Source>, and then click OK.

The Create New Data Source dialog box is displayed.

  1. In step 1, type a name to identify the data source.
  2. In step 2, click a driver for the type of database that you are using as your data source.

If the external database that you want to access is not supported by the ODBC drivers that are installed with Microsoft Query, then you need to obtain and install a Microsoft Office-compatible ODBC driver from a third-party vendor, such as the manufacturer of the database. Contact the database vendor for installation instructions.

 Note   OLAP databases do not require ODBC drivers. When you install Microsoft Query, drivers are installed for databases that were created by using Microsoft SQL Server Analysis Services. To connect to other OLAP databases, you need to install a data source driver and client software.

  1. Click Connect, and then provide the information that is needed to connect to your data source. For databases, Excel workbooks, and text files, the information that you provide depends on the type of data source that you selected. You may be asked to supply a logon name, a password, the version of the database that you are using, the database location, or other information specific to the type of database.

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.

  1. After you enter the required information, click OK or Finish to return to the Create New Data Source dialog box.
  2. If your database has tables (table: A collection of data about a particular subject that is stored in records (rows) and fields (columns).) and you want a particular table to display automatically in the Query Wizard, click the box for step 4, and then click the table that you want.
  3. If you don't want to type your logon name and password when you use the data source, select the Save my user ID and password in the data source definition check box. The saved password is not encrypted. If the check box is unavailable, see your database administrator to determine whether this option can be made available.

Security  Avoid saving logon information when connecting to data sources. This information may be stored as plain text, and a malicious user could access the information to compromise the security of the data source.

After you complete these steps, the name of your data source appears in the Choose Data Source dialog box.

Top of Page Top of Page

Use the Query Wizard to define a query

Use the Query Wizard for most queries     The Query Wizard makes it easy to select and bring together data from different tables and fields in your database. Using the Query Wizard, you can select the tables and fields that you want to include. An inner join (a query operation that specifies that rows from two tables are combined based on identical field values) is created automatically when the wizard recognizes a primary key field in one table and a field with the same name in a second table.

You can also use the wizard to sort the result set and to do simple filtering. In the final step of the wizard, you can choose to return the data to Excel, or further refine the query in Microsoft Query. After you create the query, you can run it in either Excel or in Microsoft Query.

To start the Query Wizard, perform the following steps.

  1. On the Data tab, in the Get External Data group, click From Other Sources, and then click From Microsoft Query.
  2. In the Choose Data Source dialog box, make sure that the Use the Query Wizard to create/edit queries check box is selected.
  3. Double-click the data source that you want to use.

-or-

Click the data source that you want to use, and then click OK.

Work directly in Microsoft Query for other types of queries     If you want to create a more complex query than the Query Wizard allows, you can work directly in Microsoft Query. You can use Microsoft Query to view and to change queries that you start creating in the Query Wizard, or you can create new queries without using the wizard. Work directly in Microsoft Query when you want to create queries that do the following:

  • Select specific data from a field     In a large database, you might want to choose some of the data in a field and omit data that you don't need. For example, if you need data for two of the products in a field that contains information for many products, you can use criteria (criteria: Conditions you specify to limit which records are included in the result set of a query or filter.) to select data for only the two products that you want.
  • Retrieve data based on different criteria each time you run the query     If you need to create the same Excel report or summary for several areas in the same external data — such as a separate sales report for each region — you can create a parameter query (parameter query: A type of query that, when you run it, prompts for values (criteria) to use to select the records for the result set so that the same query can be used to retrieve different result sets.). When you run a parameter query, you are prompted for a value to use as the criterion when the query selects records. For example, a parameter query might prompt you to enter a specific region, and you could reuse this query to create each of your regional sales reports.
  • Join data in different ways     The inner joins that the Query Wizard creates are the most common type of join used in creating queries. Sometimes, however, you want to use a different type of join. For example, if you have a table of product sales information and a table of customer information, an inner join (the type created by the Query Wizard) will prevent the retrieval of customer records for customers who have not made a purchase. Using Microsoft Query, you can join these tables so that all the customer records are retrieved, along with sales data for those customers who have made purchases.

To start Microsoft Query, perform the following steps.

  1. On the Data tab, in the Get External Data group, click From Other Sources, and then click From Microsoft Query.
  2. In the Choose Data Source dialog box, make sure that the Use the Query Wizard to create/edit queries check box is clear.
  3. Double-click the data source that you want to use.

-or-

Click the data source that you want to use, and then click OK.

Reusing and sharing queries     In both the Query Wizard and Microsoft Query, you can save your queries as a .dqy file that you can modify, reuse, and share. Excel can open .dqy files directly, which allows you or other users to create additional external data ranges from the same query.

To open a saved query from Excel:

  1. On the Data tab, in the Get External Data group, click From Other Sources, and then click From Microsoft Query. The Choose Data Source dialog box is displayed.
  2. In the Choose Data Source dialog box, click the Queries tab.
  3. Double-click the saved query that you want to open. The query is displayed in Microsoft Query.

If you want to open a saved query and Microsoft Query is already open, click the Microsoft Query File menu, and then click Open.

If you double-click a .dqy file, Excel opens, runs the query, and then inserts the results into a new worksheet.

If you want to share an Excel summary or report that is based on external data, you can give other users a workbook that contains an external data range, or you can create a template (template: A workbook that you create and use as the basis for other similar workbooks. You can create templates for workbooks and worksheets. The default template for workbooks is called Book.xlt. The default template for worksheets is called Sheet.xlt.). A template allows you to 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

Work with the data in Excel

After you create a query in either the Query Wizard or Microsoft Query, you can return the data to an Excel worksheet. The data then becomes an 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.) or a PivotTable report (PivotTable report: An interactive, crosstabulated Excel report that summarizes and analyzes data, such as database records, from various sources including ones external to Excel.) that you can format and refresh.

Formatting retrieved data     In Excel, you can use tools, such as charts or automatic subtotals, to present and to summarize the data retrieved by Microsoft Query. You can format the data, and your formatting is retained when you refresh the external data. You can use your own column labels instead of the field names, and add row numbers automatically.

Excel can automatically format new data that you type at the end of a range to match the preceding rows. Excel can also automatically copy formulas that have been repeated in the preceding rows and extends them to additional rows.

 Note   In order to be extended to new rows in the range, the formats and formulas must appear in at least three of the five preceding rows.

You can turn on this option (or off again) at any time:

  1. Click the Microsoft Office Button Button image, click Excel Options, and then click the Advanced category.
  1. In the Edit section, select the Extend data range formats and formulas check box to turn off this option. To turn off automatic data range formatting again, clear this check box.

Refreshing external data     When you refresh external data, you run the query to retrieve any new or changed data that matches your specifications. You can refresh a query in both Microsoft Query and Excel. Excel provides several options for refreshing queries, including refreshing the data whenever you open the workbook and automatically refreshing it at timed intervals. You can continue to work in Excel while data is being refreshed, and you can also check the status while the data is being refreshed. For more information, see Refresh connected (imported) data.

Top of Page Top of Page

 
 
Applies to:
Excel 2007