none
PS/Excel - If Column A, Change to Column B RRS feed

  • Question

  • Total newb here and as a Hardware tech for my company I've been tasked with making changes to AD regarding the Office field. 

    I have a spread sheet with the current office field listed under A and what it needs to be changed to listed under B

    My experience with PS v4 is single lines of code, mostly querying data regarding groups or OU's, however, I've ran single-field bulk ADupdates for SAMAccounts listed in a txt file. That's really the extent of it.

    I honestly don't know where to begin and all the searches I've done haven't produced anything I understand to be a solution. I appreciate any advice you can offer. 


    Thursday, March 2, 2017 5:19 PM

Answers

  • Hi Andy,

    first of all, save your excel file into a csv format (using "SaveAs" in Excel). Make sure you have column headers for it, makes life easier. Let's label the column A as "OldOffice" and column B as "NewOffice".

    Let's move to the PowerShell console next:

    Import-Csv office.csv | ForEach-Object{
    	$item = $_;
    	Get-ADUser -LDAPFilter "(&(physicalDeliveryOfficeName=$($_.OldOffice)))" | Set-ADUser -Office $item.NewOffice
    }

    And that should already do it all for you.
    Remember to do a test with a fictional office and a single test user first. Never run bulk edits in AD without testing first.

    Cheers,
    Fred


    There's no place like 127.0.0.1

    • Marked as answer by Damaged Andy Tuesday, March 7, 2017 9:10 PM
    Thursday, March 2, 2017 5:37 PM

All replies

  • Save your spreadsheet as a CSV file. PowerShell can process CSV data easily (Import-Csv), and you can use Set-ADUser (or whatever) to make the needed changes.

    Start with any of the numerous PowerShell tutorials.


    -- Bill Stewart [Bill_Stewart]

    Thursday, March 2, 2017 5:37 PM
    Moderator
  • Hi Andy,

    first of all, save your excel file into a csv format (using "SaveAs" in Excel). Make sure you have column headers for it, makes life easier. Let's label the column A as "OldOffice" and column B as "NewOffice".

    Let's move to the PowerShell console next:

    Import-Csv office.csv | ForEach-Object{
    	$item = $_;
    	Get-ADUser -LDAPFilter "(&(physicalDeliveryOfficeName=$($_.OldOffice)))" | Set-ADUser -Office $item.NewOffice
    }

    And that should already do it all for you.
    Remember to do a test with a fictional office and a single test user first. Never run bulk edits in AD without testing first.

    Cheers,
    Fred


    There's no place like 127.0.0.1

    • Marked as answer by Damaged Andy Tuesday, March 7, 2017 9:10 PM
    Thursday, March 2, 2017 5:37 PM
  • Thank you. This worked flawlessly. I appreciate your help and your time. Have a great day!

    Thursday, March 2, 2017 7:28 PM