Import Data from a Data Feed

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. 

Note

In Windows Vista and Windows 7, features in the PowerPivot window are available on a ribbon, which is discussed in this topic. In Windows XP, features are available from a set of menus. If you are using Windows XP and want to see how the menu commands relate to the ribbon commands, see The PowerPivot UI in Windows XP.

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.

Application

Approach

Link

PowerPivot for Excel

Click From Data Feeds to import a feed from an Atom service document or a single data feed from a service or application that generates feeds in the Atom format.

Use this approach to import feeds from external or internal services or applications that provide Atom feeds over an HTTP connection.

How do I…

SharePoint 2010 lists

Export all or part of a SharePoint list as an Atom data feed.

How do I…

Reporting Services

Export all or part of a report running on a SQL Server 2008 R2 report server as an Atom data feed.

How do I…

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.

Note

The Export as Data Feed button appears in the Library Tools ribbon on sites for which the PowerPivot feature has been activated. A SharePoint administrator can activate the feature for you if it not available. For more information, see Activate PowerPivot Integration for Site Collections on the Microsoft Web site.

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

If you have a deployment of SQL Server 2008 R2 Reporting Services, 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.