you can use import-csv PowerShell cmdlet to read the contents of a CSV file. Once you load the contents of the CSV file, you get a PowerShell object in which each column of the CSV file is a separate property. This article discusses the specifics of reading data from CSV files using the Import-CSV cmdlet.
CSV files are used to store tabular data. For example, we’ve created a simple Excel spreadsheet containing AD user information with the following structure: ADlogin, Email, Phone, Department, Note that the first row of the spreadsheet contains the column names. In PowerShell, these will be available as object attributes in an array.
Our task is to import this CSV file into PowerShell and make changes from the file to Active Directory for each user.
Save this excel file as CSV file with comma separated values. The resulting text file should look like this:
Now, to import a CSV file into PowerShell, you only need to specify its name (path):
$ADUsers=Import-CSV -path C:\PS\my_ad_users.csv
The Import-CSV cmdlet converts data from a CSV file into a PowerShell array.
;) Use the command:
$ADUsers=Import-CSV -path C:\PS\my_ad_users.csv -Delimiter ";"
If you use a tab character as a delimiter in CSV, you can specify it as follows: -Delimiter `t
If you want to use the delimiter defaults in your system (based on language and regional Windows settings), use -UseCulture flag.
Run this command to get your system-default delimiter in Windows:
(Get-Culture).TextInfo.ListSeparator
Now if you call the $ADUsers variable, it will display the contents of your CSV file. Converts the contents of the cmdlet file to an array. All values in the CSV file are represented as rows.
Import-CSV -path C:\PS\my_ad_users.csv -Encoding UTF8
The list of available object properties can be displayed as follows:
$ADUsers|get-member
You can see that the array rows contain PSCCustomObjects, the elements of which can be accessed by column names from your CSV file. 
You can display all values from a certain column:
$ADusers.email
or specified object attributes:
$ADusers| Select-Object Email, Phone
Another useful option is to directly access a specific item in the array. For example, run the following command to get the value of row 4 (numbering starts at 0):
$ADusers[3]
If you want to change the attributes of the Active Directory users specified in your CSV file, just use a ForEach loop to iterate through all the rows in the $ADUsers array (we’ll use the Set-ADUser cmdlet from AD PowerShell to change module to do).
# Iterating over all the rows in the array one by one
$ADUsers | ForEach-Object {
# In this example, the $_. statement means we are accessing the current row of the array You can get the value of a particular column from the current row by accessing it by the column name. For example, to display the ADLogin value from the current line:
Write-host $_.ADLogin
# Now we search for a user in AD by the value of the ADLogin column and change its attribute values:
Set-ADUser -Identity $_.AdLogin -MobilePhone $_.Phone –Department $_.Department –EmailAddress $_.Email
}
You can replace this script with a more compact PowerShell one-liner:
Import-Csv C:\PS\my_ad_users.csv | foreach {Set-ADUser -Identity $_.AdLogin -MobilePhone $_.Phone –Department $_.Department –EmailAddress $_.Email}
$User Variable we defined in ForEach statement:
ForEach ($User in $ADUsers){
$samaccountname = $User.ADlogin
$email = $user.Email
Write-host $samaccountname
Write-host $email
}
In some cases, when processing array elements from a CSV file, you may need to add an additional column to the array. For example, if you want to add the current date to the $ADUsers object:
$ADUsers | add-member -membertype NoteProperty -name Date -value (Get-Date -Format dd/MM/yyyy)
Here are more scenarios in which you can use the Import-CSV cmdlet to perform administrative operations on a large number of objects:
Leave a Comment