- 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]).
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:
Subscribe to 4sysops newsletter!
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.
Join the 4sysops PowerShell group!
Your question was not answered? Ask in the PowerShell forum!
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?
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.
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)
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.
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.
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
@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.
I am looking for a powershell script which is able to read from .csv file having Region , Email Id
Region, Email Id
South , firstname.lastname@example.org
My requirement is that powershell read the file and searches for file that contains anything like *North.pdf and emails this file to emailid 'email@example.com .
I am looking for doing this in loop than writing individual static code to achieve this.
Kindly help here !
Thanks in advance.