Import data from a SharePoint list

You can import and analyze data from a list on a Windows SharePoint Services 3.0 or Windows SharePoint Services 2.0 site. For example, you can import a parts inventory list from a SharePoint site and then create a PivotChart report in Microsoft Office Excel 2007 to summarize and compare the data.

In this article

Overview

Import data from a SharePoint list

Update data from a SharePoint list

Overview

To import data from a SharePoint site, you must have Office Excel 2007 installed on the computer where you are working with the SharePoint list.

When you import data from a SharePoint site to Office Excel 2007, you can analyze it in Office Excel 2007, but any changes that you make in Excel are not reflected in the list on the SharePoint site.

To use an Office program to manipulate data from a list on a SharePoint site and have those changes reflected on the SharePoint site, you can use Microsoft Office Access 2007 or a solution that uses Visual Basic for Applications (VBA).

You can also manipulate data from a SharePoint list in a workbook that is saved as an Excel 97-2003 workbook (.xls format). However, if you save an Excel 2003 workbook as an Office Excel 2007 workbook (.xlsx format), only a read-only connection is available from the SharePoint list.

As the list continues to be updated on the site, you can update your workbook with the latest data from the SharePoint site. When you update your data from the SharePoint site, the original data from the SharePoint site and any changes that you made in Office Excel 2007 are overwritten.

In Office Excel 2007, you can import data into an existing worksheet or create a new worksheet. If you have folders in your list on the SharePoint site, the structure does not appear on the resulting Excel worksheet.

When you import data from a SharePoint list with a read-only connection to the list, the Item Type and Path columns are added to the data on the worksheet. These columns enable you to filter and sort the data, based on its type and location on the site — for example, if it was located in a subfolder of the list.

To import data from a list on a SharePoint site, you must have a connection to the SharePoint site and permission to read the list.

Top of Page

Import data from a SharePoint list

You can import data from lists on a Windows SharePoint Services 3.0 or Windows SharePoint Services 2.0 site. When you import data from a SharePoint list, some steps differ, depending on which version is running on your SharePoint site.

How to tell which version is running on the SharePoint site

• On a Windows SharePoint Services 3.0 site, a menu with your user name or account may appear in the upper-right corner.

• On a Windows SharePoint Services 2.0 site, the top-level navigation in the upper-left corner may contain links for Home, Documents and Lists, and Help.

  Note: Your site might be customized by your site owner or administrator. If you are not sure which version you are using, see the site owner or administrator.

1. Do one the following on a SharePoint site:

   Windows SharePoint Services 3.0

   1. If your list is not already open, click its name on the Quick Launch. If the name of your list doesn't appear, click View All Site Content, and then click the name of your list.

   2. On the Actions menu , click Export to Spreadsheet.

   3. If you are prompted to confirm the operation, click OK.

      Windows SharePoint Services 2.0

   4. If your list is not already open, click Documents and Lists, and then click the name of your list.

   5. On the page that displays the list, under Actions, click Export to spreadsheet.

2. In the File Download dialog box, click Open.

3. If you are prompted whether to enable data connections on your computer, click Enable if you believe the connection to the data on the SharePoint site is safe to enable.

4. Do one of the following:

   1. If no workbook is open, Excel creates a new blank workbook and inserts the data as a table on a new worksheet.

   2. If a workbook is open, do the following in the Import Data dialog box that appears:

      1. Under Select how you want to view this data in your workbook, click Table, PivotTable Report, or PivotChart and PivotTable Report.

      2. Under Where do you want to put the data, click Existing worksheet, New worksheet, or New workbook.

         If you click Existing worksheet, click the cell where you want to place the upper-left corner of the list.

      3. Click OK.

Top of Page

Update data from a SharePoint list

As people continue to update the list on the SharePoint site, you can update the data on your worksheet with the latest changes. Doing this overwrites the data that was originally imported from the SharePoint list and any changes that you made to the data in Excel.

1. Click anywhere in the table that you want to update.

2. On the Design tab, in the External Table Data group, click Refresh.

Tip: If you no longer want a connection between your worksheet and the SharePoint site, you can unlink the table. On the Design tab, in the External Table Data group, click Unlink.

Top of Page