PowerShell Export-CSV turns lovely tables in to garbage


powershell logo 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

Advertisements

4 thoughts on “PowerShell Export-CSV turns lovely tables in to garbage

What do you think?

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s