Powershell with excel

This topic contains 3 replies, has 3 voices, and was last updated by Profile photo of Flynn Bundy Flynn Bundy 1 year, 3 months ago.

  • Author
  • #27218
    Profile photo of NetAdminTX

    I am not sure if powershell is the best fit for this problem, so if you have another suggestion those are welcome also.

    I have an excel spreadsheet, contained in this spreadsheet is a column that contains the username for each record, I need to read this username from say Column B of each row and write the corresponding title from active directory in to say column G of the same row

    I can get the active directory title info from get-aduser -property Title
    but I am not sure how to work with the excel spreadsheet (it is actually a CSV if that makes it easier)

  • #27238
    Profile photo of Flynn Bundy
    Flynn Bundy

    Hey mate,

    We can use some fancy selecting to get this job done. here's an example that I'll explain.

     $x = Import-Csv C:\Users.CSV | select Username,@{n='Guid';e={Get-ADUser $_.Username | select -ExpandProperty ObjectGUID}}  | Export-Csv C:\Duh.csv -NoTypeInformation 

    In this example I have a CSV with two columns. one called Username and one called GUID. As soon as you Import-CSV those Columns become Properties which are now available to you. ( I have this stored in X for convenience) After that I pipe the CSV while selecting Username and also making my own custom header.

    In the Custom header I'm calling the Get-ADUser and specifying the $_Username (property) as the username I am looking to match. From there I do an expand on the property I am looking to populate in my GUID column. Once my Custom header looks good I close it off and export-csv with notypeinformation.

    You can easily change this to contain a username column and a title colum and just go through the same steps (ie. replace GUID with Title). Think of the custom header as just a single property in your select statement. IE:.

     | Select Title,@{n='Custom';e={ Code here } },Guid,samaccountname 

    You can add other properties also if required. There is really endless possibilities when using a query like this because you can just run a separate command inside the expression tag and expand the result passing that back to the CSV.

    Let me know how you go.


  • #27260
    Profile photo of Rob Simmers
    Rob Simmers

    @Flynn: Your second example has a pipe ( | ) between Select and the properties. The first example is correct.

  • #27282
    Profile photo of Flynn Bundy
    Flynn Bundy

    good eye Rob! my typo!

You must be logged in to reply to this topic.