Author Posts

July 6, 2015 at 1:37 pm

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)

July 6, 2015 at 7:51 pm

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.

Cheers

July 7, 2015 at 5:39 am

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

July 7, 2015 at 1:46 pm

good eye Rob! my typo!