Converting a csv file to a tsv file and vice versa

I used to use Excel to convert a tsv file to a csv file. However, there is a very quick way to convert csv and tsv files to each other by using a sed one-liner. sed is a handy little tool that is available on both Linux and Windows (on Windows you need to install cygwin). It is used for quick find and replace in files (however it can do a lot more and I will cover some of its other uses later).

Now let’d download a csv file and convert it to tsv quickly. Let’s first use wget to download this file into our local directory

Let’s see how our file looks like by using the head command in the terminal

sed can plow through a file and change any instance of a particular string to another string for example if we want to convert all the instances of “day” to “night” we can use the following

Now we can use this pattern to replace every instance of commas ‘,’ to a tab ‘\t’

Note that you need single qoutes around ‘s/,/\t/’ otherwise it will not work. To convert a tsv to a csv you can simply reverse the order of your comma and \t and use the following

An alternative method would be to pipe the file to a tr command and replace the commas with tabs using tr

Now you can convert csv files to tsv files by a sed one-liner. However, these one liners are not guaranteed to convert any arbitrary csv file. For those you might end up using the csv parsers inside Excel or use the csv module for Python or use R.

Other useful references:

  • This stack overflow question contains more information and more generalizable techniques to convert csv and tsv files to each other.
  • This post uses python to do the same type of conversion

2 thoughts on “Converting a csv file to a tsv file and vice versa

  1. There’s actually a command called “tr” that was designed just for converting from one character to another.

    That said, that isn’t all the work needed to convert between a tsv & and a csv, as you have to worry about escape characters and such. If only the world had learned about ASCII’s separator characters or the wisdom of length delimited fields…

    • That is an excellent point. I added some a little note to the post to clarify that these one liners can never parse and arbitrary csv file. For those I have found that a little R script is the most reliable way. Are there other ways to do that?

Leave a Reply to Mark Faridani Cancel reply

Your email address will not be published. Required fields are marked *