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 🙂

Advertisements

One thought on “Opening a CSV file in Excel when you have used the pipe 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.