CODEWITHSUNDEEP
powershellcsv
Acerbity
Acerbity

Reputation: 527

Powershell System.Array to CSV file

I am having some difficulty getting an Export-Csv to work. I am creating an array like this...

[pscustomobject] @{
    Servername = $_.Servername
    Name = $_.Servername
    Blk = ""
    Blk2 = ""
    Method = "RDP"
    Port = "3389"
}

The issue I have is when I try to export that to a CSV I get garbage that looks like this...

"9e210fe47d09416682b841769c78b8a3",,,,,

I have read a ton of articles addressing this issue, but I just don't understand how to get the data right.

Upvotes: 14

Views: 98761

Answers (3)

Monty Harris
Monty Harris

Reputation: 79

Use Select-Object to prevent the bad CSV export.

Example:

Get-Services | Select-Object * | export-csv -Path C:\log.csv

Upvotes: 7

FoxDeploy
FoxDeploy

Reputation: 13537

This happens when you try to pipe out from any of the Format-* commands.

The Format-List, Format-Table and Format-Wide cmdlets are special in PowerShell, in that they're meant to consume the pipeline, and transform it for display in the console. So, you can't pipe from FL, FT or FW into Export-csv. As Don Jones says "Format On the Right".

Don't believe me? Observe, as I run Get-Process, send it through Format-Table and then convert to Csv.

gps | ft | ConvertTo-Csv
#TYPE Microsoft.PowerShell.Commands.Internal.Format.FormatStartData
"ClassId2e4f51ef21dd47e99d3c952918aff9cd","pageHeaderEntry","pageFooterEntry","autosizeInfo","shapeInfo","groupingEntry"
"033ecb2bc07a4d43b5ef94ed5a35d280",,,,"Microsoft.PowerShell.Commands.Internal.Format.TableHeaderInfo",
"9e210fe47d09416682b841769c78b8a3",,,,,
"27c87ef9bbda4f709f6b4002fa4af63c",,,,,
"27c87ef9bbda4f709f6b4002fa4af63c",,,,,
"27c87ef9bbda4f709f6b4002fa4af63c",,,,,

It's even the same string! Why do we get this string? I really wish I knew, but I think it has something to do with the way the Format-* commands convert objects into text instructions for display in the console.

If you REALLY love the way your Format-Table looks, send it to Out-File, which is the only way to redirect the output of a Format-* command.

Another message is to use Tee-Object to dump a copy of your pipe to a variable, and then send that out to Export.

Get-Process | Tee-Object -Variable ExportMe | Format-Table
$exportMe | export-Csv .\Export.csv

I call this the 'have your cake and eat it too approach'.

Upvotes: 5

Hunter Eidson
Hunter Eidson

Reputation: 1909

For testing, I built a CSV file w/ the servernames, and read it in, and the following works in PS4:

$serverList = import-csv "datafile.csv"

$AllObjects = @()

$serverList | ForEach-Object {
    $AllObjects += [pscustomobject]@{
        Servername = $_.Servername
        Name = $_.Servername
        Blk = ""
        Blk2 = ""
        Method = "RDP"
        Port = "3389"
    }
}

$AllObjects | Export-Csv -Path "outfile.csv" -NoTypeInformation

Upvotes: 28

Related Questions