How to add header to a CSV file initially with PowerShell

Question

I want to add headers to a CSV file initially. The reason I want to add the headers initially is for some rows, some column values might be empty.

As per Microsoft documentation, the export-csv only takes headers/columns which is present in first row.

When you submit multiple objects to Export-CSV, Export-CSV organizes the file >based on the properties of the first object that you submit. If the remaining >objects do not have one of the specified properties, the property value of >that object is null, as represented by two consecutive commas. If the >remaining objects have additional properties, those property values are not >included in the file.

https://docs.microsoft.com/en-us/powershell/module/microsoft.powershell.utility/export-csv?view=powershell-6#notes

What I have tried so far:

$csvContents =@()

foreach ($item in $items) {
$row = New-Object System.Object # Create an object to append to the array
$row | Add-Member -MemberType NoteProperty -Name "header1" -Value "value1"
$row | Add-Member -MemberType NoteProperty -Name "header2" -Value "value2"
$row | Add-Member -MemberType NoteProperty -Name "header3" -Value "value3"
$csvContents += $row # append the new data to the array
}

$csvContents | Export-CSV -NoTypeInformation -Path $ResultCsvPath

The problem is for example Item1 may have only header1 and header3, means these columns are dynamic. So after export the result csv will only contain header1 and header3 only and header2 will be missing.

What I expect is that I want to add header1, header2, header3 initially.

Do you know all the headers from the $items object? If so just write them first with null values — Scepticalist
@Scepticalist Yes I tried that. it will create a dummy first row. I can use it as a workaround. I was just checking if there is actually some method to add headers properly. — Aswathy Santhosh

Theo Theo · Accepted Answer · 2019-11-01T10:50:52

With large collections this may take up some time, but here's something that might work for you:

Suppose this is your collection

$obj = @(
    [pscustomobject]@{
        'header1' = 'value1'
        'header3' = 'value3'
    },
    [pscustomobject]@{
        'header1' = 'value1'
        'header2' = 'value2'
    },
    [pscustomobject]@{
        'header3' = 'value3'
    },
    [pscustomobject]@{
        'header1' = 'value1'
        'header2' = 'value2'
        'header3' = 'value3'
    }
)

Then you can add the missing properties like:

# Try and find all headers by looping over all items.
# You could change this to loop up to a maximum number of items if you like.
# The headers will be captured in the order in which they are found. 
$headers = $obj | ForEach-Object {($_.PSObject.Properties).Name} | Select-Object -Unique

# Find the missing headers in the first item of the collection
# and add those with value $null to it.
$headers | Where-Object { ($obj[0].PSObject.Properties).Name -notcontains $_ } | ForEach-Object {
    $obj[0] | Add-Member -MemberType NoteProperty -Name $_ -Value $null
}

# output on console
$obj

# output to csv file
$obj | Export-Csv -Path 'D:\test.csv' -NoTypeInformation

Output:

header1 header3 header2
------- ------- -------
value1  value3         
value1          value2 
        value3         
value1  value3  value2

How to add header to a CSV file initially with PowerShell

3 Answers

Output (from CSV)