Getting Sharepoint List Version Data Into Excel: Iqy, Powershell, And Xml Options
Microsoft Excel is a versatile data analysis and visualization tool used by millions worldwide. For SharePoint administrators and power users who manage data in SharePoint lists, getting that list data into Excel for further manipulation and reporting is a common need.
Fortunately, SharePoint offers several built-in options for exporting list data into Excel spreadsheets. The three main methods covered in this guide are:
- iqy files – Simple text files that contain parameters for querying list data.
- PowerShell scripts – For advanced users, scripts can extract and shape data for Excel.
- XML export – SharePoint has an in-box XML converter for list exports.
This guide will provide an overview of how each export method works, sample code and usage examples, and tips for optimizing SharePoint-to-Excel data integration.
Exporting SharePoint List Data with iqy Files
What are iqy files and how do they work?
IQY, short for Internet Query files, are simple text documents that contain parameters for querying data. When loaded in Excel, these iqy files connect with the specified data source (in this case, SharePoint lists) and display the dynamically refreshed content within the spreadsheet.
The benefits of iqy exports include:
- Simple human-readable file format requiring only Notepad
- Refreshes data every time the Excel workbook is loaded
- Easy to customize columns, filters, sorting in the export
- Works with SharePoint OnPrem and SharePoint Online
Creating an iqy file to export SharePoint list data
The basic components of an .iqy file are:
- WEB command – Specifies SharePoint URL as data source
- TABLE command – The list GUID along with columns to export
- ROW command – Filters and sort orders configured here
Using these elements, we can build out custom iqy files tailored to any SharePoint list we want to export. For example:
“`
WEB
1
https://my.sharepoint.com
“`
First, we declare the 1 for the data source index, followed by the actual SharePoint site URL.
“`
TABLE
1
{E7390E6B-AC17-4FFE-BFCC-BF991A2BA5C4}
ID,Title,Status,AssignedTo,Created,DueDate
“`
Next, refer back to the data source index while specifying the list GUID enclosed in curly brackets, followed by column names separated by commas.
“`
ROW
1
ID>=1
SORTBY DueDate
“`
Finally, filter to records with ID >=1 and sort results by the DueDate column ascending.
Once saved as an .iqy file, opening it in Excel will initiate the SharePoint data pull, populating cells dynamically.
Sample iqy file code and usage
Full sample .iqy exporting Tasks list data:
“`
WEB
1
https://contoso.sharepoint.com
“`
“`
TABLE
1
{94A3B714-32D7-4BE8-BE21-FA0A02672EA8}
ID,Title,AssignedTo,Status,Priority,DueDate,Predecessors
“`
“`
ROW
1
Status<>Completed
SORTBY Priority,DueDate
“`
To use this:
- Copy text to Tasks.iqy file using Notepad
- Upload .iqy to a SharePoint Document Library
- Open .iqy while in Excel to pull latest tasks data
The steps above create a reusable iqy file to consistently export filtered, sorted SharePoint Tasks list data into Excel for reporting.
Using PowerShell to Export SharePoint List Data
PowerShell basics for exporting SharePoint data
PowerShell provides unmatched capabilities for working with SharePoint content and metadata at scale. While more complex than iqy exports, with scripting we can query, shape, combine, export, and pipe SharePoint list data in advanced ways.
Key aspects of SharePoint-PowerShell integration:
- Load the SharePoint Online client assembly using Add-PSSnapin
- Get context to site collections with Get-SPOSite cmdlet
- Pass context down to query lists, like Get-SPOTListItems
- Filter, sort, select columns with native commands
- Export output to CSV for easy Excel usage
Sample PowerShell scripts to export list data
Standard script template to export a list:
“`Powershell
Add-PSSnapin microsoft.sharepoint.powershell
$siteUrl = “https://contoso.sharepoint.com/sites/team”
$listName = “Tasks”
$context = Get-SPOSite $siteUrl
$list = Get-SPOTList -Name $listName -Connection $context
$items = Get-SPOTListItems -Connection $context -List $list
$items | Select Title,AssignedTo,DueDate,Status | Export-CSV ./$listName-Export.csv -NoTypeInformation
“`
This script connects to the Tasks lists, exports name, assigned user, status, and deadline to a CSV formatted for Excel.
Customizing PowerShell exports for different data needs
We can customize the simple script above to handle more advanced scenarios.
**Export only incomplete tasks**
“`Powershell
$items = Get-SPOTListItems -Connection $context -List $list | Where-Object {$_.Status -ne “Completed”}
“`
**Export data across multiple lists**
“`Powershell
$list1 = Get-SPOTList -Name List1 -Connection $context
$list2 = Get-SPOTList -Name List2 -Connection $context
$items = Get-SPOTListItems -Connection $context -List $list1
$items += Get-SPOTListItems -Connection $context -List $list2
“`
**Export looked up metadata**
“`Powershell
$items | Select Title,@{Label=”Assignee”;Expression={$_.AssignedTo.LookupValue}},DueDate,Status | Export-CSV ./Export.csv
“`
These examples demonstrate how flexible PowerShell exports can meet the needs of advanced SharePoint list reporting in Excel.
Exporting SharePoint List Data as XML
Understanding SharePoint’s built-in XML export
In addition to iqy and PowerShell, SharePoint has native support for exporting list data as XML files, which Excel can import and convert.
Benefits of SharePoint’s OOTB XML export include:
- Works for all SharePoint lists without customization
- Handles export of lookups, persons, managed metadata etc.
- Allows re-import back into SharePoint after Excel manipulation
However, the fixed schema is less customizable than iqy or scripts.
Enabling XML export on a SharePoint list
Exporting a list as XML only takes a few clicks:
- Navigate to the SharePoint list
- Click the List tab on the ribbon
- Select Export to Excel in the middle Data group
- Choose Export list data source as XML Document
- Save generated .xml file to your computer
Now we have our SharePoint list exported as an XML document ready for Excel import.
Importing XML exports into Excel
Once generated, importing the XML file into Excel is straightforward:
- Open a blank Excel workbook
- Select Data tab on the ribbon
- Choose Get External Data > Import from XML options
- Browse to and select the SharePoint XML export file
A new sheet will be created with the SharePoint data converted into an Excel table ready for analysis.
Recommended Practices for SharePoint-Excel Integrations
When to use each export method
- iqy – Simple scheduled refreshes of filtered SharePoint data, lists with custom columns
- PowerShell – Advanced business logic, combining data from multiple lists, automation
- XML export – Ad hoc analysis of a single list without much filtering or shaping
Optimizing exports for Excel usage
Best practices for smooth data analysis include:
- Export only the columns you need, avoid information overload
- Use views and filters to export targeted datasets
- Structure data exports in tabular format (no rich text etc.)
- Automate frequent exports to refresh key reports
Refreshing live data vs. static exports
- Static exports provide snapshot in time for historical analysis
- Live connected data requires SharePoint permissions and connectivity
- Balance data freshness with flexibility to manipulate
Choose file-based or live exports according to the end goal for Excel usage.
