Opening a CSV file in Excel when you have used the pipe delimiter

Related to my last post I discovered that Excel does not always open a CSV file and correctly determine the column breaks when you used the pipe delimiter.    I have no idea why that’s the case but you can work around this.

If you find that when you open a csv file that has used the pipe delimiter all of the data is in column A, which is not what you wanted you can work around this using one of these two techniques:

  1. Open Excel and then use file open to load the file – this will force the text import wizard to popup and then you can specify the delimiter to use.
  2. Convert the file once it’s opened – see below

I suspect you are like me and forget the formatting used in the file and just double click the CSV file and get presented with a single column of data ARRRGHHHH!  No problem, Excel can do the conversion for you without having to close the file and start again.

  1. Select the column with all the data by clicking in the column header
  2. select the data tab on the tool bar ribbon
  3. Click Text to Columns

This will fire up the Convert to Text Columns Wizard and you can then specify the delimiter – job done 🙂

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  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 “|”