Create, sort and filter CSV files in PowerShell

• Author
• Recent Posts

Wolfgang Sommergut

Wolfgang Sommergut has over 20 years of experience in IT journalism. He has also worked as a system administrator and as a tech consultant. Today he runs the German publication WindowsPro.de.

Latest posts by Wolfgang Sommergut (see all)

• How to change the PowerShell prompt - Wed, Mar 22 2023
• Trim characters from strings in PowerShell - Tue, Mar 14 2023
• Set Chrome, Firefox and Edge as default mail client (mailto handlers) - Mon, Mar 6 2023

CSV is still the most common data exchange format for importing information from various applications into Excel. PowerShell is often used to extract system information as lists and tables which you can easily convert into CSV; you can then analyze this data in a spreadsheet.

The cmdlets Export-Csv and Convertto-Csv enable you to convert tabular data into comma-separated strings or collections of strings. One important note: you should not prepare the data with Format-Table beforehand.

The main difference between the two cmdlets is that Convertto-Csv sends its output to stdout, whereas Export-Csv additionally offers the functionality of Set-Content. Thus, Export-Csv is able to write CSV data into a file providing options such as the Append and NoClobber switches in order to append data or prevent overwriting existing files. In addition, you can determine the character set with the Encoding parameter (ASCII is default).

Writing AD users into a CSV file

For instance, if you want to write all Active Directory users of the Sales OU into a CSV file, you could use this command:

Get-AdUser -Filter * -SearchBase "OU=Sales,DC=contoso,DC=de" | Export-Csv -Path .\users-sales.csv -NoClobber

By default, both cmdlets insert a comma between the columns. However, you can change this behavior using the Delimiter parameter. Sticking with the previous example, the following command will insert a semicolon rather than a comma between columns:

Get-AdUser -Filter * -SearchBase "OU=Sales,DC=contoso,DC=de" | Export-Csv -Path .\users-sales.csv -NoClobber -Delimiter ";"

If you want to work with a tabulator, you can use the expression -Delimiter (9 -as [char]).

Exporting AD users into a CSV file

Importing CSV files

The two counterparts to Export-CSV and Convertto-Csv come into play if you have to import CSV data for further processing. ConvertFrom-Csv and Import-Csv assume that the separator is a comma. If this isn't the case, you must specify the correct separator with the Delimiter parameter.

After you import the CSV file, you can access each column through its title. However, if the column doesn't have a title, ConvertFrom-Csv and Import-Csv will just use the column's first value, which is probably not what you want.

For example, if you have a file named subscribers.csv that contains the column titles first name, name and mail, you can import the data with this command:

$a = Import-Csv -Path '.\subscribers.csv'

$a.Name will display the name column and $a.Mail the mail addresses. But if the CSV file doesn't have column titles, the parameter Header will add them for you:

$a = Import-Csv -Path '.\subscribers.csv' -Header "first name", "name", "mail"

Creating AD users from a CSV file

To remain with the AD user example above, it is a frequently used task to create a large number of accounts by importing the corresponding data from an Excel spreadsheet in order to pass it to New-ADUser:

Import-Csv .\users.csv | New-ADUser

Notice that the column titles in the CSV file must be identical to the parameter names of New-ADUser, and the file must not contain the type of information that PowerShell adds to an export.

If you want to preset the user passwords, you must consider that New-ADUser accepts only a SecureString for passwords. Therefore, you must convert the password first with ConvertTo-SecureString:

Import-Csv .\user.csv | New-ADUser -AccountPassword (ConvertTo-SecureString "P@ssw0rd" -AsPlainText -force)

Sorting and filtering CSV files

Because you can address the columns of a CSV file through their names after the import, it is relatively easy to sort this data. In the example above, with columns for first name, name, and mail, you could sort the file by name with the following command:

Import-Csv -Path '.\subscribers.csv' | sort -Property "Name"

You have all the features of Sort-Object available, such as sorting in ascending order or eliminating duplicates. Similarly, with the help of Select-Object, you can filter columns as well:

Import-Csv -Path '.\subscribers.csv' | select Mail, Name

This command would display only the columns Mail and Name.

If you want to include only particular lines in the result, you usually must work with a comparison operator:

Import-Csv -Path '.\subscribers.csv' | ? Mail -like *meier*

This example would include all records where the email address contains "meier."

If you want to filter a CSV file vertically or horizontally and save the result, you have to write the changes back with Export-Csv:

Import-Csv -Path .\subscribers.csv | ? email -like *meier* | Export-Csv sb.csv

This command would export all entries with "meier" in the email address into sb.csv.