CSV files are helpful for a lot of different reasons, but mainly due to their compatibility with a lot of different types of programs. Unfortunately all CSV files are not created or formatted in the same way, so you can run into situations where a CSV file is not compatible with what you are attempting to do. One way this can occur is if you have a CSV comma delimited file, but you need a pipe, or |, delimited file. You may have tried to convert your file in Excel 2010 to accommodate for this difference but, unfortunately, you cannot accomplish your goal using Excel. You will need to use the Notepad program on your Windows 7 computer to replace each instance of a comma with a pipe.
Replacing Commas with |s in Notepad
The word “delimited” usually follows the word that describes how the individual cells, or fields, of the CSV file are separated. One issue that many people who are new to CSV files may encounter is the limitations that are placed on their files by spreadsheet applications like Excel. While Excel can display most CSV files in their familiar interface, it does not give you a lot of control over the formatting of your files. Luckily a CSV file is technically a text file, which can be opened in a simple text editor like Notepad. Opening your CSV comma delimited file in Notepad will allow you to see what the information in the file actually looks like, which can make converting a CSV comma delimited file to a | delimited file much simpler.
***Note that the technique described below is going to replace ALL of the commas in your document. This includes any commas that are contained in the individual fields of your CSV file. If you have commas in your fields, you are going to need to go back and manually replace them after performing the instructions in this tutorial.***
Step 1: Browse to your CSV comma delimited file.
Step 2: Right-click the file, click Open with, then click Notepad.
Note that, in my sample CSV comma delimited file below, the commas in the document signify the separator between the individual fields in the file.
Step 3: Press Ctrl + H on your keyboard to open the Replace window in Notepad. You can also open this window by clicking Edit at the top of the window, then clicking Replace.
Step 4: Type a “,” into the Find what field, type a “|” into the Replace with field, then click the Replace All button. The “|” key on your keyboard is located above the “Enter” key, if you are having trouble finding it.
Step 5: Close the Replace window, then save the edited file. Be sure to append the .csv file extension to the end of the file name when saving it, as Notepad may try to save the file as a .txt file. You may want to save the file with a new name in case you need to keep the original, comma-delimited file in its’ original state.
Matthew Burleigh has been writing tech tutorials since 2008. His writing has appeared on dozens of different websites and been read over 50 million times.
After receiving his Bachelor’s and Master’s degrees in Computer Science he spent several years working in IT management for small businesses. However, he now works full time writing content online and creating websites.
His main writing topics include iPhones, Microsoft Office, Google Apps, Android, and Photoshop, but he has also written about many other tech topics as well.
Disclaimer: Most of the pages on the internet include affiliate links, including some on this site.