Newbie question - Trouble exporting results to csv

Hi everyone,

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:

(all credit to the blogger at http://captechconsulting.com/blog/amarender-peddamalku/find-list-large-files-sharepoint-2010)

cls
if((Get-PSSnapin | Where {$_.Name -eq “Microsoft.SharePoint.PowerShell”}) -eq $null)
{
Add-PSSnapin Microsoft.SharePoint.PowerShell;
}
Start-SPAssignment -Global
#Change the site url below
$Site = Get-SPSite xxxxxxxxxxxxxxxxx
$spWeb = $Site.WebApplication
#Enter the target file size in MB
$fileSize = 5
[string]$fileUrl
Write-Host “------Checking the SP web app for large files------”

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)
{
$itemUrl = $item.Web.Url + “/” + $item.Url;
Write-Host $itemUrl ", File size:: " $(’{0:N2}’ -f $itemSize) MB -ForegroundColor Green
}
}
}
}
}
}
}
Write-Host “---------DONE---------”
Stop-SPAssignment -Global

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 :stuck_out_tongue:

If anyone could help out it’d save me from a real headache.

Thanks in advance!

  • Emma

Hi Emma,

Can you show the code that you’ve attempted so far?

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

So… a couple of things.

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.