csv powershell manipulation

This topic contains 8 replies, has 4 voices, and was last updated by Profile photo of Rob Simmers Rob Simmers 1 year, 8 months ago.

  • Author
    Posts
  • #25255
    Profile photo of Ian Fermer
    Ian Fermer
    Participant

    Ok, after watching the first MVA of powershell I would figure I would try and do a csv which I'm having quite a lot of success on

    However now running into issues where certain values require a criteria to meet

    Creating a mailbox via csv the UPN suffix is my hardest part and I've tried putting it together multiple ways.

    Here is what I have

    $c = Import-Csv C:\Temp\testrun.csv -Delimiter ','

    foreach ($column in $c)

    {
    $upn = $column.Primaryemail + '@' + $column.primarydomain

    {new-mailbox -name "$($column.Firstname) $($column.lastname)" -Password (ConvertTo-SecureString $column.password -AsPlainText -force) -UserPrincipalName $upn

    }
    }

    But all this does is output the second part {new-mailbox...}

    I remove the $upn = $column.Primaryemail + '@' + $column.primarydomain and all is fine without ofcourse a acceptable UPN being set.

    What am I missing in this format, I feel I need to watch the second set of MVA powershell videos

  • #25256
    Profile photo of Martin Nielsen
    Martin Nielsen
    Participant

    You shouldn't be wrapping your New-Mailbox cmdlet in { } brackets, as this essentially tells powershell you want to create a scriptblock, not execute the command. Scriptblocks are used, for example, when you want to pass commands to a remote machine using Invoke-Command.

    Other than that it looks fine for a first powershell script, apart from the passwords being stored in cleartext. 🙂

  • #25257
    Profile photo of Ian Fermer
    Ian Fermer
    Participant

    Dammit, your right, had a deadline on a tested scripted so had to use someone elses :(, I'm going to keep working on mine though, man love MVA powershell and powershell itself.

  • #25268
    Profile photo of Stephen Owen
    Stephen Owen
    Participant

    I think your UPN problem is coming from this line:

    $upn = $column.Primaryemail + '@' + $column.primarydomain

    I really, really dislike this method of building a string, which is called String Concatenation. You're just kind of jamming things together and PowerShell will freak out if your objects on either side of the '+' sign aren't of the same type.

    There is another method you can use that looks a little strange, but will solve your concatenation problem.

    $upn = "$($column.Primaryemail)@$($column.primarydomain)"

    This syntax tells PowerShell to build a string and treat everything within the $() as a string, which will help you solve your UPN issue. Give it a shot!

  • #25271
    Profile photo of Rob Simmers
    Rob Simmers
    Participant

    I like string format:

    $upn = "{0}@{1}" -f $column.Primaryemail ,$column.primarydomain

    You don't need double quotes or any nonsense typically when you build the string and I find that it just always works.

  • #25279
    Profile photo of Ian Fermer
    Ian Fermer
    Participant

    I actually updated it with the UPN now coming from the excel
    $c= @()
    $c = Import-Csv C:\Temp\users.v1-testlogins.csv -Delimiter ','

    foreach ($column in $c)

    {
    $upn = $column.UserPrincipalName
    $proxy = $User.ProxyAddresses -split ';'
    new-mailbox -name "$($column.Firstname) $($column.lastname)" -Password (ConvertTo-SecureString $column.password -AsPlainText -force) -UserPrincipalName $upn -FirstName $column.Firstname -LastName $column.Lastname -PrimarySmtpAddress $column.PrimarySmtpAddress -OrganizationalUnit $OU -Database $column.Database
    Set-Mailbox -Identity $SAM -EmailAddresses @{add= $proxy}
    Add-ADGroupMember -Identity 'omited' -Members $SAM
    }

    Rob, don't really understand the {0} and {1} with -f, can you explain.

    Also running through the syntax of above it seems to Set-Mailbox and Add-ADGroupMember the first one running through the foreach but misses the rest, sorry I'm not asking to make it work but try to understand how the foreach works

  • #25280
    Profile photo of Ian Fermer
    Ian Fermer
    Participant

    Ah no problem now Rob I understand it, pretty cool and now in use!

    Just going to figure out this weekend how to parse more then one command through a foreach, thanks guys for all your help!

  • #25387
    Profile photo of Ian Fermer
    Ian Fermer
    Participant

    So it has been expanded a little however, in the foreach I need to skip a command if it is empty, however running into a problem, first current setup

    $c= @[]
    $c = Import-Csv C:\Temp\users.v1-testlogins.csv -Delimiter ','
    
    foreach [$column in $c] 
    
        {
        $upn = $column.UserPrincipalName
        $sam = "{0}.{1}" -f $column.Firstname, $column.Lastname
        $proxy = $Column.ProxyAddresses -split ';'
        new-mailbox -name "$[$column.Firstname] $[$column.lastname]" -Password [ConvertTo-SecureString $column.password -AsPlainText -force] -UserPrincipalName $upn -FirstName  $column.Firstname -LastName $column.Lastname -PrimarySmtpAddress $column.PrimarySmtpAddress -OrganizationalUnit $column.OU -Database $column.Database -SamAccountName $sam
       
        Set-Mailbox -Identity $column.UserPrincipalName -EmailAddresses @{add=$proxy}
             
        Add-ADGroupMember -Identity "Name" -Members $sam
    }
    

    Works great, however sometimes the proxy is empty and I want to skip it, so I came up with this

     if
        [$column.proxyaddresses -ne '']{
        foreach [$column in $c]
        {
        $proxy = $Column.ProxyAddresses -split ';'
        Set-Mailbox -Identity $column.UserPrincipalName -EmailAddresses @{add=$proxy}
        }
        }

    However because I'm specifying the identity it fails of course, so I'm trying to skip loop if field (proxyaddress) is blank..

    Of course now going through this in my head and writing it out I came up with

      foreach [$column in $c]
        {
        $proxy = $Column.ProxyAddresses -split ';'
        if
        [$column.proxyaddresses -ne '']{
        Set-Mailbox -Identity $column.UserPrincipalName -EmailAddresses @{add=$proxy}
        }
        }
    

    If anyone is interested

  • #25391
    Profile photo of Rob Simmers
    Rob Simmers
    Participant

    There are couple of ways to do it:

    if ($column.proxyaddresses) {
    
    if ([string]::IsNullOrEmpty($column.proxyaddresses) {
    

    Make sure you have this line wrapped in the if as well:

    $proxy = $Column.ProxyAddresses -split ';'
    

    Using the -Split switch, you may not get an error, but you can't Split null:

    PS C:\Windows\System32\WindowsPowerShell\v1.0> $nothing = $null
    
    PS C:\Windows\System32\WindowsPowerShell\v1.0> $nothing -Split ","
    
    
    PS C:\Windows\System32\WindowsPowerShell\v1.0> $nothing.Split(",")
    You cannot call a method on a null-valued expression.
    At line:1 char:1
    + $nothing.Split(",")
    + ~~~~~~~~~~~~~~~~~~~
        + CategoryInfo          : InvalidOperation: (:) [], RuntimeException
        + FullyQualifiedErrorId : InvokeMethodOnNull
    

You must be logged in to reply to this topic.