PowerShell: How to Export an Array to a CSV File?

PowerShell is a versatile scripting language and command-line shell that provides powerful automation and management capabilities. As a PowerShell user, you may have found yourself in a situation where you need to export an array to a CSV file. In this blog post, we will explore how to export an array to a CSV (Comma Separated Values) file using PowerShell. CSV files are widely used for data exchange between applications and platforms due to their simplicity and compatibility. By the end of this post, you will understand how to export an array to a CSV file using various techniques.

Understanding Arrays and CSV Files in PowerShell

Arrays are collections of elements that are stored in a single variable. Each element in an array can be accessed using an index number. Arrays are commonly used to store data in PowerShell because they allow you to group related data together and manipulate it as a single entity.

An array is a collection of elements stored in a single variable. Here is an example of how to create a simple array:

$Array = @(1, 2, 3, 4, 5)

CSV files are plain text files that store tabular data in a structured format. Each row in a CSV file represents a record, and each column represents a field in that record. CSV files are commonly used to exchange data between different applications and databases.

There are several ways to export an array to a CSV file in PowerShell. In this post, we will cover three methods: the Export-Csv cmdlet, Add-Content with a ForEach loop, and Out-File with a ForEach loop.

Method 1: Using the Export-Csv Cmdlet

The Export-Csv cmdlet is the most straightforward method for exporting an array to a CSV file. This cmdlet exports objects to a CSV file and converts them into a series of comma-separated values. Here’s an example:

$array = @(1, 2, 3, 4, 5)
$array | Export-Csv -Path "ArrayExport.csv" -NoTypeInformation

However, this method will not work as expected with simple arrays. To use the Export-Csv cmdlet, we need to input an object to it. Here’s how to do that:

$array = @(1, 2, 3, 4, 5)
$arrayObjects = $array | ForEach-Object { 
     [PSCustomObject]@{'Value' = $_} 
}
$arrayObjects | Export-Csv -Path "C:\Temp\ArrayExport.csv" -NoTypeInformation

Similarly, if you export a string array using PowerShell, the CSV will only show length!

#String Array
$Array = @("apple", "orange", "banana", "kiwi")

#Export array to CSV
$Array | Export-CSV -Path "C:\Temp\Array.csv" -NoTypeInformation

Output:

"Length"
"5"
"6"
"6"
"4"

This is because, the Export-Csv cmdlet takes objects as inputs and exports a table of object properties and their values. Since your script is producing string objects, and the only property they have is length, that’s what you got. So, to overcome this issue, we have to use the “Select-Object” cmdlet, which returns a custom PSObject.

#String Array
$Array = @("apple", "orange", "banana", "kiwi")

#Convert Array to Object Array - using Select-Object cmdlet
$ObjArray = $Array | Select-Object @{Name='Name';Expression={$_}}

#Export Object Array to 
$ObjArray | Export-Csv "C:\Temp\ArrayExport.csv" -NoTypeInformation

Exporting an Array to CSV Using PowerShell

In this example, we’re creating an array called $Array that contains three objects. Each object has three properties: Name, Age, and City (Similar to the multidimensional array). The PSCustomObject class is used to create custom objects with properties. Once you have an array, you can export it to a CSV file using the Export-Csv cmdlet. Here’s an example of how to export the $Array array to a CSV file called data.csv in the “C:\Temp” folder:

#Define Array
$Array = @( [PSCustomObject]@{ Name = "John"; Age = 30; City = "New York" }, 
            [PSCustomObject]@{ Name = "Jane"; Age = 25; City = "Los Angeles" }, 
            [PSCustomObject]@{ Name = "Bob"; Age = 40; City = "Chicago" }
         )

#Export array to CSV
$Array | Export-Csv -Path "C:\Temp\data.csv" -NoTypeInformation

In this example, we’re piping the $Array array to the Export-Csv cmdlet. The -Path parameter specifies the path and filename for the CSV file. The -NoTypeInformation parameter prevents PowerShell from adding a type information header to the CSV file. After running the script, a new file named Data.csv will be created in the “C:\Temp” directory, containing the data from the array in CSV format.

Exporting Arrays with Specific Columns

If you have an array with many columns, and you only want to export a subset of those columns, you can use the Select-Object cmdlet. For example, if you have an array with five columns, and you only would like to export the first three columns, you can use the following command:

$Array | Select-Object Column1, Column2, Column3 | Export-Csv -Path "data.csv" -NoTypeInformation

Export Multiple Arrays

How to export multiple arrays to a single CSV file also requires the Export-CSV cmdlet. This task is performed by concatenating the array contents using the + (plus) symbol. Here is an example:

#Define Arrays
$Array1 = @(1,2,3,4)
$Array2 = @(5,6,7,8)
$Array3 = $Array1 + $Array2

$ResultArray = @()
#Convert Array to object
ForEach ($Item in $Array3)
{
    $ResultArray+=[pscustomobject]@{Result = $item }
}

#Export Array to CSV
$ResultArray | Export-Csv -Path "C:\Temp\Array.csv" -NoTypeInformation

In this example, we are creating two arrays ($array1, $array2), merging them into the $Array3, and exporting the resulting combined array to a CSV file.

Method 2: Using Add-Content with a ForEach Loop

Another way to export an array to a CSV file is by using the Add-Content cmdlet in combination with a ForEach loop. This method allows you to create a new file or append data to an existing file. Here’s an example:

#Define Array
$Array = @(1, 2, 3, 4, 5)
$CSVFile  = "C:\Temp\ArrayExport.csv"

#Remove the CSV if exists
If (Test-Path -Path $CSVFile) { Remove-Item -Path $CSVFile -Force}

#Set Header
$Header = "Value"
#Add Header to the CSV file
Add-Content -Path $CSVFile -Value $Header

#Add content to the CSV
ForEach ($Item in $Array) {
    Add-Content -Path $CSVFile -Value $Item
}

Note that we are checking if the CSV file exists and deleting it. This is because the Add-Content cmdlet appends to the existing file by default.

Method 3: Using Out-File with a ForEach Loop

Out-File is another cmdlet that can be used to export an array to a CSV file. This cmdlet sends output to a file instead of the console. Here’s an example:

$array = @(1, 2, 3, 4, 5)
$header = "Value"
$header | Out-File -FilePath "C:\Temp\ArrayExport.csv"
ForEach ($item in $array) {
    $item | Out-File -FilePath "C:\Temp\ArrayExport.csv" -Append
}

Conclusion

In this blog post, we’ve covered three methods to export an array to a CSV file in PowerShell: the Export-Csv cmdlet, the Add-Content with a ForEach loop, and the Out-File with a ForEach loop. Each method has its advantages, and you can use the most appropriate to your requirements.

SharePoint Diary