locked
How to get ADUsers properties from a CSV file? RRS feed

  • Question

  • Hi everybody,

    I did a lot of research in Google since it should be very easy to do that, but I still didn't find a solution.

    Let's say I have a CSV file with a list of users, which are in 3 columns called "ID" (which is just a useless counter), "First name" and "Last name". I need to do a simple PowerShell script to take the names from this list, find them in the AD and get back their email addresses. How hard that should be? 

    I used the import-csv cmdlet and a ForEach-Object but then I don't know how to do tell the system to look for the users with the same name (well, actually First name + Last name) and then give me back their mail addresses. Can you give me a clue? 

    Thank you in advance!


    Thursday, March 7, 2019 9:01 PM

Answers

  • I thought my Excel saves CSV files comma delimited. In any case, the comma is the default delimiter assumed by the Import-Csv cmdlet, but you can specify another with the -Delimiter parameter, as in:

    $Lines = Import-Csv "C:\Scripts\Users.csv" -Delimiter ";"



    Richard Mueller - MVP Enterprise Mobility (Identity and Access)

    • Marked as answer by Giant Lizard Friday, March 15, 2019 5:49 PM
    Sunday, March 10, 2019 10:54 PM

All replies

  • If ID is the sAMAccountName (pre-Windows 2000 logon name in ADUC), specify that with the -Identity parameter of Get-ADUser. It uniquely identifies users. First and last name do not. Check the help for Get-ADUser.

    Richard Mueller - MVP Enterprise Mobility (Identity and Access)

    Thursday, March 7, 2019 9:07 PM
  • If ID is the sAMAccountName (pre-Windows 2000 logon name in ADUC), specify that with the -Identity parameter of Get-ADUser. It uniquely identifies users. First and last name do not. Check the help for Get-ADUser.

    Richard Mueller - MVP Enterprise Mobility (Identity and Access)

    Sadly not, I have to work with only the first and last name. The ID column is not really important for this, it's just a counter from 1 to the last user.

    I will update the first post now, in order to not get confused.

    Thursday, March 7, 2019 9:18 PM
  • Similar to below might work. I assume you want the proxyAddresses attribute, since you refer to plural addresses.

    $Lines = Import-Csv "C:\Scripts\Users.csv"
    ForEach ($Line In $Lines)
    {
        $First = $Line."First Name"
        $Last = $Line."Last Name"
        $User = Get-ADUser -Filter {(GivenName -eq $First) -And (Surname -eq $Last)} -Properties proxyAddresses
        Switch ($User.Count)
        {
            0 {"User $First $Last not found"}
            1 {"User $First $Last has email addresses " + $User.proxyAddresses}
            Default {"More than one user $First $Last found"}
        }
    }
    

    proxyAddresses is an array.


    Richard Mueller - MVP Enterprise Mobility (Identity and Access)

    • Proposed as answer by BOfH-666 Sunday, March 10, 2019 8:34 PM
    Thursday, March 7, 2019 9:45 PM
  • Similar to below might work. I assume you want the proxyAddresses attribute, since you refer to plural addresses.

    $Lines = Import-Csv "C:\Scripts\Users.csv"
    ForEach ($Line In $Lines)
    {
        $First = $Line."First Name"
        $Last = $Line."Last Name"
        $User = Get-ADUser -Filter {(GivenName -eq $First) -And (Surname -eq $Last)} -Properties proxyAddresses
        Switch ($User.Count)
        {
            0 {"User $First $Last not found"}
            1 {"User $First $Last has email addresses " + $User.proxyAddresses}
            Default {"More than one user $First $Last found"}
        }
    }

    proxyAddresses is an array.


    Richard Mueller - MVP Enterprise Mobility (Identity and Access)

    Hi and thank you for your help.

    I don't understand what's that ProxyAdresses property, though. What it does?

    Sunday, March 10, 2019 7:28 PM
  • If you have Exchange, the proxyAddresses attribute of AD is an array of email addresses. Otherwise, the mail attribute is a single-valued attribute whose value appears on the "General" tab of ADUC.. If you don't have Exchange, substitute "mail" for "proxyAddresses" in the code I posted. Documentation on proxyAddresses:

    https://docs.microsoft.com/en-us/windows/desktop/ADSchema/a-proxyaddresses

    https://blogs.technet.microsoft.com/exchange/2005/01/10/fun-with-changing-e-mail-addresses/


    Richard Mueller - MVP Enterprise Mobility (Identity and Access)


    • Proposed as answer by BOfH-666 Sunday, March 10, 2019 8:34 PM
    • Edited by Richard MuellerMVP Sunday, March 10, 2019 8:35 PM Added link
    Sunday, March 10, 2019 8:29 PM
  • If you have Exchange, the proxyAddresses attribute of AD is an array of email addresses. Otherwise, the mail attribute is a single-valued attribute whose value appears on the "General" tab of ADUC.. If you don't have Exchange, substitute "mail" for "proxyAddresses" in the code I posted. Documentation on proxyAddresses:

    https://docs.microsoft.com/en-us/windows/desktop/ADSchema/a-proxyaddresses

    https://blogs.technet.microsoft.com/exchange/2005/01/10/fun-with-changing-e-mail-addresses/


    Richard Mueller - MVP Enterprise Mobility (Identity and Access)


    Ok, yes, it's indeed MailAddress what I was looking for :) that seems to work to me.

    The script still doesn't work properly though, and I think it's because of my csv file. I actually created an excel file, filled the 3 columns and then saved as csv. I think that's not the kind of csv file Powershell likes, right? I mean, if I do the same thing from a txt file, it works.

    One of my colleagues told me it could be because of the fact that Excel saves csv files separating columns with a semicolon instead of a comma, could it be the reason?

    Thank you again for your help!

    Sunday, March 10, 2019 10:25 PM
  • I thought my Excel saves CSV files comma delimited. In any case, the comma is the default delimiter assumed by the Import-Csv cmdlet, but you can specify another with the -Delimiter parameter, as in:

    $Lines = Import-Csv "C:\Scripts\Users.csv" -Delimiter ";"



    Richard Mueller - MVP Enterprise Mobility (Identity and Access)

    • Marked as answer by Giant Lizard Friday, March 15, 2019 5:49 PM
    Sunday, March 10, 2019 10:54 PM
  • I thought my Excel saves CSV files comma delimited. In any case, the comma is the default delimiter assumed by the Import-Csv cmdlet, but you can specify another with the -Delimiter parameter, as in:

    $Lines = Import-Csv "C:\Scripts\Users.csv" -Delimiter ";"



    Richard Mueller - MVP Enterprise Mobility (Identity and Access)

    Whomma! I didn't know it was possible to change the delimiter in cmdlet :O tomorrow I'll try it and let you know! Thank you!
    Sunday, March 10, 2019 11:31 PM
  • It worked! Thank you for your help ^^
    Friday, March 15, 2019 5:51 PM
  • You are welcome!

    Richard Mueller - MVP Enterprise Mobility (Identity and Access)

    Friday, March 15, 2019 6:12 PM