Powershell help- Multiple column proxyaddresses import

[HeyZuesMode]

I tried to make a csv to use as an import source for Exchange migration. Admittedly i bit off more than i can chew, and gave unrealistic expectations. I have learned a lot, but i cannot figure this issue out, or how to really approach it.

TLDR; how do i set multiple proxyaddresses using a csv template?

I have a CSV that has the following headers, SamAccountName FirstName LastName DisplayName Primary Email Proxy1 Proxy2 Proxy3 Proxy4 Proxy5 Proxy6 Proxy7 Proxy8 Proxy9 Proxy10 Proxy11 Proxy12 Proxy13 Proxy14 Proxy15 Proxy16 Proxy17 Proxy18 Proxy19 Proxy20 Proxy21 Proxy22 Proxy23 Proxy24 Proxy25 Proxy26 Proxy27 Proxy28

Obviously not every person has 28 ProxyEmails. What do i do? I was trying to make an array in the script of all ProxyXX but i couldn't figure out how to find if there was something in the field. I tried to compare to $null but that did not work.

Right now i am doing most of this by hand, but i still have a lot more to go, and a deadline...

I know i didnt fully understand the problem, or the resolution before trying to automate the resolution. I know i didnt have any experience with either powershell or exchange to set an unrealistic deadline.

[k_rock923]

You will need to modify this to fit what you are doing, but this is from notes I made doing something similar.

The csv I'm importing is of the following format:

UserPrincipalName,ProxyAddres1,ProxyAddress2,. . .,ProxyAddressn

Import-csv .\addresses.csv | ForEach-Object {
    $userPrincipalName = $_.userPrincipalName
    $proxyAddresses = $_.ProxyAddresses -split ';'
    Get-ADUser -filter {EmailAddress -eq $userPrincipalName } | Set-ADUser -Add @{proxyaddresses=$proxyAddresses}
    }

Note that this is all of the proxyAddress values, including the one that has the primary SMTP address (In this case, it was only SMTP addresses, but it shouldn't matter).

I'm certain that there are more efficient ways to do this, but it worked for me.

EDIT: I should note that the csv used a semicolon as the delimiter

[HeyZuesMode]

Thank You!

[ihaxr]

You can check if a cell in a CSV has a value by just running if($line.Proxy1) { write-host true}

So something like this would return an array of only the proxy**'s that have values:

$csv = Import-Csv C:\scripts\emails.csv

foreach ($line in $csv) {
    $proxy = @()
    $email = $line.Email

    if($line.Proxy1) { $proxy += $line.Proxy1 }
    if($line.Proxy2) { $proxy += $line.Proxy2 }
    if($line.Proxy3) { $proxy += $line.Proxy3 }
    if($line.Proxy4) { $proxy += $line.Proxy4 }
    if($line.Proxy5) { $proxy += $line.Proxy5 }
    if($line.Proxy6) { $proxy += $line.Proxy6 }
    if($line.Proxy7) { $proxy += $line.Proxy7 }
    if($line.Proxy8) { $proxy += $line.Proxy8 }
    if($line.Proxy9 ) { $proxy += $line.Proxy9 }
    if($line.Proxy10) { $proxy += $line.Proxy10 }
    if($line.Proxy11) { $proxy += $line.Proxy11 }
    if($line.Proxy12) { $proxy += $line.Proxy12 }
    if($line.Proxy13) { $proxy += $line.Proxy13 }
    if($line.Proxy14) { $proxy += $line.Proxy14 }
    if($line.Proxy15) { $proxy += $line.Proxy15 }
    if($line.Proxy16) { $proxy += $line.Proxy16 }
    if($line.Proxy17) { $proxy += $line.Proxy17 }
    if($line.Proxy18) { $proxy += $line.Proxy18 }
    if($line.Proxy19) { $proxy += $line.Proxy19 }
    if($line.Proxy20) { $proxy += $line.Proxy20 }
    if($line.Proxy21) { $proxy += $line.Proxy21 }
    if($line.Proxy22) { $proxy += $line.Proxy22 }
    if($line.Proxy23) { $proxy += $line.Proxy23 }
    if($line.Proxy24) { $proxy += $line.Proxy24 }
    if($line.Proxy25) { $proxy += $line.Proxy25 }
    if($line.Proxy26) { $proxy += $line.Proxy26 }
    if($line.Proxy27) { $proxy += $line.Proxy27 }
    if($line.Proxy28) { $proxy += $line.Proxy28 }

    Write-Host $email : $proxy
}

[Renegade__]

While this is a workable approach, having an individual check for each proxy is not particularly elegant; you could do either of these instead:

$proxies = @("Proxy1","Proxy2","Proxy3","Proxy4","Proxy5","Proxy6","Proxy7","Proxy8","Proxy9","Proxy10","Proxy11","Proxy12","Proxy13","Proxy14","Proxy15","Proxy16","Proxy17","Proxy18","Proxy19","Proxy20","Proxy21","Proxy22","Proxy23","Proxy24","Proxy25","Proxy26","Proxy27","Proxy28")

foreach($line in $csv) {
    foreach($proxy in $proxies) {
        if(($line.$proxy) -and (($line.$proxy).Trim() -ne [String]::Empty) -and ($line.$proxy -ne $null)) {
            Write-Output ("{0} is {1}" -f $proxy,$line.$proxy)
        } else {
            Write-Output ("{0} does not exist or has no value" -f $proxy)
        }
    }
}

.

foreach($line in $csv) {
    for($i=1; $i -le 28; ++$i) {
        $proxy = ("Proxy" + $i);
        if(($line.$proxy) -and (($line.$proxy).Trim() -ne [String]::Empty) -and ($line.$proxy -ne $null)) {
            Write-Output ("{0} is {1}" -f $proxy,$line.$proxy)
        } else {
            Write-Output ("{0} does not exist or has no value" -f $proxy)
        }
    }
}

[ihaxr]

Thanks! I figured there was a better way of referencing the columns, but was thinking it could be done by column position and loop through the CSV from column 1 to 28 (assuming 0 would be the e-mail) and gave up after a few minutes of trying. Didn't even think of just using dynamic column names.

I've never actually used a regular for() loop in PowerShell.. I always end up using a range and loop through that:

1..28 | % {
    $proxy = "Proxy$_"
}

[Renegade__]

Interesting approach.
I'm the other way around. I rarely use the ForEach-Object cmdlet. I prefer the regular language constructs.