01 February 2011

Export SharePoint 2010 List to Excel with PowerShell

In the previous article I explained how to create a list item,
This time I will demonstrate how to export SharePoint List to Excel using PowerShell.
All we need to do, is to open the Site, the List and the Items, and then to export it to Excel.



Code:
$MyWeb = WebUrl
$MyList = MyWeb.Lists["ListName"]
$MyItems = $MyList.Items
$MyItems | Select Fields.. | Export-Csv
FilePath
$MyWeb.Dispose()

The list looks like this


The Excel sheet looks like this



9 comments:

Anonymous said...

What snapin needs to be registered for this script to work assuming this is not a sharepoint admin who is trying to do this but someone who has full privieleges within that list ?

Roi kolbinger said...

If I'm not mistaken, you must run the script at least with site collection administrator privileges

Anonymous said...

This is the privilege that i have on the sp website.
Design - Can create lists and document libraries and edit pages in the Web site.
I assume I cannot run the script with these privileges. Also how do i register the powershell snapin after installing powershell 2.0 ?

Roi kolbinger said...

In SharePoint2010 added the tool for that called "SharePoint 2010 PowerShell Management Shell".
You should run the PowerShell in this tool.

Anonymous said...

Hello,

I have a list with multiple columns (e.g. Title, Body, Metadata) and I would like to export it into a .CSV-file.
But when I type "Select Title,Body,Metadata", only the "Title"-field is filled with data, Body and Metadata are empty.

Do you have any solution for that?

Thanks in advance!

Roi kolbinger said...

You're right,

Field of type "Single line of text" - works.
A field of type "Metadata" and a field of type "Multiple lines of text" do not work.

I have no idea - I'll check

G0pinath said...

Has there been an update on this..
$MyLists = MyWeb.Lists["ListName"]
Foreach($mylist in $mylists){
$Mylist.Items | select title,body,metadata
}

Any idea if the above might work?

Roi kolbinger said...

I believe it will work

AvibaD said...

Thanks it works!

Post a Comment