Newbie question - Trouble exporting results to csv

This topic contains 4 replies, has 3 voices, and was last updated by Profile photo of Don Jones Don Jones 1 year, 9 months ago.

  • Author
    Posts
  • #22499
    Profile photo of Emma O'Donnell
    Emma O’Donnell
    Participant

    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 😛

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

    Thanks in advance!

    – Emma

  • #22500
    Profile photo of Tim Pringle
    Tim Pringle
    Participant

    Hi Emma,

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

  • #22501
    Profile photo of Emma O'Donnell
    Emma O’Donnell
    Participant

    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

  • #22504
    Profile photo of Don Jones
    Don Jones
    Keymaster

    So... a couple of things.

    First, anything output by "Write-Host" goes [i]to the screen[/i], 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 [i]in a Month of Lunches[/i] 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.

  • #22505
    Profile photo of Don Jones
    Don Jones
    Keymaster

    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.

You must be logged in to reply to this topic.