Problem:
Microsoft Excel is a very useful tool for data analysis. It supports import of data from various sources and formats into it for analysis. But, though it supports export of data in various common formats, it does not natively support export of data in a delimited text format with the delimiter other than Comma and Tab characters. This post explains how to get the excel data exported into a "|" (pipe) delimited text file.
Solution:
Excel does not directly support export or saving the data delimited with a pipe or such other characters. It supports comma delimited files (.csv) or tab delimited files though. The csv file generator however uses the List Separator as set in the windows Regional Settings as the delimiter. With this you can change this value in the regional settings and the csv file option will now produce a delimited file with the delimiter of your choice as set in the regional settings. For those not familiar with the regional settings, here is how to get this accomplished:
If you are using Windows 8 or 8.1, you will find the Regional Settings option under the "Clock, Language and Region" category.
Click on the Additional Settings button in the Formats tab of the Regional Settings dialogue box. In the Numbers tab of the resulting dialogue box, you will find the field List Separator (Highlighted in the image. By default, it displays ","(comma). Now set it to a character that you need the files to be delimited with. For instance, if you want export a pipe delimited text file from excel, enter the charcter "|" in this field and apply the change.
You are done. Close and re-open Excel with the data that you want to export. Now use the Save As option to save the sheet as a csv file (.csv). This will now produce a text file delimited with the pipe character. Unless you regularly use pipe as the delimiter, you may want to set it back to comma, so that it does not impact any other operations that dependent on the List Separator field.
