none
Powershell import users from csv, get department, add to belonging member group. RRS feed

  • Question

  • Hello,

    I am working as an intern at a company and I need to make a script where it reads the .csv of an export from AFAS, edit/add where needed and then import to AD. I've gotten everything to work apart from one thing. Because AFAS does not have the member groups like AD has, I need to manually add them. I want to do this by having the syntax look for the attribute ''department'' in the .csv and then adding that value as member group to the user.

    I've done some searching online and I failed to find any script that gets the attribute from a file to then assign a member group to it, I have only found scripts where you need to put either the user in manually or the member group manually. As we'll be importing users in bulks of 10-xxx employees, this is not an option. I'm thinking it's supposed to be something like this:

    #Where Test & Test Accountants B.V. is the department and Accountants will be the member group the user is added to.

    $Memberof = @{ 'Test & Test Accountants B.V.' = 'Accountants'; } ADD-ADGroupMember | Select-Object *, @{ Name = "Memberof"; Expression = { $Memberof[$_.Department] }}

    I was hoping you could help me further with this.

    Thanks in advance.

    Friday, February 26, 2016 1:08 PM

Answers

  • Hi,

    Here's a starter:

    Import-Csv .\userInput.csv | ForEach {
    
        If ($_.Department -eq 'Test & Test Accountants B.V.') {
    
            Add-ADGroupMember -Identity 'Accountants' -Members $_.Username
    
        }
    
        If ($_.Department -eq 'Smart Sales B.V.') {
    
            Add-ADGroupMember -Identity 'Sales' -Members $_.Username
            Add-ADGroupMember -Identity 'Marketing' -Members $_.Username
    
        }
    
        # If (....)
    
    }

    This assumes at least a header of Username and Department in your input CSV file.


    Friday, February 26, 2016 1:20 PM
  • Hi,

    Here's a starter:

    Import-Csv .\userInput.csv | ForEach {
    
        If ($_.Department -eq 'Test & Test Accountants B.V.') {
    
            Add-ADGroupMember -Identity 'Accountants' -Members $_.Username
    
        }
    
        If ($_.Department -eq 'Smart Sales B.V.') {
    
            Add-ADGroupMember -Identity 'Sales' -Members $_.Username
            Add-ADGroupMember -Identity 'Marketing' -Members $_.Username
    
        }
    
        # If (....)
    
    }

    This assumes at least a header of Username and Department in your input CSV file.


    Thanks alot for this.

    Friday, February 26, 2016 2:18 PM

