[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 the file in CSV format, and maybe your third-party developer needs the file in a pipe-delimited text file.

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 format in Microsoft Excel is a really easy process using the File > Save As menu.

Presto, changeo…you have the data in the correct format.

Pipe-delimited files are a whole different animal, though. There isn’t a “pipe delimited text file” file type, 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?
  • Convert my CSV or Excel worksheet to pipe delimited online.

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 file and you have to go through the process of swapping it back to comma.

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

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

  1. Download Notepad++ – If you are a Digital Marketer or Technology Professional you should have this tool. It’s free and is 1000000000 times better than Microsoft’s Notepad program.
  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 CSV or tab delimited fie to pipe delimited by opening it in Notepad++

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

  5. Click Replace All and every tab in your document will be replaced with a pipe.
  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 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!



Share This Post With A Pin!

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!

Comments And Reactions

  1. what is text delimited table as applied in QGIS

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

Speak Your Mind, Share A Comment, Ask A Question