How to Export an Array to CSV in PowerShell

When working with PowerShell, you may often find yourself in situations where you need to export data to a CSV file for reporting, analysis, or data interchange purposes. CSV, which stands for Comma Separated Values, is a simple file format widely supported and easily read by humans and machines. In this PowerShell tutorial, we’ll explore how to export an array to a CSV file using PowerShell with a few real examples.

Table of Contents

Understanding Arrays and CSV in PowerShell

Before diving into the export process, let’s establish a basic understanding of arrays and CSV files in the context of PowerShell.

An array is a data structure that holds a collection of items. These items can be of any data type, and you can access them by their index in the array. In PowerShell, you create an array by assigning multiple values to a variable, separated by commas.

A CSV file is a plain text file where each line represents a data record. Each record consists of one or more fields, separated by commas. The first line often contains headers, which are the names of the fields.

Read Create CSV Files with Headers in PowerShell

Exporting a PowerShell Array to CSV Using `Export-Csv`

The primary cmdlet used for exporting data to a CSV file in PowerShell is Export-Csv. This cmdlet takes an array of objects and creates a CSV file where each object becomes a row, and the object properties become the columns.

Basic Example

Let’s start with a simple example of exporting an array of PSObjects to a CSV file:

# Create an array of PSObjects
$personArray = @(
    [PSCustomObject]@{Name='John Doe'; Age=30; City='New York'},
    [PSCustomObject]@{Name='Jane Smith'; Age=25; City='Los Angeles'},
    [PSCustomObject]@{Name='Michael Johnson'; Age=35; City='Chicago'}
)

# Export the array to a CSV file
$personArray | Export-Csv -Path 'C:\temp\people.csv' -NoTypeInformation

In this example, we create an array $personArray that contains three custom objects, each representing a person with Name, Age, and City properties. We then pipe this array to the Export-Csv cmdlet, specifying the path to the CSV file. The -NoTypeInformation parameter is used to exclude the type information from the CSV file.

I have executed the script using Visual Studio code in the screenshot below for the output.

Read How to Use PowerShell Export-CSV cmdlet?

Export PowerShell Simple Arrays to CSV Format

If you have a simple array, not an array of objects in PowerShell, you must convert it to a format that Export-Csv can use. Here’s how you can do that:

# Create a simple array
$simpleArray = @('Apple', 'Banana', 'Cherry')

# Convert the simple array to an array of objects
$objectArray = $simpleArray | ForEach-Object {
    [PSCustomObject]@{Fruit=$_}
}

# Export the array of objects to a CSV file
$objectArray | Export-Csv -Path 'C:\temp\fruits.csv' -NoTypeInformation

The above script, $simpleArray contains a list of strings. We use ForEach-Object to convert each string into a custom object with a single property. Then, we export the resulting array of objects to a CSV file.

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

Advanced Usage of `Export-Csv` in PowerShell

Let us check out a few advanced scenarios to export an array to CSV in PowerShell using Export-csv.

1. Appending to an Existing CSV File

If you want to add more data to an existing CSV file, you can use the -Append parameter:

# Additional data to append
$newData = @(
    [PSCustomObject]@{Name='Anna Brown'; Age=28; City='Miami'},
    [PSCustomObject]@{Name='Greg White'; Age=40; City='Seattle'}
)

# Append the new data to the existing CSV file
$newData | Export-Csv -Path 'C:\temp\people.csv' -NoTypeInformation -Append

This script will add $newData to the people.csv file without overwriting the existing content.

2. Specifying a Delimiter

Sometimes, you may need to use a different delimiter instead of a comma. For instance, to use a semicolon, you can use the -Delimiter parameter:

# Export using a semicolon delimiter
$personArray | Export-Csv -Path 'C:\temp\people_semicolon.csv' -Delimiter ';' -NoTypeInformation

Conclusion

Once you understand how the cmdlet works, exporting an array to a CSV file in PowerShell is a straightforward process. Whether you’re dealing with simple arrays or arrays of custom objects, PowerShell provides the flexibility to shape and export your data as needed. Remember to use the -NoTypeInformation parameter to keep your CSV files clean and the -Append parameter to add data to existing files without overwriting them.

In this PowerShell tutorial, I have explained how to export an array to CSV in PowerShell.