PowerShell Export-Csv Picks Up Last Row Only

Question

I know this question has been asked before, but I'm having a really hard time applying the solutions of others to my situation. Please provide both the conceptual and technical (code) explanations to your answer as I need to understand how this works so I don't have to ask again for different scenarios. :)

Question: How do I get this to export all the rows in my PSObject, and why is it currently only exporting the last row? (please remember I'm only on PS 2.0)

$d = Get-SPDatabase | Sort-Object DiskSizeRequired -desc
$d | %{
    #Report
    $t = New-Object PSObject
    $t | Add-Member NoteProperty "Size (MB)"([string]("{0:N0}" -f ($_.DiskSizeRequired / 1MB)) + " MB")
    $t | Add-Member NoteProperty "Database"($_.Name)
    Write-Output $t
}
#Save Report to Tab Delimited File on the Desktop
$t | Export-Csv ("{0}\Desktop\SP DB Sizes ({1}).txt" -f $Env:UserProfile, (Get-Date -Format "yyyy-MM-dd")) -Delimiter `t -Encoding UTF8 -NoTypeInformation

The above is a SharePoint specific script, but I expect the same concepts should apply to any situation involving a PSObject for outputting tabular data. Yes, I want to both write the output to the console as well as a file.

You do realize you `write-output $t` for each item, but never pipe that or save that, so the only one left is the final one? — Eris, Jul 01 '16 at 00:32
Hmmm, I see what you mean, but I'm not sure how to fix it which is why I'm here. I've tried many things, but nothing has worked so far. — Chiramisu, Jul 01 '16 at 00:37

Eris · Answer 1 · 2016-07-01T02:12:51.947

1

As I said in the comment the value of $t is never being saved in an array or on the pipeline.

So to fix this, I'm going to assume you mean to see the values, and have the values on the pipeline only go to Export-Csv. I don't have powershell 2.0 available to test, but I know HashTables are available

$d = Get-SPDatabase | Sort-Object disksizerequired -desc
$d | %{
    #Report
    # We don't really need a PSObject, since it's just a hashtable/dictionary anyway
    $t = @{ 
     "Size (MB)" = '{0:N0} MB' -f ($_.DiskSizeRequired / 1MB)
     "Database" = $_.Name
    }
    # Write to pipeline
    Write-Output $t
    # Write to console host
    Write-Host $t
} | # move pipe here, which will feed the pipeline output to the next non-commented command
#Save Report to Tab Delimited File on the Desktop
Export-Csv ("{0}\Desktop\SP DB Sizes ({1}).txt" -f $Env:UserProfile, (Get-Date -Format "yyyy-MM-dd")) -Delimiter `t -Encoding UTF8 -NoTypeInformation

edited Jul 01 '16 at 02:12

answered Jul 01 '16 at 00:47

Eris

7,378
1
30
45

OK, this seems to be in the right direction, but the `Write-Host` line just spits out a bunch of arrays rather than the tabular formatted style I'm aiming for. – Chiramisu Jul 01 '16 at 01:09
@Chiramisu I changed it to use a hashtable instead, which should write correctly, however, I don't have powershell 2.0 available to test at the moment. – Eris Jul 01 '16 at 02:37
I get back `System.Collections.DictionaryEntry System.Collections.DictionaryEntry` in host, and a lot of extra fields with some `Hashtable+KeyCollection` and other data. Do you think it's better to not use the `PSObject`? I read to use that on TechNet, but I'm open to other options. ;) – Chiramisu Jul 01 '16 at 22:10
I would just index directly into the object you get back. It's a powershell cmdlet, so they already did the work for you. – Eris Jul 01 '16 at 22:29
Thank you kindly for your feedback. I've added my own solution inspired by yours. ^.^ – Chiramisu Jul 02 '16 at 01:05

score 0 · Answer 2 · answered Jul 02 '16 at 01:01

After a LOT of playing around (and learning more about PS ;) I settled on the following solution. Thanks to @Eris for getting me pointed in the right direction.

$t = @() #Reporting Table
$d = Get-SPDatabase | Sort-Object DiskSizeRequired -desc
$d | %{
    #Report
    $t += New-Object PSObject -Property @{
        "Size (MB)" = "{0:N0} MB" -f ($_.DiskSizeRequired / 1MB)
        "Database" = $_.Name
    } | Select "Size (MB)","Database"
}
$t
#Save Report to Tab Delimited File on the Desktop
$t | Export-Csv ("{0}\Desktop\SP DB Sizes ({1}).txt" -f $Env:UserProfile, (Get-Date -Format "yyyy-MM-dd HH-mm-ss")) -Delimiter `t -Encoding UTF8 -NoTypeInformation

Note: It may not be the best performing solution (and I'm open to suggestions on that), but it generates the output I want both in the console and the file. ;)

PowerShell Export-Csv Picks Up Last Row Only

2 Answers2