Export-CSV – Export to CSV file in PowerShell

PowerShell Export-CSV cmdlet converts objects in CSV (comma-separated value) text and save as CSV file. Using Export-CSV cmdlet, you can easily create spreadsheets and export the objects to a CSV file.

Export-CSV cmdlet is available in Microsoft.PowerShell.Utility PowerShell module creates a CSV file of the objects passes to it. Each object is a row in a CSV file that contains the object’s property values. Path parameter specifies the location to export CSV file in a given directory. NoTypeInformation parameter removes the #TYPE information header from the CSV output.

Using Export-CSV cmdlet in PowerShell, you can append the CSV output to the end of the specified file, specify the delimiter to use to separate the property value, specify an encoding for exported CSV file, create a CSV file with a header.

In this article, I will explain how to use Export-CSV cmdlet to convert objects to CSV and export to CSV file with different examples.

Export-CSV Syntax

Syntax:

Export-CSV converts objects to comma-separated values (CSV) strings and writes to CSV file.

Export-Csv
      -InputObject <PSObject>
      [[-Path] <String>]
      [-LiteralPath <String>]
      [-Force]
      [-NoClobber]
      [-Encoding <Encoding>]
      [-Append]
      [[-Delimiter] <Char>]
      [-IncludeTypeInformation]
      [-NoTypeInformation]
      [-QuoteFields <String[]>]
      [-UseQuotes <QuoteKind>]
      [-WhatIf]
      [-Confirm]
      [<CommonParameters>]

Parameters:

Append: Use this parameter if you need Export-CSV to write CSV contents to the output of the specified file. If parameter not specified, Export-CSV replaces the file contents without warning.

Delimiter: Default is a comma (,) to separate the property values. Use this parameter to specify another delimiter like semicolon (;), colon (:) or any other.

Encoding: The default encoding is utf8NOBOM. Use this parameter to specify the encoding for the exported CSV file. Acceptable values for this encoding parameter are as below

ascii,bigendianunicode,bigendianutf32,oem,unicode,utf7,utf8,utf8BOM, utf8NOBOM,utf32 

Path: Required parameter specifies location path to save exported CSV file

NoTypeInformation: This parameter removes #TYPE information header from the output.

Let’s understand the Export-CSV cmdlet with an examples.

Export to CSV – BIOS Information

In this example, we will use Get-WmiObject to get BIOS information on the local computer and export the object to a CSV file.

Get-WmiObject -Class Win32_Bios | Select-Object -Property BIOSVersion,Manufacturer,Version,ReleaseDate | Export-CSV -Path D:\PowerShell\ComputerDetails.csv -NoTypeInformation

In the above PowerShell script,

Get-WmiObject gets the information about the Win32_Bios class and passes the output objects to the Select-Object cmdlet.

Select-Object uses Property parameter to select BIOS properties like BIOSVersion, Version, Manufacturer, ReleaseDate and send these BIOS objects to Export-CSV cmdlet.

Export-CSV converts objects to CSV ( comma-separated value) strings and export to csv file at location specified by the Path parameter. NoTypeInformation parameter remoe #TYPE information header from CSV output file.

Using the Import-CSV cmdlet in PowerShell, we can import the CSV file created in earlier steps. It will import CSV file and write the content of CSV file on PowerShell terminal as below

PowerShell Export to CSV file
PowerShell Export to CSV file

Create CSV file and Append Objects to CSV file

In this example, we will create a CSV file and use the Append parameter to add new objects to an existing CSV file as below

# Get Windows PowerShell logs having instance Id=403 and Entrytype = Information
$PowerShellLogs = Get-EventLog -LogName 'Windows PowerShell' -InstanceId 403 -EntryType Information

# Select EventId,MachineName,EntryType,Source and InstanceId property to write to csv file
$PowerShellLogs | Select-Object EventId,MachineName,EntryType,Source,InstanceId | Export-Csv -Path D:\PowerShell\PowerShellLogs.csv -NoTypeInformation

