I’ve only been playing around with Powershell for a day or so and I’m sure this is a basic question… but I’m struggling to work out how to achieve this.
I’ve been trying to work out a script to pull up a list of all the files on a SharePoint site above a certain size (5mb). I want this list to be exported to a csv file which contains the document’s:
Title
Path
File size
I found a script on the internet which successfully lists the files within the powershell window, but when I’ve tried to manipulate the code to make it export to csv I’ve just broken everything. Here’s what I started with:
I can follow that, but unfortunately I don’t know enough about Powershell yet to manipulate it in the way I want to. My strategy was to try to store the results in an array variable and then export the variable as a csv at the end but as soon as I did this (using “get-item”) my powershell console just showed a sea of angry red writing to let me know that I suck
If anyone could help out it’d save me from a real headache.
I can, there isn’t anything too different to be honest. Like I said I’m not really sure on exactly how to do this…
Here, I was just trying to dump everything into the variable $Results and then show the result as an output. I figure if I can do that it may not be too hard to export the variable as a csv:
The bit that I changed starts at “$Results”, left the rest out as it’s all the same.
# Enumerate though all site collections, sites, sub sites and document libraries in a SP web app
if($spWeb -ne $null)
{
$Results = @()
$Results += foreach ($siteColl in $spWeb.Sites)
{
foreach($subWeb in $siteColl.AllWebs)
{
foreach($List in $subWeb.Lists)
{
if($List.BaseType -eq “DocumentLibrary”)
{
$ItemsColl = $List.Items
foreach ($item in $ItemsColl)
{
$itemSize = (($item.File.Length)/1024)/1024
if($itemSize -Ge $fileSize)
{
get-item $item
}
}
}
}
}
}
}
Write-Host $Results
Write-Host “---------DONE---------”
Stop-SPAssignment -Global
First, anything output by “Write-Host” goes to the screen, and can’t be redirected anywhere else. Like, a CSV. You want to use Write-Output.
Second, there’s no actual reason to accumulate your results in a variable ($Results) and then output it at the end. That’s exactly what the PowerShell pipeline does. For example, suppose the following is in a script named MyScript.ps1:
[CmdletBinding()]
Param()
# Enumerate though all site collections, sites, sub sites and document libraries in a SP web app
if($spWeb -ne $null)
{
foreach ($siteColl in $spWeb.Sites)
{
foreach($subWeb in $siteColl.AllWebs)
{
foreach($List in $subWeb.Lists)
{
if($List.BaseType -eq "DocumentLibrary")
{
$ItemsColl = $List.Items
foreach ($item in $ItemsColl)
{
$itemSize = (($item.File.Length)/1024)/1024
if($itemSize -Ge $fileSize)
{
get-item $item
}
}
}
}
}
}
}
Stop-SPAssignment -Global
The only thing here producing output is Get-Item, which is writing it to the pipeline. I could then run:
./MyScript.ps1 | Export-CSV whatever.csv
And the output of the script would be converted to a CSV and written to disk. That’s because Get-Item doesn’t produce nasty, unusable text; it produces objects that have properties, and it writes them to the pipeline. That makes the output usable by other PowerShell commands.
Write-Host is kinda bad.
PowerShell, as a scripting language, is markedly different in some key concepts from nearly any other scripting language you’ll run across. It’s worth picking up a short book (I’m partial to the two in a Month of Lunches PowerShell books, naturally) to cover some of those differences, because they’re hard to discover on your own. Without knowing them, though, you can really head off down a bad path.
BTW, if the above is causing errors, then we’d kinda need to see an example of the error message to be helpful. I’m assuming the code you posted worked, but didn’t do what you wanted.
Notice that in the above, I’ve added [CmdletBinding()]. That enables two switches -Debug and -Verbose. So, in your code, you could add Write-Debug statements and Write-Verbose statements. E.g.:
$ItemsColl = $List.Items
foreach ($item in $ItemsColl)
Write-Debug "Item is $item"
If you run the script:
./MyScript.ps1 -Debug
You enable the Write-Debug statements (similarly, -Verbose enables Write-Verbose in your script) and you can have an easier time troubleshooting. Generally, errors happen when a variable contains something other than what you thought it contained, so taking the time to display the contents of variables as the script runs can let you verify them and find your mistake.
