Add Data by using the Table Import Wizard (Tutorial)

  • Article

In this lesson, you will use the Table Import Wizard to connect to a database, select the data to load, and import that data into your PowerPivot workbook.

PowerPivot can import data from a variety of relational sources: Access, Oracle, Sybase, Informix, DB2, Teradata, and more. The process for importing data from each of these relational sources is very similar to what is described below.

Additionally, data can be loaded using a custom query or stored procedure. You will do this in the next lesson.

Keep in mind that in order to use this data in your analysis, you must create relationships between the data from the relational 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 Create Your First PowerPivot Workbook (Tutorial).

Create a Connection to an Access Database

  1. Navigate to the location on your computer where you downloaded the samples and double-click Stores. You will use the stores data in a later lesson.

  2. In the Excel window, on the PowerPivot tab, click PowerPivot Window.

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

  4. In the Friendly connection name field, type ContosoDB from Access.

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

  6. Click Next and verify that Select from a list of tables and views to choose the data to import is selected.

  7. You want to select from a list of tables and views, so click Next to display a list of all the source tables within the database.

  8. Select the check box for the following tables: DimChannel, DimDate, DimProduct, DimProductSubcategory, and FactSales.

Now that you have connected to the database and selected the tables to import, go to the next section headed Filter the Table Data prior to Importing.

Filter the Table Data prior to Importing

The FactSales and DimProduct tables that you are importing from the Access database contain a subset of the data from the original SQL Server Contoso database: sales and products from two categories (games and home appliances) are not included. You will apply a filter to one of the other tables before importing it, so that you eliminate the same categories. You will also filter out some of the columns from the DimProduct table.

Filter the Table Data prior to Importing

  1. Select the row for the DimProduct table and click Preview & Filter.

  2. The Preview Selected Table window opens with all the columns in the DimProduct table displayed.

  3. Clear the checkboxes at the top of the columns for all the columns from ClassID through StockTypeName (a total of 15 columns) and then click OK.

  4. Notice that the words Applied filters are now displayed in the Filter Details column in the DimProduct row; if you click on that link you’ll see a textual description of the filters that you just applied.

  5. Now select the row for DimProductSubcategory and click Preview & Filter.

  6. Since you’re interested in only some of the products, you will apply a filter so that you import only the data for these categories.

    At the top of the ProductCategoryKey column, click the arrow and deselect 7 and 8. Categories 7 and 8 include games and home appliances, and you don't want to include those in your analysis.

  7. At the top of the ProductSubcategoryDescription column, clear the checkbox. Since the descriptions are almost identical to the names, there is no need to import both columns, and eliminating unnecessary columns will make your workbook smaller. Click OK.

Import the Selected Table and Column Data

Finally, import the selected data. The wizard imports the table relationships along with the table data. For more information about relationships, see Creating Relationships Between Tables.

Import the Selected Table and Column Data

  1. Review your selections. If everything looks OK, click Finish.

    While importing the data, the wizard displays how many rows have been fetched. When all the data has been imported, a message indicating success is displayed. Notice that you imported more than 2 million rows from the FactSales table alone.

  2. Click Close.

    The wizard closes and the PowerPivot window is visible. Each table has been added as a new tab in the PowerPivot window. If the data changes at the source, you can keep the data imported into the PowerPivot window up-to-date by using data refresh. For more information, see Refreshing or Changing Imported Data.

Next Step

To continue this tutorial, go to the next topic: Add Data by using a Custom Query (Tutorial).

See Also

Other Resources

Creating Relationships Between Tables

Import Data from a Database

Adding Data to PowerPivot

Add Data to Your PowerPivot Workbook (Tutorial)

Install PowerPivot for Excel

Table Import Wizard

Recalculating Formulas