Master Import-Csv and Other CSV Commands in PowerShell

The PowerShell Export-Csv cmdlet and the PowerShell Import-Csv cmdlets allow administrators to import CSVs via foreach loop, use Export-Csv to append CSVs and export arrays to a CSV file and a whole lot more.

In this article you will learn about many common scenarios in which you can use PowerShell to manage CSVs like:

• Reading CSV Files with PowerShell
• Saving CSV Files with PowerShell
• Formatting Output Prior to Running Export-CSV
• Appending to a CSV
• Appending Differences to an Existing File
• Export-CSV and the #TYPE String

If you are unfamiliar with the inner workings of CSV files, they are text files that follow a standard format of literally comma separating your values in a table. You can create a CSV file in PowerShell using the Export-Csv cmdlet and piping one or more objects to it.

The command below finds the first two running processes and passes the objects generated to the Export-Csv cmdlet. The Export-Csv cmdlet then creates a CSV file called processes.csv in the root of your system drive (most likely C:\).

PS51> Get-Process | Select-Object -First 2 | Export-CSV -Path "$env:SystemDrive\processes.csv"

Now open processes.csv with notepad. You should see "Name","SI","Handles","VM" at the top as headers. You will also see #TYPE System.Diagnostics.Process, which may not make that much sense now. Don’t worry, we will cover that string in this article.

Reading CSV Files with Import-Csv

Want more tips like this? Check out my personal PowerShell blog.

PowerShell has a couple of commands that allow you to read text files. Those commands are Get-Content and Import-Csv. Each of these two cmdlets technically read the file the same way. But Import-Csv takes it one step further. Import-Csv understands the underlying structure of not just a text file but a CSV file.

Since a CSV file follows a certain schema, Import-Csv understands the CSV schema. This cmdlet only reads the text file from disk but also converts the rows in the CSV file to PowerShell objects.

Non CSV Files

Typically, a CSV’s data is comma-separated but there are times when a CSV (not technically a CSV at that point though) has data separated with a different delimiter. Those delimiters are sometimes a tab or perhaps a semicolon.

If you have CSV with a different delimiter, you can use the Delimiter parameter. This parameter tells Import-Csv to not look for commas, which is does by default, but for another value.

For example, if you have a tab-separated file, you can read the file like below:

PS51> Import-Csv -Path tab-separated-data.csv -Delimiter "`t"

Adding Headers

A common Import-Csv parameter is Header. This parameter lets you specify the property names of the objects created by this cmdlet.

By default, the Import-Csv cmdlet will treat the top row of the CSV file as headers. It will then convert these values in properties of each row (object). But if you have a CSV file that does not have a header row, you can use the Header parameter to define one yourself.

The Header parameter prevents Import-CSV from using your first row as your header and also saves you a headache from having to manually open up the CSV file to add headers yourself.

To demonstrate this behavior, open up notepad and copy/paste the text below. This text will represent a dataset with three rows and two columns.

a,1
b,2
c,3

Save the text file as test.csv. Don’t forget to enable file type extensions or enclose the file with double quotes so that you don’t accidentally save it as a file ending in .csv.txt!

Now, use Import-CSV to read the recently-created CSV file without the Header parameter and inspect the output.

PS51> Import-Csv .\test.csv

a 1
---
b 2
c 3

Notice that it used the first row as the object properties. A and 1 aren’t “labels” you want for the object properties. A, b and c are letters while 1, 2 and 3 are numbers. You need to define those with the Header parameter like below:

PS51> Import-Csv .\test.csv -Header "Letter", "Number"

Letter Number
------ ------
a      1
b      2
c      3

Saving CSV Files with PowerShell

If you need to create or save a CSV file from PowerShell objects, you can also go the other way. While the Import-Csv cmdlets “converts” a CSV file to PowerShell objects, Export-Csv does the opposite. This cmdlet “converts” PowerShell objects to a CSV file.

By saving a CSV file with Export-Csv allows you to later view or use that data in other systems.

For example, I can save all running processes on my computer by piping Get-Process to the Export-Csv cmdlet.

PS51> Get-Process | Export-Csv -Path processes.csv

The Export-Csv cmdlet is simple in nature but there are a couple gotchas to look out for.

Formatting Output Prior to Running Export-CSV

As you’ve seen above, Export-Csv, by itself does a “raw” conversion. It doesn’t add any special rich-text formatting, add any colors and so on.

One of the most common gotchas is attempting to make output look pretty prior to exporting to a CSV. Many users will attempt to make the output look better prior to exporting to a CSV. But I’m about to show you that make things worse.

You can open a CSV in Microsoft Excel and italicize, bold, add colors and many other things but if you save the file as a CSV (rather than an Excel workbook), all formatting will be erased. A CSV file simply isn’t “smart” enough.

