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
– TheMadTechnician

2014-06-19 16:34:29 +00:00
Commented Jun 19, 2014 at 16:34 — TheMadTechnician
– TheMadTechnician, Commented Jun 19, 2014 at 16:34
Yes. Looks great when I pipe it to format table.

Acerbity
– Acerbity

2014-06-19 16:52:26 +00:00
Commented Jun 19, 2014 at 16:52 — Acerbity
– Acerbity, Commented Jun 19, 2014 at 16:52

Hunter Eidson · Accepted Answer · 2014-06-19 16:21:12Z

29

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, 2014 at 16:21

Hunter Eidson

1,91914 silver badges23 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Acerbity Over a year ago

So what made this work? Just declaring the array before the loop?

Hunter Eidson Over a year ago

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.

FoxDeploy Over a year ago

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.

deadlydog Over a year ago

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.

Paul Roub · Accepted Answer · 2016-04-13 20:38:22Z

7

Use Select-Object to prevent the bad CSV export.

Example:

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

edited Apr 13, 2016 at 20:38

Paul Roub

36.5k27 gold badges88 silver badges95 bronze badges

answered Apr 13, 2016 at 20:35

Monty Harris

791 silver badge5 bronze badges

1 Comment

Krantz Over a year ago

This answer is not clear.

FoxDeploy · Accepted Answer · 2015-12-12 03:45:59Z

5

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

answered Dec 12, 2015 at 3:45

FoxDeploy

13.8k3 gold badges42 silver badges58 bronze badges

1 Comment

Jose Ortega Over a year ago

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.

Collectives™ on Stack Overflow

Powershell System.Array to CSV file

3 Answers 3

4 Comments

1 Comment

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

4 Comments

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related