Pulling a Sharepoint 2007 list into Excel as Raw XML
Jul 11, 14I recently had a need to mash-up some data from a SharePoint 2007 list in an Excel document I was working on. I already knew that I could work with SharePoint 2007 data in Excel by using the following instructions from Microsoft:
- Do one the following on a SharePoint site:
Windows SharePoint Services 3.0
- 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.
- On the Actions menu
, click Export to Spreadsheet.
- If you are prompted to confirm the operation, click OK.
Windows SharePoint Services 2.0
- If your list is not already open, click Documents and Lists, and then click the name of your list.
- On the page that displays the list, under Actions, click Export to spreadsheet.
- In the File Download dialog box, click Open.
- 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.
- Do one of the following:
- If no workbook is open, Excel creates a new blank workbook and inserts the data as a table on a new worksheet.
- If a workbook is open, do the following in the Import Data dialog box that appears:
- Under Select how you want to view this data in your workbook, click Table, PivotTable Report, orPivotChart and PivotTable Report.
- 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.
- Click OK.
http://office.microsoft.com/en-us/excel-help/import-data-from-a-sharepoint-list-HA010131473.aspx
That’s all very well, but in my case I had an existing spreadsheet with quite a few tables and an existing excel model. I didn’t want to go through these gyrations. Isn’t there some way to just get the data right off the list as a web page?
Yes. Enter the “Import XML data” feature in Excel. MS Offers the following following guidance for getting XML data from a web service:
Import XML data from a Web service
To do the following procedure, you must have access to a server that is running pnSTS11. A default installation of pnSTS11 provides a data retrieval service for connecting to data in SharePoint lists. A SharePoint site administrator can install the Microsoft Office Web Parts and Components to add additional data retrieval services for Microsoft SQL Server and Microsoft Business Solutions. The installation program for Microsoft Office Web Parts and Components is available from the Downloads on Microsoft Office Online.
- On the Data menu, point to Import External Data, and then click Import Data.
- Do one of the following:
Create and open a new data source connection
- Select one of the following options:
- XML list in existing worksheet
The contents of the XML data file are imported into an XML list in the existing worksheet at the specified cell location.
- XML list in new worksheet
The contents of the file are imported into an XML list in a new worksheet starting at cell A1.
- If the XML data file does not refer to a schema, then Excel will infer the schema from the XML data file.
- To control the behavior of XML data, such as data binding, format, and layout, click Properties which displays the XML Map properties dialog box. For example, existing data in a mapped range will be overwritten when you import data by default, but you can change this.
But how to get the URL to call for the XML data? The following link holds the key:
http://{0}/_vti_bin/owssvr.dll?Cmd=Display&List={1}&View={2}&XMLDATA=TRUE
You have to replace {0} with your site URL and the {1} with your GUID for the list ID on SharePoint and {2} with the view GUID. How to get these? Well the easiest way (I think) is to go to your list, then select the view you want. Click view dropdown and select “Modify this view.” This will open the link to the screen where you can modify that view. The URL in the browser should have a View and List value. I stripped the %7B and %7D off as those are the { and } respectively. You don’t need to use a view and can omit that completely if you want to connect directly to the list. But I found I normally wanted to just get a certain view or I wanted to create a special view just for this activity.
Regardless, you can now follow the auto prompts to get the data into a spreadsheet. You can also open the developer tab and dive into the XML itself (see the MS link above for more instructions) and drag and drop the fields to other locations. Now when you get the data it will put it wherever you bound it.
By default it will dump it into a table and will include the schema items which you don’t need. You can open the Developer tab, select “Source” in the XML section and then just deselect the ns2 : Schema element. It will stop syncing those fields and you’ll have to clean up your table to get rid of them. I was able to rename the columns etc as needed, but since this is a read only feed I was typically using it to calculate other columns so I frequently stick with the ows_* columns that are the defaults for the data element.
Screenshot of the XML section from Developer tab in Excel:
Once I figured out I needed to find the XML import stuff for Excel, I was able to find lots of articles on it online to help. The following was my favorite and links to a lot of other ones.