The PowerShell cmdlet Export-Csv is one of the many reasons that this language has saved everyone so much time. This cmdlet has transformed what was once a troublesome task of trying to wrangle loose text into some structured format with VBScript. Instead of messing around with Excel, we can instead use PowerShell to create CSV files.

If you're a beginner/intermediate PowerShell scripter, be sure to check out my FREE mini-course on Building a PowerShell Tool! 9,000+ words of deep explanation and insights on how to build a PowerShell tool.

Using both the Export-Csv and Import-Csv commands to export and import any CSV table data like contacts, lists of companies, server listings and so on, we can script just about any action we need around CSV files.

The CSV format or what some unfamiliar with a CSV call an "Excel file" is merely a comma-separated list of rows. Since it is just a text file, we could technically just use a generic text file command like Set-Content to do the job. To use a generic command though would require us building out the necessary CSV structure ourselves. Why do that when we have a CSV export command already in Export-Csv?

CSV files require a specific structure. The ability to natively understand this structure and the ease at which PowerShell objects can be transformed to CSV files is what makes the Export-Csv PowerShell cmdlet so great for use in your PowerShell script.

Objects -eq CSV Rows

The Export-Csv cmdlet has a single purpose; to create or append rows to a CSV file based on one or more PowerShell objects being sent to it. If you've got an object with two properties foo and bar with respective values of 1 and 2 sending that object to Export-Csv to "export a CSV" would create a CSV file with two columns and a single row. I can then import the CSV contents and inspect what it looks like.

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

Notice that PowerShell essentially treats an object in memory and a CSV file the same! It also that my column labels are supposed to be headers. Exporting objects using Export-Csv is like saving the object to the file system. Using the generic text PowerShell command Get-Content, we can inspect the raw text to see what the Export-Csv command exported.

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

#TYPE System.Management.Automation.PSCustomObject
"foo","bar" "1","2"

Notice that the command uses the double quote around each row entry. This allows you to include fields with spaces as well.

Useful Export-Csv Parameters

In Windows PowerShell 5.1, Export-Csv records the type of object that created the CSV file. If you'd rather now see this line, a popular parameter is NoTypeInformation which eliminates this line entirely.

If you find yourself working with lots of data at once, you may be in for a rude awakening when you notice that, by default Export-Csv overwrites an existing CSV file. No problem though if you're using the Append parameter. Using the Append parameter either creates the CSV from scratch if it exists and adds the appropriate row or adds another row if the CSV file already exists.

Another useful parameter is Delimiter. Occasionally, you may find yourself needing to create a CSV file that doesn't use the usual common delimiter to separate fields. Instead, perhaps you need to create a file with a lot of data that uses tabs as separators or maybe even semicolons. The Export-Csv PowerShell cmdlet can help us out with these as well by specifying whatever delimiter we need.

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"

Summary

The Export-Csv cmdlet is a simple yet extremely useful tool for managing CSV data. Its ability to understand the structure of a CSV data file natively and to transform PowerShell objects into CSV rows seamlessly is a huge time saver. I'm not about to go back to batch, or VBScript where creating a CSV file from an existing object required instantiating a FileSystemObject, creating a loop and a bunch of Write() methods!

This command is yet another great reason to start writing PowerShell code today!

Join the Jar Tippers on Patreon

It takes a lot of time to write detailed blog posts like this one. In a single-income family, this blog is one way I depend on to keep the lights on. I'd be eternally grateful if you could become a Patreon patron today!

Become a Patron!