Format CSV file output using powershell

Question

I wrote the following code that stores certificates details in a csv file. It works but in each line of the csv file the symbols @{ at the beginning and } at the end are written.also in the first column which contains the headers the symbol = is also written. So I did not know how populate my csv file without these symbols

Here is the code that I wrote

$StartDate = Get-Date
 $CertPath = 'Cert:\LocalMachine\'

  $CertsDetail =  Get-ChildItem -Path $CertPath -Recurse | Where-Object {$_.PsIsContainer -ne $true } | ForEach-Object {                               
   $DaysLeft = (New-TimeSpan -Start $StartDate -End $_.NotAfter).Days
   If ($DaysLeft -lt 30) {
    $Under30 = $true
    $Text = "The Certificate is but valid about to expire"
}
Else {
    $Under30 = $false
}
If ($DaysLeft -lt 1) {
    $Expired = $true
    #$Not_Expired = $false
    $Text = "The Certificate is expired"
}
Else {
    $Expired = $false
    #$Not_Expired = $true
    $Text = "The Certificate is still valid and not going soon to expire"
}
[pscustomobject]@{Text=$Text;`
                Subject = $_.Subject;`
                ExpireDate = $_.NotAfter;`
                DaysRemaining = $DaysLeft;`
                Under30Days = $Under30;`
                Expired = $Expired;`
                #Not_Expired = $Not_Expired
                }

                }
         $obj = [PSCustomObject] @{
         'Example Header 1' = $null
         'Example Header 2' = $null
         'Example Header 3' = $null
         'Example Header 4' = $null 
         'Example Header 5' = $null 
         'Example Header 6' = $null 


$obj | Add-Content -Path 'C:\Users\hanna\Desktop\certificate.csv'
$CertsDetail | Add-Content -Path 'C:\Users\hanna\Desktop\certificate.csv'

[1] remove the entire $obj section & the line that starts with $obj | Add-Content. [2] change the $CertsDetail | line to use Export-CSV instead of Add-Content. [3] read the help from Get-Help Export-CSV ... [grin] — Lee_Dailey
– Lee_Dailey, Commented May 19, 2019 at 19:55

Benjamin Hubbard · Accepted Answer · 2019-05-19 17:39:12Z

1

Try something like this:

$StartDate = Get-Date
$CertPath = 'Cert:\LocalMachine\'
$CertsDetail = Get-ChildItem -Path $CertPath -Recurse | 
    Where-Object { $_.PsIsContainer -ne $true } | ForEach-Object {                               
    $DaysLeft = (New-TimeSpan -Start $StartDate -End $_.NotAfter).Days
    if ($DaysLeft -lt 1) {
        $Under30 = $true
        $Expired = $true
        $Text = "The Certificate is expired"
    }
    elseif ($DaysLeft -lt 30) {
        $Under30 = $true
        $Expired = $false
        $Text = "The Certificate is but valid about to expire"
    }
    else {
        $Under30 = $false
        $Expired = $false
        $Text = "The Certificate is still valid and not going soon to expire"
    }
    [PSCustomObject]@{
        Text = $Text
        Subject = $_.Subject
        ExpireDate = $_.NotAfter
        DaysRemaining = $DaysLeft
        Under30Days = $Under30
        Expired = $Expired
    }
}
$CertsDetail | Export-Csv -Path 'C:\Users\hanna\Desktop\certificate.csv'

answered May 19, 2019 at 17:39

Benjamin Hubbard

2,92524 silver badges29 bronze badges

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

3 Comments

mamadou Over a year ago

It works but there still two things : 1-> #TYPE System.Management.Automation.PSCustomObject appears in the head of the file 2-> Expire Date written in a hidden format(######) Also I want to know how to fit the size of columns to the content

Razorfen Over a year ago

To get rid of the #TYPE System.Management.Automation.PSCustomObject just add -NoTypeInformation to your Export-Csv like so: Export-Csv -Path 'C:\Users\hanna\Desktop\certificate.csv' -NoTypeInformation

Benjamin Hubbard Over a year ago

If you resize the column (assuming you are viewing the CSV in Excel), does that allow you to view the Expire Date?

Collectives™ on Stack Overflow

Format CSV file output using powershell

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related