Windows OS Hub
  • Windows
    • Windows 11
    • Windows Server 2022
    • Windows 10
    • Windows Server 2019
    • Windows Server 2016
  • Microsoft
    • Active Directory (AD DS)
    • Group Policies (GPOs)
    • Exchange Server
    • Azure and Microsoft 365
    • Microsoft Office
  • Virtualization
    • VMware
    • Hyper-V
  • PowerShell
  • Linux
  • Home
  • About

Windows OS Hub

  • Windows
    • Windows 11
    • Windows Server 2022
    • Windows 10
    • Windows Server 2019
    • Windows Server 2016
  • Microsoft
    • Active Directory (AD DS)
    • Group Policies (GPOs)
    • Exchange Server
    • Azure and Microsoft 365
    • Microsoft Office
  • Virtualization
    • VMware
    • Hyper-V
  • PowerShell
  • Linux

 Windows OS Hub / PowerShell / Import-CSV: Reading CSV Files with PowerShell

March 17, 2024

Import-CSV: Reading CSV Files with PowerShell

You can use the 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 from the CSV file is a separate property. This article discusses the specifics of reading data from CSV files by using the Import-CSV cmdlet.

CSV files are used to store tabular data. For example, we have created a simple Excel spreadsheet containing AD user info 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 the array.

Our task is to import this CSV file into PowerShell and make changes to Active Directory for each user from the file.

In the previous article, we explained how to use the Export-CSV cmdlet to save PowerShell objects to a CSV file.

csv file from excel

Save this Excel file as a 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, all you have to do is 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 with the -Delimiter parameter. For example, to import a CSV file with a semicolon delimiter (;) 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 a delimiter default in your system (depending on the language and regional Windows settings), use the -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. The cmdlet converts the contents of the file to an array. All the values from the CSV file are represented as rows.

If your CSV file contains 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:

$ADUsers|get-member

You can see that the array rows contain PSCustomObjects, 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:

$ADusers.email

Or the specified object attributes:

$ADusers| Select-Object Email, Phone

Get specific fields from CSV file with PowerShell

Another useful option is to access a specific object in the array directly. For example, run the following command to get the value of row 4 (numbering starts at 0):

$ADusers[3]

Getting specific rows in a Powershell array

If you want to change the attributes of Active Directory users that you have specified in your CSV file, simply use the ForEach loop to iterate through all the rows in the $ADUsers array (we’ll use the Set-ADUser cmdlet from the AD PowerShell module to make the changes).

# 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 the $User variable we defined in the 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 extra 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)

powershell add new property to object

Here are more scenarios in which you can use the Import-CSV cmdlet to perform administrative operations on a large number of objects:

  • Export the Exchange mailboxes to PST files:
    Import-CSV "C:\ps\user_to_export_pst.csv" | ForEach {New-MailboxExportRequest -Mailbox $_.username -FilePath $_.UNCPathtoPst
  • Add users to an AD security group with PowerShell: I
    mport-CSV .\users.csv -Header users | ForEach-Object {Add-AdGroupMember -Identity ‘TestADGroup’ -members $_.users}
    Or a similar case of adding users to a Microsoft Entra ID (Azure AD) group:
    Import-CSV "C:\PS\add_m365_members.csv" | ForEach-Object {
    Add-UnifiedGroupLinks –Identity it_dept –LinkType Members –Links $_.member
    }
  • Get information about the user from AD:
    Import-Csv c:\ps\usernsme_list.csv | ForEach {
    Get-ADUser -identity $_.user -Properties Name, telephoneNumber |
    Select Name, telephoneNumber |
    Export-CSV c:\ps\export_ad_list.csv -Append -Encoding UTF8
    }
    Or use Set-ADUser to change the attributes of specific users:Import-Csv "C:\ps\modifyad_users.csv" | foreach {Set-ADUser -Identity $_.SamAccountName –Title $_.Title -MobilePhone $_.MobilePhone}Update users’ photos in Active Directory:
    Import-Csv C:\PS\import.csv |%{Set-ADUser -Identity $_.AD_username -Replace @{thumbnailPhoto=([byte[]](Get-Content $_.Photo -Encoding byte))}}
  • Create bulk users in Active Directory from a list in a CSV file.
0 comment
0
Facebook Twitter Google + Pinterest
PowerShellWindows 10Windows Server 2019
previous post
Save Sent Items in Shared Mailbox on Exchange (Microsoft 365)
next post
Find Out Which Process is Listening on a Specific Port on Windows

Related Reading

Wi-Fi (Internet) Disconnects After Sleep or Hibernation on...

March 15, 2024

Fix: Remote Desktop Licensing Mode is not Configured

August 24, 2023

How to Install Remote Server Administration Tools (RSAT)...

March 17, 2024

How to Find the Source of Account Lockouts...

March 12, 2024

Managing Windows Firewall Rules with PowerShell

March 11, 2024

How to Delete Old User Profiles in Windows

March 15, 2024

Start Menu or Taskbar Search Not Working in...

April 22, 2025

How to Install and Configure Free Hyper-V Server...

March 16, 2024

Leave a Comment Cancel Reply

join us telegram channel https://t.me/woshub
Join WindowsHub Telegram channel to get the latest updates!

Recent Posts

  • Map a Network Drive over SSH (SSHFS) in Windows

    May 13, 2025
  • Configure NTP Time Source for Active Directory Domain

    May 6, 2025
  • Cannot Install Network Adapter Drivers on Windows Server

    April 29, 2025
  • Change BIOS from Legacy to UEFI without Reinstalling Windows

    April 21, 2025
  • How to Prefer IPv4 over IPv6 in Windows Networks

    April 9, 2025
  • Load Drivers from WinPE or Recovery CMD

    March 26, 2025
  • How to Block Common (Weak) Passwords in Active Directory

    March 25, 2025
  • Fix: The referenced assembly could not be found error (0x80073701) on Windows

    March 17, 2025
  • Exclude a Specific User or Computer from Group Policy

    March 12, 2025
  • AD Domain Join: Computer Account Re-use Blocked

    March 11, 2025

Follow us

  • Facebook
  • Twitter
  • Telegram
Popular Posts
  • Install and Manage Windows Updates with PowerShell (PSWindowsUpdate)
  • Fix: Remote Desktop Licensing Mode is not Configured
  • How to Delete Old User Profiles in Windows
  • How to Install Remote Server Administration Tools (RSAT) on Windows
  • Configuring Port Forwarding in Windows
  • Start Menu or Taskbar Search Not Working in Windows 10/11
  • Adding Drivers into VMWare ESXi Installation Image
Footer Logo

@2014 - 2024 - Windows OS Hub. All about operating systems for sysadmins


Back To Top