How to send output of Powershell script to CSV

Question

I have been given the following script to connect to an Azure subscription, get all the certificates and output some of the certificate properties. The script works fine,
What I am trying to do is send the output into a CSV file so I can sort by certificate expiry-date (for example) and manipulate the output into spreadsheet reports (for instance).

Powershell Script

Set-AzureSubscription 'MySubscription'
Select-AzureSubscription 'MySubscription'

$enc = [system.Text.Encoding]::UTF8
$azcerts = Get-AzureService | Get-AzureCertificate
foreach ($azcert in $azcerts) {      
    $bytes = $enc.GetBytes($azcert.data) 
    $cert = new-object System.Security.Cryptography.X509Certificates.X509Certificate -ArgumentList @(,$bytes)    
    $subjectstring = $cert.Subject
    $expirydate = $cert.GetExpirationDateString()
    $servicename = $azcert.ServiceName
    $thumprint = $azcert.Thumbprint
    $cnstring = $subjectstring -replace "(CN=)(.*?),.*",'$2'    
    Write-Output "$servicename, $cnstring, $expirydate, $thumprint"    }

What I have tried...

When I try to replace the last line in the for-each loop with this I get a useless CSV file with only one column "#TYPE System.String"

"$servicename, $cnstring, $expirydate, $thumprint" | Export-Csv "outputfile.csv" -append -NoTypeInformation

Might want to look at the Format-Custom, Format-List, Format-Table and Format-Wide Cmdlets. I'm sure using one of those you'll get your format in the layout you want to import into Excel. — Simon W
– Simon W, Commented Aug 14, 2014 at 5:20

Andy Arismendi · Accepted Answer · 2014-08-14 04:31:10Z

1

You've made you're own CSV lines in the loop so you can just write to disk:

"$servicename, $cnstring, $expirydate, $thumprint" | Out-File -FilePath output.csv -Append -Encoding ASCII

before the loop write your headers:

"servicename, cnstring, expirydate, thumprint" | Out-File -FilePath output.csv -Encoding ASCII

For Export-Csv or ConvertTo-Csv to work you'd need to pipe a collection of objects with these fields as properties. You could make such a collection using New-Object:

New-Object -TypeName PsObject -Property @{servicename=$servicename;cnstring=$cnstring <ETC...>}

answered Aug 14, 2014 at 4:31

Andy Arismendi

53.1k17 gold badges113 silver badges130 bronze badges

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

Collectives™ on Stack Overflow

How to send output of Powershell script to CSV

Powershell Script

What I have tried...

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

Powershell Script

What I have tried...

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related