The PowerShell cmdlet Import-Csv allows you to import a CSV file to a custom PowerShell object easily. And with Export-Csv you can export the data from an object to a CSV file.
Contents of this article

We IT professionals work with data all the time, whether it's a group of servers, employee names syncing to Active Directory, exports from databases, or what have you. One of the most pervasive and easy-to-use formats is the comma-separated-value (CSV) file. This file is simple in nature yet so powerful due to its standardized columnar spreadsheet-like format without the bloat of Excel.

Since a CSV file is simply a text file written in a structured manner, many programming languages have built-in commands to manipulate CSV files. In PowerShell, we have the Import-Csv and Export-Csv commands. These commands can understand the structure of a CSV file and both simultaneously read and write to a CSV through an object conversion process. Both of these commands are truly lifesavers if you're used to working with CSV files in other languages.

To demonstrate working with CSV files in PowerShell, let's first go over a super-simple example of when and how to use both Import-Csv and Export-Csv.

Import a CSV file ^

Perhaps I have an object that's returning some standard set of properties. For demonstration purposes, I'll just create an array of pscustomobject objects, but these could be any old objects. Perhaps this set of objects represents an export from an HR database.

$employees = @(
    [pscustomobject]@{
        FirstName = 'Adam'
        LastName = 'Bertram'
        Department = 'Executive Office'
    }
    [pscustomobject]@{
        FirstName = 'Don'
        LastName = 'Jones'
        Department = 'Janitorial Services'
    }
)

I now have a set of objects in memory to work with. I'd like to export data from these objects to a structured CSV file. To do this, I can use the Export-Csv command. This command takes any object returned to the pipeline, creates a CSV file on the filesystem, creates all the necessary CSV columns, and then populates the fields with the property values. Since Export-Csv accepts pipeline input, the most common way to invoke this command is to pipe the objects directly to it.

$employees | Export-Csv -Path C:\Employees.csv

Opening this up in Excel then looks like this:

CSV file with type information

CSV file with type information

But notice all of that "type" stuff at the top. By default, Export-Csv adds this information when used. I personally always disable this by using the -NoTypeInformation parameter. Also, you should know that Export-Csv will overwrite any existing CSV at will. Most of the time I always want to append to an existing CSV. So be aware that the ‑Append parameter is always available to you as well.

Export a CSV file ^

On the flip side, PowerShell can quickly import an existing CSV file as well. Import-Csv not only reads the CSV file, but since the command natively understands the CSV format, it then automatically creates pscustomobject objects from each row in the CSV.

I've got my CSV file I just created, so let's now read that CSV file and create some objects from it. To do that, I'll run Import-Csv -Path C:\Employees.csv. This gives me an output that looks like this:

Import Csv

Import Csv

Easy enough, right? Most CSV files are comma-separated (hence the name) but occasionally you'll find some that are tab-separated or even separated with a pipe (|) symbol. Luckily, Import-Csv allows you to change the delimiter to anything you wish using the -Delimiter parameter. By using the -Delimiter parameter, it doesn't matter whether each CSV column is separated with a comma or a wingding. If you can type it on the keyboard, you can specify it as a delimiter.

Subscribe to 4sysops newsletter!

Working with CSV files is great in PowerShell. As long as the CSV file is in a properly delimited format (when reading) or your objects have standard properties (when writing), you'll be okay. If not, you may run into situations where PowerShell unexpectedly does not add some object properties to the CSV. Likewise, an error might occur, letting you know the existing CSV field names do not match up to the object properties you're trying to write to it with.

avatar
1 Comment
  1. Good one!

    It just seems like you inverted the Import and Export subtitles...

Leave a reply

Please enclose code in pre tags

Your email address will not be published. Required fields are marked *

*

© 4sysops 2006 - 2021

CONTACT US

Please ask IT administration questions in the forums. Any other messages are welcome.

Sending

Log in with your credentials

or    

Forgot your details?

Create Account