All replies

  • I recommend that you start by learning PowerShell.  Once you have an actual script rather than something you copied without understanding it we will be batter able to help you.

    You can find many scripts in the Gallery that you can use to do this with some adjustments.

    You request is not as simple as you think and you have not provided enough information for us to be of much help.  After learning more about PowerShell and AD you will be better equipped to ask your question.


    \_(ツ)_/

    Friday, February 26, 2016 1:17 PM
  • Hi,

    Here's a starter:

    Import-Csv .\userInput.csv | ForEach {
    
        If ($_.Department -eq 'Test & Test Accountants B.V.') {
    
            Add-ADGroupMember -Identity 'Accountants' -Members $_.Username
    
        }
    
        If ($_.Department -eq 'Smart Sales B.V.') {
    
            Add-ADGroupMember -Identity 'Sales' -Members $_.Username
            Add-ADGroupMember -Identity 'Marketing' -Members $_.Username
    
        }
    
        # If (....)
    
    }

    This assumes at least a header of Username and Department in your input CSV file.


    Friday, February 26, 2016 1:20 PM
  • This:

    ADD-ADGroupMember | Select-Object *, @{ Name = "Memberof"; Expression = { $Memberof[$_.Department] }}

    is nothin g.  It cannot be used and show a complete lack of knowledge of PowerShell and AD.

    use help for "Add-ADGroupMember" and "Select-Object" and you will begin to understand something about the difficulty of you question.


    \_(ツ)_/

    Friday, February 26, 2016 1:20 PM
  • This:

    ADD-ADGroupMember | Select-Object *, @{ Name = "Memberof"; Expression = { $Memberof[$_.Department] }}

    is nothin g.  It cannot be used and show a complete lack of knowledge of PowerShell and AD.

    use help for "Add-ADGroupMember" and "Select-Object" and you will begin to understand something about the difficulty of you question.


    \_(ツ)_/

    I indeed do have a lack of powershell knowledge. But it actually is something so you're abit overreacting here in my opinion cause I used this variable in another part of my script where it works and as I did not manage to find anything about assigning the member of with a variable I copied this to perhaps give you a better understanding of what is it I'm trying to achieve. To prove:

    $Company = @{ 
    'Test & Test Accountants B.V.' = 'OU=Accountancy,OU=Gebruikers,OU=TestNet,DC=Test-Test,DC=Corp';
    'Test & Test Audit B.V.' = 'OU=Accountancy,OU=Gebruikers,OU=TestNet,DC=Test-Test,DC=Corp';
    }
    
    $Department = @{ 
    'Automatisering' = 'OU=Automatisering,OU=Gebruikers,OU=TestNet,DC=Test-Test,DC=Corp';
    'Personeel & Organisatie' = 'OU=CKA - P&O,OU=Gebruikers,OU=TestNet,DC=Test-Test,DC=Corp';
    }
    
    Import-csv D:\Powershell\File01.csv | Select-Object *, @{ Name = "Path"; Expression = { $Company[$_.Company] ;$Department[$_.Afdeling] }} |
    Export-Csv D:\Powershell\File02.csv -NoTypeInformation

    Friday, February 26, 2016 2:18 PM
  • Hi,

    Here's a starter:

    Import-Csv .\userInput.csv | ForEach {
    
        If ($_.Department -eq 'Test & Test Accountants B.V.') {
    
            Add-ADGroupMember -Identity 'Accountants' -Members $_.Username
    
        }
    
        If ($_.Department -eq 'Smart Sales B.V.') {
    
            Add-ADGroupMember -Identity 'Sales' -Members $_.Username
            Add-ADGroupMember -Identity 'Marketing' -Members $_.Username
    
        }
    
        # If (....)
    
    }

    This assumes at least a header of Username and Department in your input CSV file.


    Thanks alot for this.

    Friday, February 26, 2016 2:18 PM
  • This:

    ADD-ADGroupMember | Select-Object *, @{ Name = "Memberof"; Expression = { $Memberof[$_.Department] }}

    is nothin g.  It cannot be used and show a complete lack of knowledge of PowerShell and AD.

    use help for "Add-ADGroupMember" and "Select-Object" and you will begin to understand something about the difficulty of you question.


    \_(ツ)_/

    I indeed do have a lack of powershell knowledge. But it actually is something so you're abit overreacting here in my opinion cause I used this variable in another part of my script where it works and as I did not manage to find anything about assigning the member of with a variable I copied this to perhaps give you a better understanding of what is it I'm trying to achieve. To prove:

    $Company = @{ 
    'Test & Test Accountants B.V.' = 'OU=Accountancy,OU=Gebruikers,OU=TestNet,DC=Test-Test,DC=Corp';
    'Test & Test Audit B.V.' = 'OU=Accountancy,OU=Gebruikers,OU=TestNet,DC=Test-Test,DC=Corp';
    }
    
    $Department = @{ 
    'Automatisering' = 'OU=Automatisering,OU=Gebruikers,OU=TestNet,DC=Test-Test,DC=Corp';
    'Personeel & Organisatie' = 'OU=CKA - P&O,OU=Gebruikers,OU=TestNet,DC=Test-Test,DC=Corp';
    }
    
    Import-csv D:\Powershell\File01.csv | Select-Object *, @{ Name = "Path"; Expression = { $Company[$_.Company] ;$Department[$_.Afdeling] }} |
    Export-Csv D:\Powershell\File02.csv -NoTypeInformation

    It cannot be used the way you are trying to use it.  That is why I suggested using HELP as it will tell you what the command does.  Guessing will only get you lost.  What you are asking to do can best be learned by starting with the help.

    The conversion of the CSV is also unnecessary as you can just do as Mike has posted or do it inline with the import into the Add-AdGroupMember block.

    I try to convince new users to do some basic research.  PowerShell has good help and it is a valuable first step as it will improve you overall knowledge more quickly and will improve your questions.


    \_(ツ)_/

    Friday, February 26, 2016 2:29 PM
  • Here is what I call dynamic translation:

    $groups=@{
    	 'Test & Test Accountants B.V.='Accountants'
    	'SmartSales B.V.'='Sales'
    } 
    
    Import-Csv .\userInput.csv | 
    	ForEach {
    		Add-AdGroupMember $groups[$_.Department] -Member $_.UserName
    	}
    
    


    \_(ツ)_/

    Friday, February 26, 2016 2:38 PM
  • Cheers, you're very welcome.

    Friday, February 26, 2016 3:06 PM