# Get Windows PowerShell logs having instance Id=300 and Entrytype = Warning
$PowerShellWarningLogs = Get-EventLog -LogName 'Windows PowerShell' -InstanceId 300 -EntryType Warning

# Select EventId,MachineName,EntryType,Source and InstanceId property to append to existing csv file
$PowerShellWarningLogs | Select-Object EventId,MachineName,EntryType,Source,InstanceId | Export-Csv -Path D:\PowerShell\PowerShellLogs.csv -NoTypeInformation -Append

In the above Export to CSV PowerShell example,

Get-EventLog cmdlet gets the event logs from Windows logs. It retrieves the event logs as per specified LogName, InstanceId, and EntryType parameters. It stores PowerShell event logs to the $PowerShellLogs variable.

In the second command, it uses the $PowerShellLogs variable to pass event log objects to the Export-CSV cmdlet.

Export-CSV converts event log objects to CSV ( comma-separated value) strings and exports to CSV file in the location specified by the Path parameter. The NoTypeInformation parameter removes the #Type information header from the output CSV file.

Get-EventLog cmdlet gets the event logs for different InstanceId and Entrytype and stores log objects in the $PowerShellWarningLogs.

$PowerShellWarningLogs event logs objects are sent down to the pipeline to the Export-CSV cmdlet.

Export-Csv uses Append parameter to add $PowerShellWarningLogs objects to an existing PowerShellLogs csv file.

The final PowerShellLogs.csv file contains Information and Warning source event logs for PowerShell.

Export Variable to CSV

PSCustomObject use to create structured data in PowerShell. It creates reusable objects.

To export variable to CSV file, use below code

$custObject = [PSCustomObject]@{
     Drive = "C:\"
     Description = "C Drive Disk Size Space"
     Id = 1
}

$custObject | Export-Csv -Path D:/PowerShell/Comp-Details.csv -NoTypeInformation

In the above PowerShell script,

Using PSCustomObject, it creates a structured data object having Drive, Description, and Id variables and stores object in the $custObject variable.

In the second command, $custObject objects are sent down to pipeline to Export-Csv cmdlet.

Export-Csv convert objects to CSV ( comma-separated value) string and create csv file and export csv file to location specified by Path parameter.NoTypeInformation removes the #TYPE information header from CSV output.

Output of the above script as below when we use Get-Content cmdlet to read CSV file

PS D:\PowerShell> Get-Content .\Comp-Details.csv

"Drive","Description","Id"
"C:\","C Drive Disk Size Space","1"

Overwrite Read-Only CSV file using Force

In the above example, we export variable to csv file. Now make that file as readonly and try to write new content to read-only file as below

$myObject = [PSCustomObject]@{
     Drive = "C:\"
     Size = "50GB"
    
}

$myObject | Export-Csv -Path D:/PowerShell/Comp-Details.csv -NoTypeInformation

In the above PowerSell script,

PSCustomObject creat objects having Drive and Disk Size information. When we try to export object to csv file which is read-only, it throws exception as below

Export-Csv : Access to the path 'D:\PowerShell\Comp-Details.csv' is denied.
At line:7 char:13
+ $myObject | Export-Csv -Path D:/PowerShell/Comp-Details.csv -NoTypeIn ...
+             ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : OpenError: (:) [Export-Csv], UnauthorizedAccessException
    + FullyQualifiedErrorId : FileOpenFailure,Microsoft.PowerShell.Commands.ExportCsvCommand

To write to csv file forcefully, use Force parameter.

Force parameter in Export-CSV cmdlet, force to export to write to CSV file, use below command to write to csv read-only file forcefully.

$myObject = [PSCustomObject]@{
     Drive = "C:\"
     Size = "C Drive Disk Size Space"
    
}
 
$myObject | Export-Csv -Path D:/PowerShell/Comp-Details.csv -Force -NoTypeInformation

Conclusion

You can pipe any object to Export-CSV cmdlet in PowerShell to write to CSV file. Output of Export-CSV cmdlet is CSV string saved to file on location specified by Path parameter.

You can find more topics about PowerShell Active Directory commands and PowerShell basics on ShellGeek home page.

Leave a Comment