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

And it doesn’t end with different delimiters. Maybe your business colleague sent you a simple text file and now you have to figure out how to convert Notepad to Excel. Or maybe your boss just asked you for an Excel pipe delimited file instead of a pipe delimited text file.

I know, I know. It’s enough to make your head spin. Same data…different formats. Pain in the you-know-what.

But don’t worry, help is here. The key is to know how to change delimiter in Excel or change the CSV delimiter and then save the file to the requested output format.

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

Pro-Tip – If you don’t need to do this a lot and just need to change the delimiter for a file one time, you can use the tool located at Online CSV Tools to change the CSV delimiter online in a flash. It’s a pretty handy text to CSV converter tool that allows you to upload your source document, choose the new delimiter and then download the updated document. It even allows you to convert CSV to TSV (tab separated values) and CSV to a pipe delimited text file.

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 1 – Download 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 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.

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

Update 1 – Is it possible to convert Notepad to Excel?

This was a question I got via my contact form and the answer is yes…as long as you structure the file correctly. It is entirely possible to created a data file in Notepad and convert it to Excel or open it in Excel.

Here are the things you need to know, though.

1)  Create a blank file and save it with the .csv file format. To do this, just click File > Save As and choose “All Files” as the Save as type value. Then type the name of your file with the .csv extension.

2) Next, add your data in the proper format. In order to create a valid data structure, all your content has to be separated by commas (remember, CSV stands for comma separate values).  So to create a CSV document that has three columns with header in the columns you would add data that would look like this:

Color, Height, Length, Width, ProductNumber
Red,12,24,36,224455
Green,2,16,36,224433
Blue,9,12,24,224466

This would create four rows in your spreadsheet. The first would be your column titles and the following three would be the values.

Pro Tip: Note that the end of the line does NOT have a comma. This signals to Excel that this is the end of the data for that row.

3) With your data added, just click save to update the file.

4) Next, Open Microsoft Excel and choose File > Open. Browse to your CSV file and open it.

That’s all there is to it. One additional note. If you use commas in your data, you will need to use quotes around the value to preserve the comma and ensure that Excel doesn’t treat the comma like a delimiter.

As an example, if your column name was “FullName” and your data contained values like “Johnson, Fred” and you want to keep those values together, then you just add quotes around them. So your data might look like:

FirstName,LastName,FullName
Sally,Johnson,”Johnson, Sally”
Joseph,Smith,”Smith, Joseph”

I hope that helps! Let me know if there are additional questions.

–Sean

Share This Post With A Pin!