Sorting a tab delimited file

Question

I have a data with the following format:

foo<tab>1.00<space>1.33<space>2.00<tab>3

Now I tried to sort the file based on the last field decreasingly. I tried the following commands but it wasn't sorted as we expected.

$ sort -k3nr file.txt  # apparently this sort by space as delimiter

$ sort -t"\t" -k3nr file.txt
  sort: multi-character tab `\\t'

$ sort -t "`/bin/echo '\t'`" -k3,3nr file.txt
  sort: multi-character tab `\\t'

What's the right way to do it?

Here is the sample data.

Solution 1

Using bash, this will do the trick:

$ sort -t$'\t' -k3 -nr file.txt

Notice the dollar sign in front of the single-quoted string. You can read about it in the ANSI-C Quoting sections of the bash man page.

Solution 2

By default the field delimiter is non-blank to blank transition so tab should work just fine.

However, the columns are indexed base 1 and base 0 so you probably want

sort -k4nr file.txt

to sort file.txt by column 4 numerically in reverse order. (Though the data in the question has even 5 fields so the last field would be index 5.)

Solution 3

You need to put an actual tab character after the -t\ and to do that in a shell you hit ctrl-v and then the tab character. Most shells I've used support this mode of literal tab entry.

Beware, though, because copying and pasting from another place generally does not preserve tabs.

Solution 4

The $ solution didn't work for me. However, By actually putting the tab character itself in the command did: sort -t'' -k2

Solution 5

I wanted a solution for Gnu sort on Windows, but none of the above solutions worked for me on the command line.

Using Lloyd's clue, the following batch file (.bat) worked for me.

Type the tab character within the double quotes.

C:\>cat foo.bat

sort -k3 -t"    " tabfile.txt

View more solutions

Comments

[Danf]

@MB: I need to keep the space intact.

[Michiel Buddingh]

There's undoubtably a cleaner way to do it, but nothing prevents you from piping it through awk, change the spaces to tabs, sorting the data, and then piping it through awk again, changing the tabs back into spaces.

[James Thompson]

This won't work if there is a mixture of tabs and spaces that you want to preserve.

[Lars Haugseth]

This will only work if the number of space characters between the tab-separated fields is the same for all lines of input.

[Pablo Bianchi]

Use '"'"' to use it inside an alias.

[Júda Ronén]

Use <C-v><Tab> in order to insert tab in case the tab key is used for auto-completion in your shell.

[codeforester]

ANSI quoting $'\t' works in ksh, zsh, and bash. Bourne shell doesn't support it. See this post: unix.stackexchange.com/a/371873/201820

[The Unfun Cat]

My tr does not read files, only streams XD. usage: tr [-Ccsu] string1 string2

[Randal Schwartz]

tr string1 string2 <some-file. Everything can read a file as long as it can read stdin.

[Wyatt Ward]

This is the best (most portable) answer. emacs also lets you do that in 'quoted insert' mode: C-q <tab> for instance. I think it's ^V in nano as well.

[Luke Hutchison]

Use -g rather than -n if you want numeric sort. -n is broken.

[Cornelius Roemer]

Bad hack that doesn't work generally for valid tsv files that contain spaces in fields. Don't use for production!

[DragonLord]

cntrl-q in QTerminal talking to the shell.