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. Export-CSV cmdlet is available in Microsoft.PowerShell.Utility PowerShell module creates a CSV file of the objects passed to it.

Using the Export-CSV cmdlet, you can easily create spreadsheets and export the object’s output to a CSV file.

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 the 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, and create a CSV file with a header.

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

Let’s practice!

PowerShell Export-CSV Syntax

Syntax:

PowerShell Export-CSV converts objects to comma-separated values (CSV) strings and writes them to a CSV file. In PowerShell, it output to file CSV format.

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 the parameter is 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 the #TYPE information header from the output.

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

PowerShell Export-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, and ReleaseDate and send these BIOS objects to the 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. It output to a file in CSV format.

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 the earlier steps.

It will import CSV file and write the content of CSV file on PowerShell terminal as below

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 PowerShell Export-CSV Append parameter 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 PowerShell Export-CSV cmdlet.

Export-CSV converts event log objects to CSV ( comma-separated value) strings and exports them 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.

PowerShell Export-CSV 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 is used to create structured data in PowerShell. It creates reusable objects.

To export variable to CSV file, use the 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 export CSV script,

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

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

PowerShell Export-Csv convert objects to CSV ( comma-separated value) string and create CSV file and export CSV file to location specified by Path parameter.

It writes the object’s output to a file in CSV format.

NoTypeInformation removes the #TYPE information header from CSV output.

The output of the above script as below when we use the 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 variables to a CSV file.

Now make that file read-only and try to write new content to the read-only file as below

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

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

In the above PowerShell export CSV script,

PSCustomObject creates objects having Drive and Disk Size information.

When we try to export objects to a CSV file that is read-only, it throws an 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 the CSV file forcefully, use the Force parameter.

Using PowerShell Export-CSV Force parameter, 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

Cool Tip: How to get free disk space in PowerShell!

Using PowerShell Export-CSV NoClobber

PowerShell Export-CSV file writes output to CSV file and overwrites any existing file if exists on disk.

Using the Export-CSV NoClobber parameter throws an error if trying to overwrite and prevents overwriting the existing CSV file.

Let’s consider the above example to illustrate PowerShell export CSV noclobber functionality.

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

In the above PowerShell output to file script, we have used the Export-CSV NoClobber parameter.

If the file already exists on the specified path, it will throw an error and prevent overwriting the output to a CSV file.

The output of the above example to write output to CSV file is:

Using PowerShell Export-CSV Delimiter Parameter

When you export a CSV file, by default values in the CSV file are comma-separated.

You can change the delimiter in a CSV file using the Export-CSV Delimiter parameter.

Using the Delimiter parameter, allows you to specify the delimiter.

For example, referring to the above example to get free disk space using PowerShell, use delimiter as ‘;’ (semicolon) instead of the default ‘,’ (comma).

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

In the above PowerShell Export CSV Delimiter example,

It writes output to CSV file separated by delimiter ‘;’ (semicolon)

The output of the above example is:

Conclusion

You can pipe any object to Export-CSV cmdlet in PowerShell to write to a CSV file.

The output of the PowerShell Export-CSV cmdlet is a CSV string saved to a file on the location specified by the Path parameter.

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