Forum Discussion

SS9911's avatar
SS9911
Copper Contributor
Sep 07, 2023

Get users from CSV, foreach user, get country, name, etc., then Export

Hi!

I have a problem with PS query. I have a CSV file with +1000 userprincipalname. Id like to export that UPN and for each user get info like  userprincipalname, title, country, department, enabled, then export to excel.

My example queries:

$csv = gc "C:\New folder\Users.csv"
$Users=@()

$csv | Foreach{
$elements=$_ -split(";")
$Users+= ,@($elements[0])
}


ForEach ($User in $Users) {
Get-ADUser -filter "userPrincipalName -eq '$User'" -Properties * | Select-Object userprincipalname, title, country, department, enabled
}

 

OR

 

$Users = Get-contect -path "C:\New folder\Users.csv"
ForEach ($User in $Users) {
Get-ADUser -filter "userPrincipalName -eq '$User'" -Properties * | Select-Object userprincipalname, title, country, department, enabled
}

 

OR

 

$Users = Get-contect -path "C:\New folder\Users.csv"
ForEach ($User in $Users) {
Get-ADUser -filter $User -Properties * | Select-Object userprincipalname, title, country, department, enabled
}

 

My excel has UPN only, like:

mailto:email address removed for privacy reasons

mailto:email address removed for privacy reasons

etc.

No headers.

 

Someone will help? 😞

@Windows PowerShell
Active Directory
Active Directory (AD)

2 Replies

Sort By
  • LainRobertson's avatar
    LainRobertson
    Silver Contributor
    Sep 07, 2023

    SS9911 

     

    Hi.

     

    If your CSV has neither headers nor delimited columns, then it's just a text file and not a CSV. Though at the end you say "my Excel", and Excel (.xlsx file extension) is a whole different discussion.

     

    Assuming Excel has nothing to do with this, then you can get what you want with a simple script.

     

    Input file

     

    Example

    Get-Content -Path "D:\Data\Temp\Forum\myInputFile.csv" |
    ForEach-Object {
        Get-ADUser -Filter { (userPrincipalName -eq $_) } -Properties country, department, enabled, title, userPrincipalName
    } |
        Select-Object -Property userPrincipalName, enabled, title, department |
            Export-Csv -NoTypeInformation -Path "D:\Data\Temp\Forum\myExportFile.csv";

     

    Output file

     

     

    Cheers,

    Lain

  • FranciscoNabas's avatar
    FranciscoNabas
    Copper Contributor
    Sep 07, 2023
    Take a look at the 'Import-Csv', 'Export-Csv' Cmdlets. they do all the heavy lifting for you. Then you can use 'Select-Object' to select the properties you want.
    https://learn.microsoft.com/powershell/module/microsoft.powershell.utility/import-csv

Resources