Import Data from a Data Feed

Looking for help with Power Pivot in Excel 2013? Go to Power Pivot Help on Office.com.

Data feeds are one or more XML data streams that are generated from an online data source and streamed to a destination document or application. In PowerPivot for Excel, you can import a data feed into your PowerPivot workbook by using the Table Import Wizard. After you import a data feed into a workbook, you can use it later in any data refresh operations that you schedule. 

This topic contains the following sections:

Prerequisites

Choose an import approach

Import data feeds from public or corporate data sources

Import data feeds from SharePoint lists

Import data feeds from Reporting Services Reports

Prerequisites

The feed must be an Atom feed. RSS feeds are not supported.

The feed must either be publicly available or you must have permission to connect to it under the Windows account you are currently logged in as.

To export a SharePoint list as a data feed, the SharePoint server from which you are importing data must have ADO.NET Data Services 3.5 SP1. For more information, refer to Determine Hardware and Software Requirements (SharePoint 2010). In Software Requirements, find the software prerequisites list and click the link for ADO.NET Data Services 3.5 that corresponds to the operating system you are using (either Windows Server 2008 SP2 or Windows Server 2008 R2).

Choose an import approach

Data is added once during import and placed into the PowerPivot workbook. To get updated data from the feed, you can either refresh the data from PowerPivot for Excel, or configure a data refresh schedule for the workbook after it is published to SharePoint. For more information, see Different Ways to Update Data in PowerPivot.

You can use any of the following approaches to import data feeds to a PowerPivot workbook.

Import or Export from Azure DataMarket

You must have a Windows Live ID in order to use data from Azure DataMarket. If you are new to Azure DataMarket, you can choose from a variety of free data sources to learn the steps.

Start from Azure DataMarket

1. Go to the Azure DataMarket Web site. Log in using your Windows Live ID.

2. On first use, be sure to fill in account information and create an account ID that you will use when importing or exporting to Excel.

3. Click Data.

4. Under Price, click Free to list just those data sources that are free of charge.

5. Click the data source. For most data sources, including free ones, you will need to sign up to use it. Click the link to sign up.

6. On the data source page, look for Explore this Data. Most pages have a link with this name, that when clicked, opens a query window.

7. Run the query.

8. Click Export.

9. In Export to Program, choose Excel PowerPivot.

10. When prompted to open or save the document, click Open. When asked to create a new workbook (Book1), click Yes. Excel opens, loads the PowerPivot add-in, opens the PowerPivot window, and starts the Table Import Wizard, with the data feed URL already specified.

11. You will be asked to enter an Account ID. You can find the Account ID on the My Account page on Azure DataMarket.

Start from PowerPivot for Excel

1. In Excel, open the PowerPivot window.

2. In Get External Data, click From Azure DataMarket.

3. Click View available DataMarket datasets. This takes you to the Data page of the Azure DataMarket site. You can now browse and sign up for free or purchased datasets that you can import directly into PowerPivot for Excel.

Import data feeds from public or corporate data sources

You can access public feeds or build custom data services that generate Atom feeds from proprietary or legacy database systems.

1. In the PowerPivot window, in the Home tab, click From Data Feeds. The Table Import wizard opens.

2. Type a descriptive name for the feed you are accessing. If you are importing multiple feeds or data sources, using descriptive names for the connection can help you remember how the connection is used.

3. Type the address for the data feed. Valid values include the following:

   1. An XML document that contains the Atom data. For example, the following URL points to a public feed on the Open Government Data Initiative web site:

      http://ogdi.cloudapp.net/v1/dc/banklocations/
      

   2. An .atomsvc document that specifies one or more feeds. An .atomsvc document points to a service or application that provides one or more feeds. Each feed is specified as a base query that returns the result set.

      You can specify a URL address to an .atomsvc document that is on a web server or you can open the file from a shared or local folder on your computer. You might have an .atomsvc document if you saved one to your computer while exporting a Reporting Services report, or you might have .atomsvc documents in a data feed library that someone created for your SharePoint site. For more information, see Import Data from a Reporting Services Report or Manage PowerPivot Data Feeds on the Microsoft Web site.

      Note

      Specifying an .atomsvc document that can be accessed through a URL address or shared folder is recommended because it gives you the option of configuring automatic data refresh for the workbook later, after the workbook is published to SharePoint. The server can re-use the same URL address or network folder to refresh data if you specify a location that is not local to your computer.

4. Click Test Connection to make sure the feed is available. Alternatively, you can also click Advanced to confirm that the Base URL or Service Document URL contains the query or service document that provides the feed.

5. Click Next to continue with the import.

6. In the Select Tables and Views page of the wizard, in the Friendly Name field, replace Data Feed Content with a descriptive name that identifies the table that will contain this data after it is imported

7. Click Preview and Filter to review the data and change column selections. You cannot restrict the rows that are imported in the report data feed, but you can remove columns by clearing the check boxes. Click OK.

8. In the Select Tables and Views page, click Finish.

9. When all rows have been imported, click Close.

Import data feeds from SharePoint lists

In SharePoint 2010, you can import any SharePoint list that has an Export as Data Feed button on the ribbon. You can click this button to export the list as a feed. If you have Excel 2010 and PowerPivot for Excel installed, the PowerPivot window will launch in response to the data feed export. If you already have a PowerPivot workbook open, you have the option to add the feed to the existing workbook or to create a new one. If you do not have a workbook open, a new one will be created.

Alternatively, you can start with PowerPivot for Excel and specify the lists you want to import. Use the following instructions to learn how.

1. In the PowerPivot window, in the Home tab, click From Data Feeds. The Table Import wizard opens.

2. Type a descriptive name for the feed you are accessing. If you are importing multiple feeds or data sources, using descriptive names for the connection might help you remember how the connection is used.

3. In Data Feed URL, type an address to the list data service, replacing <server-name> with the actual name of your SharePoint server:

   http://<server-name>/_vti_bin/listdata.svc
   

4. Click Test Connection to make sure the feed is available. Alternatively, you can also click Advanced to confirm that the Service Document URL contains an address to the list data service.

5. Click Next to continue with the import.

6. In the Select Tables and Views page of the wizard, select the lists you want to import. For example, if you want to import metadata about all the documents in PowerPivot Gallery, you could select PowerPivot Gallery from the list.

   Note

   You can only import lists that contain columns.

7. Click Preview and Filter to review the data and change column selections. You cannot restrict the rows that are imported in the report data feed, but you can remove columns by clearing the check boxes. Click OK.

8. In the Select Tables and Views page, click Finish.

9. When all rows have been imported, click Close.

Import data feeds from Reporting Services Reports

You can use the new Atom rendering extension to generate a data feed from an existing report. You must have Excel 2010 with PowerPivot for Excel on your workstation. PowerPivot will launch in response to the data feed export, automatically adding and creating relationships between tables and columns as they are streamed in.

Alternatively, in the PowerPivot window, on the Home tab, in the Get External Data group, click the **From Report **button. For more information about how to import a data feed from a Reporting Services report, see Import Data from a Reporting Services Report.

See Also

Concepts

Data Sources Supported in PowerPivot Workbooks

Different Ways to Update Data in PowerPivot

Other Resources

Add and Maintain Data in PowerPivot