Add Data by Using the Table Import Wizard (Tutorial)
Looking for help with Power Pivot in Excel 2013? Go to Power Pivot Help on Office.com.
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 PowerPivot for Excel Tutorial Introduction.
To create a connection to an Access database
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.
In the Excel window, on the PowerPivot tab, click PowerPivot Window.
In the PowerPivot window, on the Home tab, click From Database, and then click From Access. The Table Import Wizard launches and guides you through setting up a connection to a data source.
In the Friendly connection name box, type ContosoDB from Access.
To the right of the Database name box, click Browse. Navigate to the location where you downloaded the sample files, select ContosoSales, click Open, and then click Next to continue.
Verify that Select from a list of tables and views to choose the data to import is selected, and then click Next to display a list of all the source tables within the database.
Select the check box for the following tables: DimChannel, DimDate, DimProduct, DimProductSubcategory, and FactSales. (Do not yet close the Table Import Wizard.)
Now that you have connected to the database and selected the tables to import, go to the next section below, 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.
To filter the table data prior to importing
Select the row for the DimProduct table, and then click Preview & Filter. The Preview Selected Table dialog box opens with all the columns in the DimProduct table displayed.
Scroll to the right, and clear the checkboxes at the top of the columns for all the columns, from ClassID through StockTypeName (a total of 15 columns; stop before UnitCost), and then click OK.
Notice that the words Applied filters appear in the Filter Details column in the DimProduct row. If you click on that link, you will see a textual description of the filters that you just applied.
Now select the row for DimProductSubcategory, and then click Preview & Filter.
Since you are interested in only some of the products, you will apply a filter so that you import only the data for these categories.
Scroll to the right. At the top of the ProductCategoryKey column, click the arrow on the right side of the cell, scroll down, deselect 7 and 8, and then click OK. Categories 7 and 8 include games and home appliances, and you do not want to include those in your analysis. The arrow in that cell changes to a filter icon.
At the top of the ProductSubcategoryDescription column, clear the checkbox on the left side of the cell. Since the descriptions are almost identical to the names, you do not need to import both columns, and eliminating unnecessary columns makes your workbook smaller and easier to navigate. 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 Relationships Between Tables.
To import the selected table and column data
Review your selections. If everything looks okay, click Finish.
While it imports the data, the wizard displays how many rows have been fetched. When all the data has been imported, a message appears that indicates success. Notice that you imported more than 2 million rows from the FactSales table alone.
Click Close.
The wizard closes, and then the data appears in the PowerPivot window. 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 Different Ways to Update Data in PowerPivot.
Next Step
To continue this tutorial, go to the next topic: Add Data by Using a Custom Query (Tutorial).
See Also
Tasks
Add Data to Your PowerPivot Workbook (Tutorial)