Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I have a lot of tab-separated data, and this means when I import that I'm getting a lot of columns (too many for Google Drive and OpenOffice).

I think what I need to do is find and replace all tabs with line breaks (could be wrong).

*EDIT - I ended up figuring this out by using a different method. Thanks everyone for your help. :)

share|improve this question
Perhaps you can answer your own question, so that we all can benefit? – Jacob Jan Tuinstra Dec 10 '12 at 6:26

You can try using a regular expression:

sed 's/\t/\n/' input.txt > output.txt

This will replace tabs \t with newlines \n.

share|improve this answer
Hi and thank you. Is this something I am to do in Terminal? Thanks :) – Drewdavid Nov 25 '12 at 19:40
This didn't work as expected. As below, except that only the first http was changed to hnnp, all the rest of the file seems unchanged. Thanks for your help. – Drewdavid Nov 26 '12 at 17:59

To expand on root's response, you actually may need to add the /g at the end to do all the happenings.

You could also write it to the same file with an auto-backup flag.

sed -i.orig 's/\t/\n/g' file.txt

The ending /g will cause it to hit each happenstance instead of the first per line (if you need that).

The -i.orig writes the change to the same file after making a backup of the original (in this case file.txt.orig). That is just a matter of preference for me rather than having to then rename the original and the new one to take its place.


I attached a screenshot to show it working for me... You're sure the file is tab delineated?

enter image description here

share|improve this answer
Hi, thanks for your addition/recommendation. As above, is this something I am to perform in Terminal? Thanks will let you know how it goes. :) – Drewdavid Nov 25 '12 at 19:43
Yes, this would be done in the Terminal. And the .orig would be located in the same directory as whatever input file you are working with. – nerdwaller Nov 25 '12 at 19:45
Hi, this didn't exactly work as planned. It replaced all of the "t" characters in the text file with "n"s, so my URLs are "hnnp" for example. I copied and pasted the code, only changing the filename. – Drewdavid Nov 26 '12 at 17:56
@Drewdavid Attached a screenshot to show it working. Perhaps the line codings aren't unix based, what was the file made using? – nerdwaller Nov 26 '12 at 18:05
Thanks, I didn't clearly mention that I'm using Terminal on Mac OS X. The answer above is attempting to help but I'm not quite there yet. – Drewdavid Nov 28 '12 at 19:29

On OS X you have to use $'' to insert \t or \n and escape \n in the replace pattern.

echo $'1\t2\n3\t4' | sed $'s/\t/\\\n/g'

But don't linefeeds already separate lines in TSV files? You could also use TextEdit to replace the tabs with some other characters like semicolons.

share|improve this answer
Hi, need some clarification on this one. What would be the command to achieve this? Thanks for your help. – Drewdavid Nov 28 '12 at 19:29
And yes, but spreadsheets are interpreting the tabs as columns, and there are way too many columns to import and so they are getting cut off. – Drewdavid Nov 28 '12 at 19:31

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .