Use Microsoft Query to retrieve external data

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:

• Microsoft SQL Server Analysis Services (OLAP provider)

• Microsoft Office Access

• dBASE

• Microsoft FoxPro

• Microsoft Office Excel

• Oracle

• Paradox

• Text file databases

You can use also ODBC drivers or data source drivers 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 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.

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.

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.

   Note: Excel 365 has moved Microsoft Query into the Legacy Wizards menu group.  This menu is not shown by default.  To enable, go to File, Options, Data, and enable in the Show legacy data import wizards section.

2. Do one of the following:

   • To specify a data source for a database, text file, or Excel workbook, click the Databases tab.

   • To specify an OLAP cube data source, click the OLAP Cubes tab. This tab is available only if you ran Microsoft Query from Excel.

3. Double-click <New Data Source>.

   -or-

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

   The Create New Data Source dialog box is displayed.

4. In step 1, type a name to identify the data source.

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

   Notes: 

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

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

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

   Important: 

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

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

7. After you enter the required information, click OK or Finish to return to the Create New Data Source dialog box.

8. If your database has tables 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.

9. 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 Note: 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.

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

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 or a PivotTable report 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.

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

1. Click File > Options > Advanced.

2. In the Editing options section, select the Extend data range formats and formulas check. 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 an external data connection in Excel.

Top of Page