Powershell System.Array to CSV file

Question

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.

Can you display the array to screen and read it ok? – TheMadTechnician Jun 19 '14 at 16:34 — TheMadTechnician, Jun 19 '14 at 16:34
Yes. Looks great when I pipe it to format table. – Acerbity Jun 19 '14 at 16:52 — Acerbity, Jun 19 '14 at 16:52

score 27 · Accepted Answer · answered Jun 19 '14 at 16:21

27

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

answered Jun 19 '14 at 16:21

Hunter Eidson

1,896
14
23

So what made this work? Just declaring the array before the loop? – Acerbity Jun 19 '14 at 17:21
More than likely, although if the loop was ONLY outputting the object, and you piped the loop into export-csv, I don't see why it wouldn't work, either. – Hunter Eidson Jun 19 '14 at 19:45
In his example, he's piping objects to Export-Csv. The problem you described, @HunterEidson, happens when you pipe from Format-Table/Format-List into an Export command, which doesn't work. – FoxDeploy Dec 12 '15 at 03:46
I was having a slightly different issue where it was outputting property types instead of values. Adding the `[pscustomobject]` cast to the `@{...}` object fixed the issue for me. – deadlydog May 28 '20 at 04:02

score 5 · Answer 2 · answered Dec 12 '15 at 03:45

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'.

When you use the |ft argument you remove the "Powershell Objects" are those are being formatted to be printed on screen. So if you're doing futher operation with the result of any powershell command do not use the FT or Format-Table in the pipeline. — Jose Ortega, Oct 13 '16 at 03:09

score 5 · Answer 3 · edited Apr 13 '16 at 20:38

5

Use Select-Object to prevent the bad CSV export.

Example:

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

edited Apr 13 '16 at 20:38

Paul Roub

36,322
27
84
93

answered Apr 13 '16 at 20:35

Monty Harris

59
1
5

Powershell System.Array to CSV file

3 Answers3

Linked