In PowerShell, we can use the Export-CSV cmdlet to export the array of objects into a CSV file. In this post, we will explore how to append data or write content row by row (line by line or chunk by chunk) in a CSV file.
The Export-CSV cmdlet creates a CSV file of the formatted objects that we pass to the command. Each object is a row that includes a comma-separated list of the object’s property values.
This command gets all the system process objects in the local computer and exports the objects to a CSV file.
$Processes = Get-Process
$Processes | Export-CSV "C:\Temp\Processes.CSV" -NoTypeInformation -Encoding UTF8
The above command exports all the formatted properties in the object. To export only selected properties of an object, use the Select-Object cmdlet.
$Processes = Get-Process
$Processes | Select-Object Id, ProcessName,WorkingSet,Company |
Export-CSV "C:\Temp\Processes.CSV" -NoTypeInformation -Encoding UTF8
Export and append data (set of rows or lines) to a CSV file
By default, the Export-CSV cmdlet creates a new CSV file (or replaces the existing file with the same name) and writes content to the file. We need to use the –Append parameter with the Export-CSV cmdlet to append the data into an existing CSV file. The command creates a new file if the file doesn’t already exist.
The below commands create a new file (“ExportFile.csv“) and export the first set of objects to the CSV file.
#Initialize Array1 - First set of objects to export
$Array1 = @()
1..3 | ForEach {
$Array1 += New-Object PSObject -property $([ordered]@{
Name = 'User '+ $_
Number = $_
})
}
#Export - Create the file "ExportFile.csv" and write content
$Array1 | Export-CSV "C:\Temp\ExportFile.csv" -NoTypeInformation -Encoding UTF8
#Read the exported file and display the content
Get-Content -Path "C:\Temp\ExportFile.csv"
Now we can use the -Append parameter to add the second set of objects in the already exported file.
#Initialize Array2 - Second set of objects to export
$Array2 = @()
4..6 | ForEach {
$Array2 += New-Object PSObject -property $([ordered]@{
Name = 'User '+ $_
Number = $_
})
}
#Export - Append the data to the existing file ("ExportFile.csv") or create a new file if it does not exist
$Array2 | Export-CSV "C:\Temp\ExportFile.csv" -Append -NoTypeInformation -Encoding UTF8
#Read the exported file and display the content
Get-Content -Path "C:\Temp\ExportFile.csv"
Use the Force parameter with Append to add objects with mismatched properties
This example describes how to use the –Force and –Append parameters to write in a CSV file with mismatched object properties.
$Array1 = @([PSCustomObject]@{Name = 'User 1'; Number = '1'},[PSCustomObject]@{Name = 'User 2'; Number = '2'})
$Array1 | Export-CSV "C:\Temp\OutFile.csv" -NoTypeInformation
$Array2 = @([PSCustomObject]@{Name = 'User 3'; Age = '27'},[PSCustomObject]@{Name = 'User 4'; Age = '35'})
$Array2 | Export-CSV "C:\Temp\OutFile.csv" -Append -NoTypeInformation
The above command returns the following error. The append fails because there is a property name mismatch between Number and Age.
Export-CSV : Cannot append CSV content to the following file: C:\Temp\OutFile.csv. The appended object does not have a property that corresponds to the following column: Number. To continue with mismatched properties, add the -Force parameter, and then retry the command.
At line:1 char:11 + $Array2 | Export-CSV “C:\Temp\OutFile.csv” -Append -NoTypeInformation
Need to use the Force parameter with Append to export and write objects that contain mismatched properties to a CSV file. Only the properties that match are written to the file. The mis-matched properties are discarded. In this case, the Age property will get discarded.
$Array1 = @([PSCustomObject]@{Name = 'User 1'; Number = '1'},[PSCustomObject]@{Name = 'User 2'; Number = '2'})
$Array1 | Export-CSV "C:\Temp\OutFile.csv" -NoTypeInformation
$Array2 = @([PSCustomObject]@{Name = 'User 3'; Age = '27'},[PSCustomObject]@{Name = 'User 4'; Age = '35'})
$Array2 | Export-CSV "C:\Temp\OutFile.csv" -Append -Force -NoTypeInformation