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.

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

Subscribe to 4sysops newsletter!

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.

+5
avataravatar
8 Comments
  1. Dheeraj 1 year ago

    I have a csv file, first column has VDI Names and Second has User Names.

    If I supply a username to my script, it should give me the VDI Name.

    How can I accomplish this?

    0

  2.  Dheeraj:

    You should be able to just import the CSV and use Select-Object to change the column order.  At that point, you can do lookups.

    David F. 

    0

  3. mikel 1 year ago

    hello I go + 600 email verification result and I got problem when I try sort with excel so im trying powershell. I got 4 column emain syntax Mx mailbox. in the email verification result I got like 11 or 16 where either syntax, mx or mailbox are invalid. so what I want to do is only show email with 'valid' in mx, syntax and mailbox and after it show delete the three column. So I only have valid email and there is no more mx, syntax and mail and I can directly copy email and save easily. Ive try this  import-csv C:\Users\hp\Desktop\resultat\listresult.csv | ? Mailbox -notlike *invalid* | ? Syntax -notlike *invalid* | ? MX -notlike *invalid* and work perfectly but dont delete the 3 colum automatically when come with mail who have 3 valid section (mx,syntax,mailbox)

    0

  4. Use your select object at the end to just pick the properties you want.  You can also combine your where-object's.. 

    Remember, for online posts, you're always better of *not* using aliases, skipping parameter names etc.  You want to people following your conversation not to either miss something, or so that they can really understand what you are doing. 

    Also, if you can put up a data sample, that would help.

    David F.
     

    0

  5. Jw 1 year ago

    Hello, I have a csv file that is too large to open in excel. i want to be able to slip this large csv into smaller files that excel can open. mu current file exceeds the max number of columns in excel. Thanks, any help is appreciated. 

    0

  6. Anele 12 months ago

    Good day, I appreciate your assistance. However my problem is filtering column rows in a said csv file. The csv file has a header which is "Comment" and I would like to filter with multiple comments

    0

  7. @JW:  If it is just the number of columns that is the issue, you should be able to break it up with multiple passes and Select-Object.

    Something like that.  The biggest thing is figuring out how you want to chunk it up.

     

    @Anele - there is a way to split this up into multiple columns, I'll see if I can find the article on how to do that.

     

    David F.

    0

  8. Prachi 10 months ago

    Hi , 

     

    I am looking for a powershell script which is able to read from .csv file having Region , Email Id

    Sample.CSV

    Region, Email Id

    South , xza@gmail.com

    North,qwe@gmail.com

     

    My requirement is that powershell read the file and searches for file that contains anything like *North.pdf and emails this file to emailid 'qwe@gmail.com .

     

    I am looking for doing this in loop than writing individual static code to achieve this.

    Kindly help here !

    Thanks in advance.

    0

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