PowerShell export-csv

So this is a great little cmdlet but watch out for the following:

  • By default it will add a header to your CSV file – you can remove this by adding the -NoTypeInformation switch.
  • Umaluts get mangled unless you use the -Encoding UTF8 switch to fix this
  • Consider using the pipe delimiter rather than the default comma
  • Increase the speed and reduce the file size by selecting what you want exported and not every attribute

that last tip applies to your objects too – they can start taking up more memory than you can afford and you don’t want your script suffering from dementia do you.  it’s always worth limiting the returned attributes to those you are interested in by using a SELECT in the pipeline.

Using the | pipe as a delimiter?

Sometimes its better not to use commas as a delimiter in your CSV file.  OK OK, I know CSV stands for comma separated values BUT remember that a lot of  attributes in AD can contain commas.  A DN has loads of course and how often has the service desk put a comma in a description or displayname  or worse in the CN of the user object!  That little trick means you have to be careful when handling DNs in any script.    And poorly written applications and scripts will fall over for sure!

“CN=Andrews, Lee” if used in naming a user object will sometimes, depending on how you are extracting the data show up as “CN=Andrews\, Lee” because the comma is one of those special characters.  How is AD supposed to know if the comma it just parsed is a comma between two parts of the OU path or just a plain old comma.  Personally I don’t see the problem because if it’s not followed by an OU= or DC= then it’s not difficult for me to figure out its a comma not the delimiter.  the developers obviously thought otherwise and if you add a comma in the name as per the example then it gets automatically escaped using the \.

There are lots of other characters you should try and avoid using as they are going to get you in the end, the ampersand, the back tick to name a few.  These characters are production script nightmares and mean your error checking better be good if you want the script to run unattended.

I could post all the notes I have on escaping characters in a distinguished name but I doubt I could do it better than Richard L. Mueller on his Hiltop lab web page

http://www.rlmueller.net/CharactersEscaped.htm  his web site has a lot of useful information.

So back to export-csv …… (finally I hear you say) You can change the delimiter used – and a favourite of mine is the pipe |. The switch to use for this is not surprisingly -Delimiter “|”

When exporting the data from a variable the file size and time taken to export can be unexpectedly large and time consuming!

It can be quite frustrating, sitting there waiting for this command to finish, so make sure you select the attributes you want rather than just exporting all of them!

$userobjects | Select samaccountname,wwwHomePage | Export-Csv c:\temp\test22.csv -NoTypeInformation -Encoding UTF8 -Delimiter “|”


Leave a Reply

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 )

Google+ photo

You are commenting using your Google+ 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 )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.