How can I replace tab-delimited data with line breaks?

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

Drewdavid

Posted 2012-11-23T23:49:17.747

Reputation: 119

Perhaps you can answer your own question, so that we all can benefit? – Jacob Jan Tuinstra – 2012-12-10T06:26:16.563

Answers

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.

Lri

Posted 2012-11-23T23:49:17.747

Reputation: 34 501

Hi, need some clarification on this one. What would be the command to achieve this? Thanks for your help. – Drewdavid – 2012-11-28T19:29:05.087

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 – 2012-11-28T19:31:09.723

You can try using a regular expression:

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

This will replace tabs \t with newlines \n.

root

Posted 2012-11-23T23:49:17.747

Reputation: 144

Hi and thank you. Is this something I am to do in Terminal? Thanks :) – Drewdavid – 2012-11-25T19:40:18.090

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 – 2012-11-26T17:59:15.767

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.

Update

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

enter image description here

nerdwaller

Posted 2012-11-23T23:49:17.747

Reputation: 13 366

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 – 2012-11-25T19:43:09.520

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 – 2012-11-25T19:45:03.887

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 – 2012-11-26T17:56:38.790

@Drewdavid Attached a screenshot to show it working. Perhaps the line codings aren't unix based, what was the file made using? – nerdwaller – 2012-11-26T18:05:19.647

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 – 2012-11-28T19:29:38.100

Mac is a distant relative of UNIX, so it should work. What program was this file created in? – nerdwaller – 2012-11-28T19:36:17.300