How to open a CSV file that uses Pipe Delimiters in Microsoft Excel?

Question: How to open a CSV file that uses Pipe Delimiters in Microsoft Excel?

CSV stands for comma separated file, sometimes we don't want to use comma's in a CSV file and decide to rather use a Pipe Delimiter | especially in cases where one of the columns contains a paragraph with comma's in. Open Office provides us with a option to choose the Delimiter when opening CSV files, but MS Excel does not. But luckily theres a way around this.

NOTE: This how-to has been based on Windows Vista, but will work on other Windows versions as well.

Answer:

• Microsoft Excel uses the current list separator in the locale settings of Windows
• Go to Control Panel
  
  
  
  
  
• Open Regional and Language Options
  
  
  
  
  
• Click on Customize this format...
  
  
  
  
  
• The Customize Regional Options window will appear
  
  
  
  
  
• Change the List separator from , (COMMA DELIMITER) to | (PIPE DELIMITER)
  
  
  
  
  
• Open the CSV file with Pipe Delimiters again in MS Excel and it will open as expected.

• Add New Search
• Comments

• |2011-06-24 04:40:55 Krishna
  This works but it only works in case of pipe.
  if we have to open 2 different files lets say one with comma(,) as seperator on
  one file and pipe(|) as seperator on other file. Is there any way to open both
  the files simultaneously.
  • 0
  • 0

  ReplyQuote

Write comment

Your Contact Details:

Name:

Email: do not notifynotify

Comment:

Title:

UBBCode:         -color-aquablackbluefuchsiagraygreenlimemaroonnavyolivepurpleredsilvertealwhiteyellow -size-tinysmallmediumlargehuge

Message:

Security

Please input the anti-spam code that you can read in the image.