In many cases, importing relational data through the Power Pivot add-in is faster and more efficient than doing a simple import in Excel.
Generally, it's this easy to do:
-
Check with a database administrator to get database connection information and to verify you have permission to access the data.
-
If the data is relational or dimensional, then—from within Power Pivot—click Home > Get External Data > From Database.
Optionally, you can import from other data sources:
-
Click Home > From Data Service if the data is from Microsoft Azure Marketplace or an OData data feed.
-
Click Home > Get External Data > From Other Sources to choose from the entire list of data sources.
On the Choose How to Import the Data page, choose whether to take all the data in data source or to filter the data. Either choose tables and views from a list, or write a query that specifies which data to import.
The advantages of a Power Pivot import include the ability to:
-
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.
-
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.
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 that have be updated in the external database since the time of the last import. Only new rows and existing columns will refresh. If you need to add a new column to the model, you’ll need to import it using the steps given above.
A refresh simply repeats the same 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, the refresh will fail. Of course, you still retain any 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.
Typically, permissions are necessary to refresh the data. If you share the workbook with others who also want to refresh the data, they will require at least read-only permissions on the database.
The method by which you share your workbook will determine whether data refresh can occur. For Microsoft 365, you cannot refresh data in a workbook that is saved to Microsoft 365. On SharePoint Server, you can schedule unattended data refresh on the server, but it is necessary that Power Pivot for SharePoint has been installed and configured in your SharePoint environment. Contact your SharePoint administrator to see if a scheduled data refresh is available.
You can import data from one of the many data sources given in the table below.
Power Pivot doesn’t install the providers for each data source. While some providers may already exist on your computer, you may need to download and install the provider that you need.
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.
Consider the following with respect to data providers:
-
You can also use the OLE DB Provider for ODBC.
-
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.
|
Source |
Versions |
File type |
Providers |
|---|---|---|---|
|
Access databases |
Microsoft Access 2003 or later. |
.accdb or .mdb |
ACE 14 OLE DB provider |
|
SQL Server relational databases |
Microsoft SQL Server 2005 or later; Microsoft Azure SQL 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 or later |
(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 filesConnect to a Flat File |
(not applicable) |
.txt, .tab, .csv |
ACE 14 OLE DB provider for Microsoft Access |
|
Microsoft Excel files |
Excel 97-2003 or later |
.xlsx, .xlsm, .xlsb, .xltx, .xltm |
ACE 14 OLE DB provider |
|
Power Pivot workbookImport Data from Analysis Services or Power Pivot |
Microsoft SQL Server 2008 R2 or later |
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 cubeImport Data from Analysis Services or Power Pivot |
Microsoft SQL Server 2005 or later |
(not applicable) |
ASOLEDB 10 |
|
Data feedsImport Data from a Data Feed (used to import data from Reporting Services reports, Atom service documents, 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 reportsImport Data from a Reporting Services Report |
Microsoft SQL Server 2005 or later |
.rdl |
|
|
Office Database Connection files |
.odc |
Published server documents—such as Access databases already published to SharePoint—cannot be imported.
Need more help?
You can always ask an expert in the Excel Tech Community or get support in Communities.
