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
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.
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
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.
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