Import-CSV: Reading CSV Files with PowerShell |

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.

excel to csv file

Save this excel file as CSV file with comma separated values. The resulting text file should look like this:

csv file example

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.

If the CSV file uses delimiters other than commas, you can specify them -delimiter parameter. For example, to import a CSV file with semicolon delimiters (;) 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:


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.

If your CSV file has UTF-8 character encoding, make sure you also specify the UTF8 type to avoid encoding errors:

Import-CSV -path C:\PS\my_ad_users.csv -Encoding UTF8

The list of available object properties can be displayed as follows:


You can see that the array rows contain PSCCustomObjects, the elements of which can be accessed by column names from your CSV file. Import-CSV: Read CSV file with PowerShell

You can display all values ​​from a certain column:


or specified object attributes:

$ADusers| Select-Object Email, Phone

Get specific field from CSV file with PowerShell

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


getting specific rows in powershell array

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}

Sometimes you may find the format below more convenient. Here we access the current row of the array through $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)

add new property to powershell object

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