Sdílet prostřednictvím

  • Článek

Add Data by using a Custom Query (Tutorial)

In this task, you will connect to a Microsoft Access database and use a custom query to import data into your PowerPivot workbook. An Access database is a relational data source. PowerPivot can import data from a variety of relational sources: SQL Server, Oracle, Sybase, Informix, DB2, Teradata, and more.

Importing data from the Access database involves connecting to the database, locating the query file, and running the query to import the data into your PowerPivot workbook.

Keep in mind that in order to use this data in your analysis, you will have to create relationships between the data from the Access database and the other data in your PowerPivot workbook. Instructions for creating relationships are provided in a later lesson. For more information, see Create Relationships between Tables (Tutorial).

Prerequisites

This topic is part of a PowerPivot for Excel tutorial, which should be completed in order. For information about prerequisites and the first step in the tutorial, see PowerPivot for Excel Tutorial Introduction.

To Create a Connection to an Access Database

  1. In the PowerPivot window, on the Home tab, click From Database and select From Access. This starts the Table Import Wizard which guides you through setting up a connection to a data source.

  2. In the Friendly Name box, type Product Category Access DB.

  3. In the Database name field, click Browse. Navigate to the location where you downloaded the sample files, select ProductCategories, and then click Open.

  4. Click Next.

Use a Query to Select the Data to Import

The procedure for using the Table Import Wizard to import by selecting from a list of tables was described in the previous lesson in this tutorial. So instead of duplicating those steps, you will use a custom query to import this set of Access data.

The Table Import wizard guides you through the steps. You can either import a query, copy and paste the text of an existing query, or write a new query by using the PowerPivot graphical query builder. In this lesson, you will import a query that you received from the IT department.

To Use the Query Designer Tool to Select the Data to Import

  1. Select the Write a query that will specify the data to import option, and then click Next.

  2. In the Friendly Query Name box, type Product Category Query.

  3. Click Design to open the Query Builder window.

  4. Click Import and navigate to the location on your computer where you saved the samples.

  5. If you do not see the file listed, click the file down arrow and select All files (*.*).

  6. Select SQLQuery and then click Open.

  7. The SQL Statement appears in the window. This query selects all data from the ProductCategory table except GAMES and TOYS and HOME APPLIANCES.

  8. Click OK, Validate, and then Finish. A summary of the columns you are importing is displayed.

  9. When the import is finished, click Close.

    The data is displayed as a new table named Product Category Query in your PowerPivot workbook. You can keep this data current by refreshing. If the contents of the Access database change, refreshing keeps your PowerPivot data up-to-date. For more information, see Different Ways to Update Data in PowerPivot.

  10. Rename the new table by right-clicking the Product Category Query tab and selecting Rename. Type ProductCategory and then click Enter.

Next Step

To continue this tutorial, go to the next topic: Add Data by using Copy and Paste (Tutorial).