Following on from my promise to do better blogging in 2013, here’s something that frustrated me in PowerShell.
It stems from me being very much a beginner still in the PoSh ranks. Although, I’m using it more and more of late and starting to get the hang of it all
One issue I recently struggled with was when a user asked me if I could create a spreadsheet containing all the London staff phone numbers and email addresses. This sounded easy enough, I ran the following line of code and was happy with my results
1: get-aduser -SearchBase 'ou=StaffUsers,Dc=contoso,DC=biz' -filter {office -like "London*"} -Properties * | Sort-Object office, surname | FT name, emailaddress, title, office, officephone, mobilephone -AutoSize -GroupBy office
The PowerShell window showed a nicely displayed table with all of the correct columns, ordered and grouped how I wanted.
However, the user wanted it to be a script she could run to generate a CSV file so she could use the data more flexibly. No problem, I thought, I’ll just pipe an Export-CSV command on the end to save it to a CSV file. Unfortunately, this resulted in a CSV file with 200 lines of gobbledegook, e.g.
| ClassId2e4f51ef21dd47e99d3c952918aff9cd | pageHeaderEntry |
| pageFooterEntry | autosizeInfo |
| shapeInfo | groupingEntry |
| 033ecb2bc07a4d43b5ef94ed5a35d280 | |
| Microsoft.PowerShell.Commands.Internal.Format.AutosizeInfo | |
| Microsoft.PowerShell.Commands.Internal.Format.TableHeaderInfo |
After a lot of head scratching and searching I found the answer. You can’t use Format-Table when exporting to a CSV file. Rather than exporting the data as a CSV, PowerShell was trying to export the formatted table object as a CSV, which clearly broke what I was trying to do. To sort this out, you need to use Select-Object in place of Format-Table. Also, get rid of any –GroupBy or Autosize parameters as they don’t make sense in a CSV file.
Therefore, the modified command is now
1: get-aduser -SearchBase 'ou=StaffUsers,Dc=Contoso,DC=biz' -filter {office -like "London*"} -Properties * | Sort-Object surname | Select-Object name, emailaddress, title, office, officephone, mobilephone | Export-Csv -NoTypeInformation -path $env:userprofile\Desktop\London-Users.csv
One of the new things I learned was that you can’t use standard system environment variables like you could with batch scripts. E.G. you can’t use %USERPROFILE%\Desktop in a file path to go to the current logged in users desktop. Instead you need to use a standar powershell variable (denoted by the $ character) You can either define your own at the start of the script, e.g.
$users-profile-path = get-content env:userprofile
and then call it as
Export-Csv -NoTypeInformation -path $users-profile-path\Desktop\London-Users.csv
or just call it directly by using $env:userprofile in the command
Export-Csv -NoTypeInformation -path $env:userprofile\Desktop\London-Users.csv
My full script is below. I added comments and broke it up to make it easier for non-techies to understand.
1: # London Contact Info
2: # Created for PowerShell v3, make sure AD module is available
3: # Script by Thom McKiernan - Last updated 03/01/2013
4:
5: # Find the users in AD with an Office Code starting 'London'
6: get-aduser -SearchBase 'ou=Staff Users,Dc=linkedin,DC=biz' -filter {office -like "London*"} -Properties * |
7: # Sort by surname
8: Sort-Object surname |
9: # Select the required columns
10: Select-Object name, emailaddress, title, office, officephone, mobilephone |
11: # Export to the users desktop
12: Export-Csv -NoTypeInformation -path $env:userprofile\Desktop\London-Users.csv
A few notes; The user must have enabled scripts to be run, have access to the Active Directory module (part of RSAT) and, although it should work in older versions of PowerShell it was designed for v3.0
Let me know if this works for you, or any questions you may have, via the comments
Good article. This was a common occurrence during the 2012 Scripting Games as well. The same idea goes for any Format* cmdlet. Once you use one of those cmdlets, you are done with any other piping ( | Sort-Object, Export-CSV,etc…). I wrote an article last year that discussed this as well: http://learn-powershell.net/2012/04/18/scripting-games-2012-know-when-to-use-format-table/
LikeLike
It helped me.
LikeLike
Thank you very much. You saved me a lot of trouble.
LikeLike
Hi,
Im trying to output file properties liek filename, date created along with MD5 hash for the entire system32 folder.
using these commandlets in powershell
Get-FileHash C:\Windows\System32\drivers\ -Algorithm SHA1 |Select *,@{Label=’VersionInfo’;Expression={(Get-Item $_.Path).VersionInfo}} |Export-Csv c:\b.csv
I want the format in csv in seperate columns for each file property.
Anyhelp wud mean a lot!
LikeLike
Thanks! This saved me some frustration.
LikeLike