Microsoft PowerPivot for Excel 2010 – Importing Data

PowerPivot can import data from database (Access, Oracle, Sybase, Informix, DB2, Teradata and more), Excel, OLAP Cube, report, text file, and data feed.  In this blog, you would find step by step demonstration on how to import data from an Access database and Excel worksheet into your PowerPivot.  You can download the sample data if you want to follow the steps.

Importing Data From an Access Database

• In the PowerPivot window, click From Database and select From Access. This launches the Table Import Wizard.

• In the Friendly connection name field, type Contoso Sales.
• In the Database name field, enter the location of your sample Access data file, ContosoSales.
• Click Next and verify that Select from a list of tables and views to choose the data to import is selected.

• Click Next and  select all tables.

You don’t have to import all data, you can choose what columns and rows to be imported.

• Select the row for the DimProduct table and click Preview & Filter.
• The Preview Selected Table window opens with all the columns in the dbo_DimProduct table displayed.
• 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.

• Notice that the words Applied filters are now displayed in the Filter Details column in the dbo_DimProduct row.

• Now select the row for dbo_DimProductSubcategory and click Preview & Filter.
• At the top of the ProductCategoryKey column, click the arrow and deselect 7 and 8.

• At the top of the ProductSubcategoryDescription column, clear the checkbox.

• Click OK.
• Click Finish.

At the end of import process, you can see 6 tables were imported and dbo_FactSales has nearly 4 million records.

• Click Close, and you will see all 6 tables in your PowerPivot Windows.

Copy and Paste from an External Excel Worksheet

• Open ContosoStoreData Excel file from your sample directory.
• Highlight and copy cells A1 through I307.

• Back in your PowerPivot window, on the Home tab, click Paste.

• The Paste Preview dialog displays the new table that will be created. Change the Table Name to Stores, select Use first row as column headers, and click OK.

• In the Excel window, click the File tab.
• Click Save As. .
• The Save As dialog box opens.  In the File name text box, type PowerPivotTutorialSample and then click Save.

Now data is imported into your PowerPivot worksheet and you are ready to define the relationships among data which I am going to cover in my next blog.

 

• Microsoft PowerPivot for Excel 2010 – Data Analysis Expressions (DAX)
• Microsoft PowerPivot for Excel 2010 – Adding Slicer
• Microsoft PowerPivot for Excel 2010 – Adding Pivot Chart
• Microsoft PowerPivot for Excel 2010 – Adding Pivot Table
• Microsoft PowerPivot for Excel 2010 – Creating Relationships Among Data
• Microsoft PowerPivot for Excel 2010 – Importing Data
• Why Microsoft PowerPivot?
• Microsoft PowerPivot for Excel 2010 – Introduction
• Excel 2010 – PowerPivot

Andrew Chan is the owner and founder of ALG Inc.

We help you to make better and faster decisions!