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.
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.