PowerShell offers cmdlets that make it significantly easier to process CSV files. This allows you to avoid the laborious parsing with RegEx or substring operations when you want to create, sort or filter CSV data. You can do this simply by addressing the columns in a CSV file as object attributes.

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.

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:

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:

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

Exporting AD users into a CSV file

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.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:

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:

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:

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:

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:

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:

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:

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

Win the monthly 4sysops member prize for IT pros

Share
2+

Users who have LIKED this post:

  • avatar

Related Posts

0 Comments

Leave a reply

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

*

CONTACT US

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

Sending
© 4sysops 2006 - 2017

Log in with your credentials

or    

Forgot your details?

Create Account