How to Create CSV Files with Headers in PowerShell?

CSV files are commonly used to store and exchange tabular data. By adding headers to your CSV files, you can provide meaningful names for each column, making the data more readable and easier to understand. In this tutorial, I will explain how to create CSV (Comma-Separated Values) files with headers using PowerShell.

Table of Contents

Create a CSV File with Headers using PowerShell

Let me show you an example of how to create a CSV file with headers in PowerShell. Suppose you have a list of employees in the USA, and you want to export their information to a CSV file. Here’s how you can achieve this:

$employees = @(
    [PSCustomObject]@{Name='John Doe'; Age=35; City='New York'}
    [PSCustomObject]@{Name='Sarah Johnson'; Age=28; City='Los Angeles'}
    [PSCustomObject]@{Name='Michael Smith'; Age=42; City='Chicago'}
)

$employees | Export-Csv -Path 'employees.csv' -NoTypeInformation

In this example:

We create an array of PSCustomObject instances representing the employees. Each object has properties like Name, Age, and City.
We use the Export-Csv cmdlet to export the employee objects to a CSV file named ’employees.csv’. The -NoTypeInformation parameter is used to exclude type information from the output.

The resulting ’employees.csv’ file will have the following content:

"Name","Age","City"
"John Doe",35,"New York"
"Sarah Johnson",28,"Los Angeles"
"Michael Smith",42,"Chicago"

As you can see, the CSV file includes headers for each column, making the data more descriptive and self-explanatory.

I executed the above PowerShell script, and you can see the exact output in the screenshot below:

Create a CSV File with Headers using PowerShell

Check out Create a Table with Headers in PowerShell

Specify Custom Headers in CSV File using PowerShell

In some cases, you may want to specify custom headers for your CSV file. PowerShell allows you to do this using the -Header parameter of the Export-Csv cmdlet. Let’s modify the previous example to use custom headers:

$employees = @(
    [PSCustomObject]@{FirstName='John'; LastName='Doe'; Age=35; City='New York'}
    [PSCustomObject]@{FirstName='Sarah'; LastName='Johnson'; Age=28; City='Los Angeles'} 
    [PSCustomObject]@{FirstName='Michael'; LastName='Smith'; Age=42; City='Chicago'}
)

$headers = 'EmployeeName', 'EmployeeAge', 'Location'
$employees | Select-Object @{Name='EmployeeName';Expression={$_.FirstName + ' ' + $_.LastName}}, @{Name='EmployeeAge';Expression={$_.Age}}, @{Name='Location';Expression={$_.City}} | Export-Csv -Path 'employees_custom_headers.csv' -NoTypeInformation -UseCulture

In this modified example:

We split the employee name into FirstName and LastName properties.
We define an array $headers containing the desired custom headers: ‘EmployeeName’, ‘EmployeeAge’, and ‘Location’.
We use Select-Object to select the desired properties and rename them using calculated properties. The @{Name='...';Expression={...}} syntax allows us to specify a new name for each property and calculate its value using an expression.
Finally, we pipe the selected objects to Export-Csv, specifying the custom headers using the -Header parameter.
The -UseCulture parameter ensures that the custom headers specified in the $headers variable are used when exporting the data to the CSV file.

The resulting ’employees_custom_headers.csv’ file will have the following content:

"EmployeeName","EmployeeAge","Location"
"John Doe",35,"New York"
"Sarah Johnson",28,"Los Angeles" 
"Michael Smith",42,"Chicago"

Now, the CSV file uses the custom headers we specified, providing more meaningful names for each column.

Here is the exact output in the screenshot below:

How to Create CSV Files with Headers in PowerShell

Read Get the First and Last Line of a CSV File in PowerShell

Handle Missing Values

When creating CSV files, you may encounter situations where some objects have missing values for certain properties. By default, PowerShell represents missing values as empty strings in the CSV output. However, you can customize this behavior using the -NullValue parameter of the Export-Csv cmdlet.

