The Export-CSV command in PowerShell

PowerShell's Export-CSV cmdlet lets you export data to a CSV file. The cmdlet saves admins so much time by transforming loose text into a structured format. CSV files can be created with PowerShell instead of Excel.

With the Export-Csv cmdlet, you can create CSV files to feed reports, send to databases, and more.

As a text file, CSV files contain comma-separated lists of rows. Technically, we could just use a generic text file command like Set-Content to do this job since it's just a text file. To use a generic command, we would need to construct the CSV structure ourselves. The CSV export command is already included in Export-Csv, so why do that?

There is a specific structure for CSV files. The convenience of this PowerShell cmdlet lies in its ability to understand, read, and transform information into CSV files.

Objects -eq CSV Rows

The Export-Csv cmdlet is used to export PowerShell objects to a CSV file.

Consider an object that has two properties, foo and bar.  There are two values for each property.  It will create a CSV file with two columns and a single row when you send it to Export-Csv. After importing the CSV file, you can inspect the contents.

"PS> $testObject = [pscustomobject]@{'foo' = 1; 'bar' = 2}

PS> $testObject | Export-Csv -Path C:\TestObject.csv

PS> $testObject

foo bar

1 2

PS> Import-Csv -Path C:\TestObject.csv

foo bar

1  2"

An object in memory and a CSV file are treated the same by PowerShell! My column labels should also be headers. Using this cmdlet, exporting an object is like saving it to the file system. We can inspect the raw text of the exported CSV file using the generic PowerShell command Get-Content.

"PS> Get-Content -Path C:\TestObject.csv

#TYPE System.Management.Automation.PSCustomObject

"foo","bar" "1","2"

There is a double quote around each row entry in the command. You can also include fields with whitespace.

Useful Parameters

Windows PowerShell 5.1's Export-CSV command lets you specify the object type that created the CSV file. If you'd rather not see this line, NoTypeInformation is a popular parameter, which eliminates it entirely.

Export-CSV will overwrite existing files if you work with a lot of CSV files or data sources. If you use the Append parameter, there is no problem. As opposed to overwriting an existing file, the Append parameter allows you to add rows to the CSV.

The Delimiter parameter is also useful. You may occasionally need to create a CSV file that does not separate fields with a comma. Rather than using tabs as separators, perhaps you should use semicolons instead. By specifying what delimiter we need, we can use the Export-Csv PowerShell cmdlet to help us out with these situations as well.

"PS> $testObject | Export-Csv -Path C:\TestObject.csv -Delimiter "`t"

PS> Get-Content -Path C:\TestObject.csv

#TYPE System.Management.Automation.PSCustomObject

"foo"   "bar" "1"     "2"

Dealing with Quotation Marks

As part of creating CSV rows, Export-Csv surrounds all fields in quotation marks. This might not be desirable in some cases. Sadly, there is no way to prevent this. Using a little more code, however, you can accomplish this.

A CSV file example can be found below. Get-Content then reads the raw text file and replaces all double quotes with nothing. This information is then written back to disk using Set-Content.

"$testObject | Export-Csv -Path C:\TestObject.csv

(Get-Content -Path C:\TestObject.csv -Raw).replace('"','') | Set-Content -Path C:\TestObject.csv"

Summary

Using the Export-Csv cmdlet is a simple and extremely useful way to manage CSV data. It understands the structure of CSV files and transforms PowerShell objects into rows in CSV files.

The only way I'm going to write a CSV file is by creating a FileSystemObject, creating a loop, and using Write() methods!

Start writing PowerShell code today by using this command.