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

image

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

image

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

image

  • Click Next and  select all tables.

image

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.

image

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

image

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

image

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

image

  • Click OK.
  • Click Finish.

image

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.

image

Copy and Paste from an External Excel Worksheet

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

image

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

image

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

image

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

 

Andrew Chan is the owner and founder of ALG Inc.

We help you to make better and faster decisions!

About Andrew Chan
Andrew Chan is an Business Consultant who gives you accurate, consistent and timely information so that you can make better and faster decisions. He is an Associate of Society of Actuaries with over 20 years of IT experience. Apart from strong analytical skills and proven technical background, he was also a former system director at Manulife who had extensive project management experience. If you are looking for someone to gather, consolidate, validate, visualize and analyze data, look no further! Andrew can provide the most cost effective business analytics solution so that you can explore, optimize, predict and visualize your business. Don’t guess on any decision, no matter it is finance, operation, marketing or sales! Always ask for evidence!

8 Responses to Microsoft PowerPivot for Excel 2010 – Importing Data

  1. Pingback: Why Microsoft PowerPivot? | Technologies and your business

  2. Pingback: Microsoft PowerPivot for Excel 2010 – Introduction | Technologies and your business

  3. Pingback: Excel 2010 – PowerPivot | Technologies and your business

  4. Pingback: Microsoft PowerPivot for Excel 2010 – Creating Relationships Among Data | Technologies and your business

  5. Pingback: Microsoft PowerPivot for Excel 2010 – Adding Slicer | Technologies and your business

  6. Pingback: Microsoft PowerPivot for Excel 2010 – Adding Pivot Chart | Technologies and your business

  7. Pingback: Microsoft PowerPivot for Excel 2010 – Adding Pivot Table | Technologies and your business

  8. Pingback: Microsoft PowerPivot for Excel 2010 – Data Analysis Expressions (DAX) | Technologies and your business

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: