Second Life of a Hungarian SharePoint Geek

July 20, 2017

How to Export a SharePoint List View to Excel Automatically Using PowerShell

Filed under: PowerShell, SP 2013, Tips & Tricks — Tags: , , — Peter Holpar @ 21:17

Note: This post is actually only a minor modification of the post I wrote recently about the URL of the Edit View page.

We can easily export the content of a SharePoint List View via the UI, simply by clicking the Export to Excel button on the ribbon:

image

You can achieve that automatically as well, for example from PowerShell.

Note: In the text below I describe the solution for a situation if you work locally on the server, but it is possible to apply the same technique to a remote solution as well, one should only transfer the code to the Managed Client Object Model.

Assume you have a list called YourList in a SharePoint site with URL http://YourSharePoint/Web/SubWeb.

It is easy to find out (for example, by monitoring the network traffic by Fiddler) that the URL generated when you click the Export to Excel button is like this:

http://YourSharePoint/Web/SubWeb/_vti_bin/owssvr.dll?CS=65001&Using=_layouts/15/query.iqy&List=%7B5315A0C9%2DAA6A%2D4598%2DA1D4%2D99B1BBCBF8C7%7D&View=%7B8E449E17%2D593C%2D4218%2DA4A4%2D43A8B47382BC%7D&RootFolder=%2FWeb%2FSubWeb%2FLists%2FYourList&CacheControl=1

The values of the List and View query string parameters are the encoded IDs of your list and list view respectively.

The following code generates the same URL from PowerShell:

$web = Get-SPWeb http://YourSharePoint/Web/SubWeb
$list = $web.Lists[‘YourList’]

# get the default view of the list
$view = $list.DefaultView
# or get an arbitrary view by its name
# $view = $list.Views[‘All Items’]
$viewId = $view.ID

function EscapeGuid($guid)
{
  return "{$guid}".ToUpper().Replace(‘-‘, ‘%2D’).Replace(‘{‘, ‘%7B’).Replace(‘}’, ‘%7D’)
}

$escapedListId = EscapeGuid $list.ID
$escapedViewId = EscapeGuid $view.ID
$escapedRootFolder = $list.RootFolder.ServerRelativeUrl.Replace(‘/’, ‘%2F’)

$url = $web.Url + "/_vti_bin/owssvr.dll?CS=65001&Using=_layouts/15/query.iqy&List=$escapedListId&View=$escapedViewId&RootFolder=$escapedRootFolder&CacheControl=1"

The URL above is actually no URL for the data or its schema, it’s a URL for a descriptor file (with the extension .iqy, see more about that here), that contains the URL for that list data and its schema.

The content of an .iqy file looks like this (you can capture it by Fiddler as well, or have a look at the content of the file we saved in our script further below) :

WEB
1
http://YourSharePoint/Web/SubWeb/_vti_bin/_vti_bin/owssvr.dll?XMLDATA=1&List={5315A0C9-AA6A-4598-A1D4-99B1BBCBF8C7}&View={8E449E17-593C-4218-A4A4-43A8B47382BC}&RowLimit=0&RootFolder=%2fWeb%2fSubWeb%2fLists%2fYourList

Selection={5315A0C9-AA6A-4598-A1D4-99B1BBCBF8C7}-{8E449E17-593C-4218-A4A4-43A8B47382BC}
EditWebPage=
Formatting=None
PreFormattedTextToColumns=True
ConsecutiveDelimitersAsOne=True
SingleBlockTextImport=False
DisableDateRecognition=False
DisableRedirections=False
SharePointApplication=http://YourSharePoint/Web/SubWeb/_vti_bin
SharePointListView={8E449E17-593C-4218-A4A4-43A8B47382BC}
SharePointListName={5315A0C9-AA6A-4598-A1D4-99B1BBCBF8C7}
RootFolder=/Web/SubWeb/Lists/YourList

The URL we have in line 3 refers to the endpoint that returns the data schema and the data itself.. Based on this information, Excel can import and display the data of the list view.

Let’s save the file from the URL of the .icq file we have already from the first script, and start Excel to open the list view data. The script below assumes the extension .iqy is associated with Excel in your system:

$path = "C:\temp\owssvr.iqy"

$request = [System.Net.WebRequest]::Create($url)
$request.UseDefaultCredentials = $true
$request.Accept = "text/html, application/xhtml+xml, */*"

$response = $request.GetResponse()
$reader = New-Object System.IO.StreamReader $response.GetResponseStream()
$data = $reader.ReadToEnd()

$writer = [System.IO.StreamWriter] $path
$writer.WriteLine($data)
$writer.Close()

# the .iqy file will be opened by Excel
Invoke-Expression $path
# optionally delete the file
# Remove-Item $path

Advertisements

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Create a free website or blog at WordPress.com.

%d bloggers like this: