[SOLVED] Converting A Tab or Comma Delimited CSV File To Pipe Delimited Format

While not specifically focused on social media, this post is definitely one for the Technology section of this site.

If you work with Excel (and most Marketers and Technologists do), you know the pain of having to deal with a variety of file MS Excel file formats. Your client wants the file in .XLS format, MailChimp wants a comma delimited file (aka CSV format), your boss wants the file in a tab delimited format, and maybe your third-party developer needs the file in a pipe-delimited text format.

Same data…different formats. Pain in the you-know-what.

Converting between XLS, XLSX, and CSV formats is pretty easy. Just use Excel’s built-in “Save As” function and change the format using the file type dropdown.

Saving a file to the CSV format in Microsoft Excel is a really easy processSaving a file to the CSV, or comma delimited Excel format is a really easy process using the File > Save As menu.

Presto, changeo…you have the data in the correct format. Using the Save As feature, you can choose from:

  • A comma separated CSV file (both in UTF-8 or standard format)
  • A tab delimited text file
  • A space delimited text file

You can even choose to create a comma separated value file on Mac format or for MS-DOS. 

Pipe-delimited files are a whole different animal, though. There isn’t a “pipe delimited text file” file type to choose from and I had a situation that required the delivery of a pipe delimited file, so I started digging into how to make the change.

After running a bunch of searches like the ones below, I came to the same conclusion…There is no built-in feature to save an Excel document as a pipe-delimited text file.

  • How can I save an Excel file as a pipe-delimited file?
  • Is there a way to convert a file to pipe-delimited without having to change the delimiter?
  • Does MS Excel allow you to save as pipe-delimited?
  • Is there an online pipe-delimiter conversion tool?
  • Can I convert my CSV or Excel file to pipe delimited online?

None of these searches provide an easy solution to the problem. 

Instead, you have to go through the cludgy process of updating the standard delimiter from comma to pipe within MS Office, and this impacts EVERY file you save as a CSV.

This process sucks and, in my opinion, is NOT a solution.

The minute you change your standard delimiter to pipe, another client will call asking for a CSV delimiter and you have to go through the process of swapping it back to comma delimiter. Definitely not idea.

So, to solve the issue, I came up with a process that is free (yes FREE) and simple to convert any CSV file or tab delimited file to a pipe delimited file format.

For those of you that have to deal with this pain, here are the steps:

Step 1Download Notepad++ – If you are a Digital Marketer or Technology Professional you should have this tool. It’s free and is 1,000,000,000 times better than Microsoft’s Notepad program.

Step 2 – In Notepad++ Open Your Comma Separated or Tab Delimited File – Once you have Notepad++ installed and opened, just click File –> Open and navigate to your CSV or txt file and open it in Notepad++.

Begin the process of converting your CSV or tab delimited fie to pipe delimited by opening it in Notepad++Begin the process of converting your comma separated or tab delimited fie to pipe delimited by opening it in Notepad++

Step 3 – Choose Search from the menu bar – What we’re going to do is replace every tab or comma with a pipe. To do that, choose Search –> Replace from the menu bar to open the Replace dialog box.

Step 4 – Replace tabs with pipes – Just like using MS Word or Excel, you can replace the tabs with pipes. The key, here, is to type “\t” (without the quotes) in the “Find What” field. “\t” is the placeholder symbol for a tab.Then just type a pipe in the “Replace with” field.Also, be sure to click the radio button next to the “Extended” option. This will allow Notepad++ to recognize that /t is the tab, not the character string of a right slash and a lower-case T.

The next step for converting your CSV or tab delimited fie to pipe delimited is to replace the tabs with a pipe.The next step for converting your CSV or tab delimited fie to pipe delimited is to replace the tabs with a pipe.

NOTE: If you are using a comma-delimited file, obviously just use the steps above and type a comma instead of \t.

Step 5 – Click Replace All and every tab in your document will be replaced with a pipe.

Step 6 – Save your file as a new text file by choosing File –> Save As from the menu and adding “-pipe-delimited” to the end of the filename.

So there you have it.

Instructions on how to easily convert your comma delimited or tab delimited file into a pipe delimited format. While the first time might take you a couple of minutes, each time after that goes quicker.

Have a tip or trick for making this process even easier? Be sure to share it in a comment and I’ll check it out. Trusty me, readers of this blog will appreciate any tips you can share to make the conversion process go easier!

Cheers!

–Sean


Share This Post With A Pin!

Tips for converting a comma or tab delimited file to a pipe delimited format

If you need to convert a comma separated file or tab delimited file to a pipe delimited format, here are the steps to get the job done
Steps to convert a comma or tab delimited excel spreadsheet or csv file to a tab delimited format

Content So Good You Can Almost Taste It!

Subscribe to my email list and get updates in your inbox

Join my mailing list and get all of my social media tips, tricks, and comics in your inbox. Spam-free, guaranteed!

Below Content Subscribe

Comments And Reactions

  1. Great write-up! This works very well for most situations.

    Just be aware that it will not work accurately if one of the fields contains quoted data that contains the delimiter. For example, say you have a field such as “Company Name”, and one of the companies is Acme, Inc. Proper CSV formatting would format the field as “Acme, Inc.” with surrounding quotes and the comma intact. Unfortunately, a simple search and replace won’t handle this. Sadly, there is no easy solution to this that I can find.

  2. what is text delimited table as applied in QGIS

  3. Andrew Chance says

    There is an “easy” solution…. open the CSV in Excel, but then save it as a tab delimited. Then you can match \t in Notepad++ (or any 1/2 decent text editor!) and replace with a |

  4. Been trying to save tab pipe file for weeks. Very helpful. Thank you Sir.

  5. Bill Gale says

    In linux one line solution: sed “s/,/|/g” file_comma.csv > file_pipe.csv

  6. Thank you socmedsean for sharing such a useful information about the above mentioned query. It worked for me

  7. The issue with this solution is that CSV files can have quoted fields with embedded commas, e.g., “Smith, John”. If you simply search and replace the commas with a pipe, you will be adding extra fields every time you encounter this.

    To avoid this, you need to use a regular expression search/replace in notepad++, and write the appropriate match expression to only convert commas that are not delimited by quotes.

  8. Christopher Taylor says

    How would you ensure that you have the correct number of blank fields after the last field with data? For example, our file requires 21 blank fields to still be separated by pipes after the last populated field in row 1.


Speak Your Mind, Share A Comment, Ask A Question

*