Get data using the Power Pivot add-in

Importing relational data through the Power Pivot add-in is often a faster and more efficient alternative to importing in Excel.

  1. Enable Power Pivot in Microsoft Excel 2013 add-in and open a Power Pivot window.
  2. Check with a database administrator to get database connection information and to verify you have permission to access the data.
  3. In Power Pivot, click Home > Get External Data > From Database if the data is relational or dimensional.

Alternatively, import from other data sources:

  • Click Home > From Data Service if the data is from Microsoft Azure Marketplace or an OData data feed. Optionally, choose Suggest Related Data Microsoft Azure Marketplace can suggest data that is related to your data, based on categories Power Pivot has assigned to your data. See Getting suggestions of related data from DataMarket for details.
  • Click Home > Get External Data > From Other Sources to choose from the full list of supported data sources.

On the Choose How to Import the Data page you choose whether to take all the data in data source or to filter the data, either by selecting tables and views from a list or writing a query that specifies which data to import.

The advantages of importing through Power Pivot are that you can:

  • Filter out unnecessary data to import just a subset.
  • Rename tables and columns as you import data.
  • Paste in a predefined query to select the data that it returns.

Tips for choosing data sources

  • OLE DB providers can sometimes offer faster performance for large scale data. When choosing between different providers for the same data source, you should try the OLE DB provider first.
  • Importing tables from relational databases saves you steps because foreign key relationships are used during import to create relationships between worksheets in the Power Pivot window.
  • Importing multiple tables, and then deleting the ones you don't need, may save you steps. If you import tables one at a time, you might still need to create relationships between the tables manually.
  • Columns that contain similar data in different data sources are the basis of creating relationships within the Power Pivot window. When using heterogeneous data sources, choose tables that have columns that can be mapped to tables in other data sources that contain identical or similar data.
  • To support data refresh for a workbook that you publish to SharePoint, choose data sources that are equally accessible to both workstations and servers. After publishing the workbook, you can set up a data refresh schedule to update information in the workbook automatically. Using data sources that are available on network servers makes data refresh possible.

Get data from other sources

Get data from Analysis Services

Get data from a data feed

Get data from Microsoft Azure Marketplace

Connect to a Flat File from Power Pivot

Add worksheet data to a Data Model using a linked table

Copy and paste rows into a Data Model in Power Pivot

Refresh relational data

In Excel, click Data > Connections > Refresh All to reconnect to a database and refresh the data in your workbook.

Refresh will update individual cells and add rows introduced into the external database since the last import. Only rows and existing columns are refreshed; if you want to add a new column to the model, you’ll need to import it using the steps provided earlier in this article.

Refresh re-runs the query used to import the data. If the data source is no longer at the same location, or if tables or columns are removed or renamed, refresh will fail, but you will still have the data that you previously imported. To view the query used during data refresh, click Power Pivot > Manage to open the Power Pivot window. Click Design > Table Properties to view the query.

Permissions are typically required to refresh data. If you share the workbook with others who also want to refresh the data, they also need read permissions on the database.

Remember that how you share your workbook will determine whether data refresh can occur. On Office 365, you cannot refresh data in a workbook that is saved to Office 365. On SharePoint Server 2013, you can schedule unattended data refresh on the server, but doing so requires that Power Pivot for SharePoint 2013 is installed and configured in your SharePoint environment. Check with your SharePoint administrator to find out if scheduled data refresh is available.

Top of Page Top of Page

Supported data sources

Import data from the data sources in the following table. Power Pivot in Microsoft Excel 2013 doesn’t install the providers listed for each data source. Some providers might already be installed with other applications on your computer; in other cases, you may need to download and install the provider.

You can also link to tables in Excel and copy and paste data from applications like Excel and Word that use an HTML format for the Clipboard. For more information, see Add Data by Using Excel Linked Tables and Copy and Paste Data to Power Pivot.

Source Versions File type Providers 1
Access databases Microsoft Access 2003, 2007, 2010, 2013. .accdb or .mdb ACE 14 OLE DB provider
SQL Server relational databases Microsoft SQL Server 2005, 2008, 2008 R2, SQL Server 2012, SQL Server 2012 SP1; Microsoft SQL Azure Database (not applicable)

OLE DB Provider for SQL Server

SQL Server Native Client OLE DB Provider

SQL Server Native 10.0 Client OLE DB Provider

.NET Framework Data Provider for SQL Client

SQL Server Parallel Data Warehouse (PDW) SQL Server 2008 R2, SQL Server 2012, SQL Server 2012 SP1 (not applicable) OLE DB provider for SQL Server PDW
Oracle relational databases Oracle 9i, 10g, 11g. (not applicable)

Oracle OLE DB Provider

.NET Framework Data Provider for Oracle Client

.NET Framework Data Provider for SQL Server

MSDAORA OLE DB provider 2

OraOLEDB

MSDASQL

Teradata relational databases Teradata V2R6, V12 (not applicable)

TDOLEDB OLE DB provider

.Net Data Provider for Teradata

Informix relational databases    (not applicable) Informix OLE DB provider
IBM DB2 relational databases 8.1 (not applicable) DB2OLEDB
Sybase relational databases    (not applicable) Sybase OLE DB provider
Other relational databases (not applicable) (not applicable) OLE DB provider or ODBC driver
Text files
Connect to a Flat File
(not applicable) .txt, .tab, .csv ACE 14 OLE DB provider for Microsoft Access
Microsoft Excel files Excel 97-2003, 2007, 2010, 2013 .xlsx, .xlsm, .xlsb, .xltx, .xltm ACE 14 OLE DB provider
Power Pivot workbook
Import Data from Analysis Services or Power Pivot
Microsoft SQL Server 2008 R2, SQL Server 2012, SQL Server 2012 SP1 Analysis Services xlsx, .xlsm, .xlsb, .xltx, .xltm

ASOLEDB 10.5

(used only with Power Pivot workbooks that are published to SharePoint farms that have Power Pivot for SharePoint installed)

Analysis Services cube
Import Data from Analysis Services or Power Pivot
Microsoft SQL Server 2005, 2008, 2008 R2, SQL Server 2012, , SQL Server 2012 SP1 Analysis Services (not applicable) ASOLEDB 10

Data feeds
Import Data from a Data Feed

(used to import data from Reporting Services reports, Atom service documents, Microsoft Azure Marketplace DataMarket, and single data feed)

Atom 1.0 format

Any database or document that is exposed as a Windows Communication Foundation (WCF) Data Service (formerly ADO.NET Data Services).

.atomsvc for a service document that defines one or more feeds

.atom for an Atom web feed document

Microsoft Data Feed Provider for Power Pivot

.NET Framework data feed data provider for Power Pivot

Reporting Services reports
Import Data from a Reporting Services Report
Microsoft SQL Server 2005, 2008, 2008 R2, SQL Server 2012, SQL Server 2012 SP1 Reporting Services .rdl
Office Database Connection files    .odc   

1You can also use the OLE DB Provider for ODBC.

2 In some cases, using the MSDAORA OLE DB provider can result in connection errors, particularly with newer versions of Oracle. If you encounter any errors, we recommend that you use one of the other providers listed for Oracle.

Top of Page Top of Page

Unsupported sources

The following data source is not currently supported:

  • Published server documents, such as Access databases already published to SharePoint, cannot be imported.

Top of Page Top of Page

 
 
Applies to:
Excel 2013, Power Pivot in Excel 2013