Let’s consider an example where some employees have missing ages:

$employees = @(
    [PSCustomObject]@{Name='John Doe'; Age=35; City='New York'}
    [PSCustomObject]@{Name='Sarah Johnson'; Age=$null; City='Los Angeles'}
    [PSCustomObject]@{Name='Michael Smith'; Age=42; City='Chicago'}
)

$employees | Export-Csv -Path 'employees_missing_values.csv' -NoTypeInformation -NullValue 'N/A'

In this example, Sarah Johnson’s age is set to $null, representing a missing value. By using the -NullValue parameter with the value ‘N/A’, we instruct PowerShell to replace any missing values with the string ‘N/A’ in the CSV output.

The resulting ’employees_missing_values.csv’ file will have the following content:

"Name","Age","City"
"John Doe",35,"New York"
"Sarah Johnson","N/A","Los Angeles"
"Michael Smith",42,"Chicago"

Now, instead of an empty string, the missing age value is represented as ‘N/A’ in the CSV file.

Read Convert CSV to HTML Table in PowerShell

Append Data to an Existing CSV File using PowerShell

Sometimes, you may need to append new data to an existing CSV file without overwriting its contents. PowerShell allows you to achieve this using the -Append parameter of the Export-Csv cmdlet.

Here’s an example that demonstrates appending new employee data to an existing CSV file:

$newEmployees = @(
    [PSCustomObject]@{Name='Emily Davis'; Age=29; City='Houston'}
    [PSCustomObject]@{Name='Daniel Wilson'; Age=38; City='Phoenix'}
)

$newEmployees | Export-Csv -Path 'employees.csv' -NoTypeInformation -Append

In this example, we have an array $newEmployees containing additional employee objects. By using the -Append parameter with Export-Csv, the new employee data will be appended to the existing ’employees.csv’ file without overwriting its contents.

After running this code, the ’employees.csv’ file will have the following content:

"Name","Age","City"
"John Doe",35,"New York"
"Sarah Johnson",28,"Los Angeles"
"Michael Smith",42,"Chicago"
"Emily Davis",29,"Houston"
"Daniel Wilson",38,"Phoenix"

The new employee data is appended to the existing file, preserving the original data and headers.

Read How to Export Table to CSV in PowerShell?

Create CSV Files with Headers in PowerShell: Real Example

Let’s consider a real-world scenario where you need to export data from a database or an API and create a CSV file with headers. In this example, we’ll use a mock API that returns employee data in JSON format.

$apiUrl = 'https://api.example.com/employees'
$response = Invoke-RestMethod -Uri $apiUrl
$employees = $response.data

$employees | Select-Object name, age, city | Export-Csv -Path 'employees_from_api.csv' -NoTypeInformation

In this example:

We specify the URL of the API endpoint that returns employee data.
We use Invoke-RestMethod to send a GET request to the API and retrieve the response.
We extract the employee data from the response, assuming it’s stored in the data property.
We use Select-Object to select the desired properties (name, age, city) from the employee objects.
Finally, we export the selected data to a CSV file named ’employees_from_api.csv’ using Export-Csv.

The resulting ’employees_from_api.csv’ file will have the following content:

"name","age","city"
"John Doe",35,"New York"
"Sarah Johnson",28,"Los Angeles"
"Michael Smith",42,"Chicago"

This example demonstrates how you can retrieve data from an external source and create a CSV file with headers using PowerShell.

Conclusion

In this tutorial, I have explained various ways to create CSV files with headers using PowerShell. We covered examples of creating a basic CSV file with headers, specifying custom headers, handling missing values, appending data to an existing CSV file, and creating a CSV file from data retrieved from an API.

PowerShell provides cmdlets such as Export-Csv and Select-Object that make it easy to generate CSV files with headers. You can create CSV files with headers efficiently by following the examples and techniques covered in this tutorial.