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

[SS9911]

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:

email address removed for privacy reasons

email address removed for privacy reasons

etc.

No headers.

Someone will help? :(

[#2]

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

[#3]

@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