Recall that CSV files are just plain text files with values delimited by commas (or sometimes tabs). Piping Import-Csv to a Format-* PowerShell cmdlet will not work as expected.

Per Microsoft Export-Csv documentation: “Do not format objects before sending them to the Export-CSV cmdlet. If Export-CSV receives formatted objects the CSV file contains the format properties rather than the object properties.”

Why is this?

Open up a PowerShell window and create some dummy data. Let’s use the Get-Process example covered in the first section of this article. But this time, assign the output to a variable. Then, pipe those process objects to the Format-Table cmdlet.

PS51> $a = Get-Process
PS51> $a | Format-Table

You can see using Format-Table, you now have a clean, tabular output.

Now save this output to another variable.

PS51> $b = $a | Format-Table

Now view the properties on both the $a and $b variable values with Get-Member. This cmdlet will help you understand why these two seemingly like objects don’t export to a CSV file the same way:

PS51> $a | Get-Member

PS51> $b | Get-Member

The output directly from Get-Process returns: TypeName: System.Diagnostics.Process  whereas the output from Format-Table is completely different. It returns many different types with varying properties.

If you view $a and $b in the console, the output would look identical. This behavior is due to PowerShell’s formatting system.

How does this affect the output of Export-Csv?

PS51> $b | Export-Csv | Format-Table

Export-Csv reads each object as-is. When you pipe output to a Format-* cmdlet, you’re changing the input that Export-CSV receives. This then affects the output that gets saved into your new CSV file.

If you’re going to be piping output to the Export-Csv cmdlet, do not pipe output to any Format-* cmdlet.

Remember that CSVs are focused on data, not formatting.

Appending to a CSV File

Sometimes you might have an existing file that you want to add to rather than creating a new one entirely. By default Export-Csv will overwrite any file specified via the Path parameter.

If you need to append data to a CSV, use the Append parameter.

Let’s say you have a loop that you want to save each object processed to a CSV. For every iteration, you have a different object you’d like to save to a CSV file. Since you’re calling Export-Csv repeatedly, it will overwrite that CSV file if you don’t use the Append parameter. Without the Append parameter, you will only get the last object, which in most cases is not desired output.

The example below is finding the first five running processes. It’s then entering into a PowerShell Import-Csv foreach loop and recording the Name and ProductVersion properties to the test.csv file one at a time.

Get-Process | Select-Object -First 5 | Foreach-Object {
    $_ | Select-Object Name, ProductVersion | Export-CSV -Path C:\test.csv -Append
}

Without using the Append parameter, you’ll see that only the fifth process will show in the CSV file.

Appending Differences to a CSV FIle

It’s possible to only append property difference to an existing CSV file with Export-Csv. This means that if a CSV row’s columns and the object to record are different, then go ahead and append them.

To only append differences to the CSV file, you’ll need to use the Append and Force parameters together. According to the Microsoft documentation “When Force and Append parameters are combined, objects that contain mismatched properties can be written to a CSV file. Only the properties that match are written to the file. The mismatched properties are discarded.”

To demonstrate, create one object with two properties; Name and Age.

PS51> $Content = [PSCustomObject]@{Name = "Johnny"; Age = "18"}

Now create another object with a Name and Zip property.

PS51> $OtherContent = [PSCustomObject]@{Name = "Joe"; Zip = "02195"}

Each object has differing properties.

Next, create a CSV file from the first object then attempt to append the second object to the CSV without the Force parameter. You’ll receive an error.

PS51> $Content | Export-CSV -Path .\User.csv -NoTypeInformation
PS51> $OtherContent | Export-CSV -Path .\User.csv -NoTypeInformation -Append

However, if you use the Force parameter, Export-Csv will work great.

PS51> $OtherContent | Export-CSV -Path .\User.csv -NoTypeInformation -Append -Force

However, you’ll notice that the Zip column is gone in the CSV file. Use Force with care. It may not provide the intended output.

PS51> Import-Csv -Path .\User.csv

Name   Age
----   ---
Johnny 18
Joe

Export-CSV and the #TYPE String

By default, using Export-CSV with no additional parameters will include a #TYPE string at the top of your CSV file. This string is then followed by the type of object Export-Csv received.

#TYPE System.Diagnostics.Process

Most of the time, this string isn’t actually useful when consuming output. This string is only there in case you need to maintain the type of object that the properties and values came from.

To remove this string, use the NoTypeInformation parameter. This parameter removes this string from the CSV entirely.

Note that as of PowerShell Core, this is no longer necessary.

Summary

Using the Import-CSV and Export-CSV PowerShell cmdlets allow you to easily work with CSV files. These are two useful cmdlets that you should use frequently when dealing with objects and CSV files.

My hope is that with the explanations and examples that I have displayed here that you will be able to have a clear understanding of situations where you can leverage these cmdlets to your benefit.

Want more tips like this? Check out my personal PowerShell blog.