How to sort a tab delimited file with sort command?

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?

Lars Haugseth · Accepted Answer · 2013-10-16T11:15:23.837

369

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.

edited Oct 16 '13 at 11:15

answered Jun 24 '09 at 10:10

Lars Haugseth

14,721
2
45
49

2

[Use](https://stackoverflow.com/a/1250279/4970442) `'"'"'` to use it inside an alias. – Pablo Bianchi Sep 27 '17 at 05:49
can you show how to pass this delimeter to sort within an awk command? as in `awk '{print $0 | "sort -nr" > "outfile" }' datafile`, except with an escaped tab delimeter sent to the sort command. – Merlin Dec 01 '17 at 00:11
1

Use `-g` rather than `-n` if you want numeric sort. `-n` is broken. – Luke Hutchison Jul 21 '20 at 00:10
1

Also works in `zsh 5.7.1 (x86_64-apple-darwin19.0)` – Zach Young Dec 01 '20 at 22:09
Sorry, I have the same issue on Windows, is there a solution for Windows too? Other than copying the tab character and pasting it – Joe Jobs Jul 10 '21 at 00:02
@JoeJobs depending on terminal you can try `ctrl+v` and then pressing `TAB` to insert a literal tab character – CervEd Nov 27 '21 at 10:37
I am wondering why this correct answer is not placed at the top? – Katelynn ruan Aug 31 '22 at 16:37

score 12 · Answer 2 · answered Jun 24 '09 at 10:07

12

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

answered Jun 24 '09 at 10:07

laalto

150,114
66
286
303

9

This will only work if the number of space characters between the tab-separated fields is the same for all lines of input. – Lars Haugseth Jun 24 '09 at 10:23
3

Bad hack that doesn't work generally for valid tsv files that contain spaces in fields. Don't use for production! – Cornelius Roemer Nov 17 '21 at 17:31

score 7 · Answer 3 · answered Dec 16 '14 at 00:14

7

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.

answered Dec 16 '14 at 00:14

Brian Carlsen

79
1
1

This is the best (most portable) answer. emacs also lets you do that in 'quoted insert' mode: `C-q ` for instance. I think it's `^V` in nano as well. – Wyatt Ward Apr 05 '20 at 08:02
cntrl-q in QTerminal talking to the shell. – DragonLord Nov 23 '21 at 22:47

score 4 · Answer 4 · answered Mar 24 '13 at 21:21

4

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

answered Mar 24 '13 at 21:21

Lloyd

41
1

3

Use `` in order to insert tab in case the tab key is used for auto-completion in your shell. – Júda Ronén Dec 26 '17 at 10:57
5

ANSI quoting `$'\t'` works in ksh, zsh, and bash. Bourne shell doesn't support it. See this post: https://unix.stackexchange.com/a/371873/201820 – codeforester Mar 20 '18 at 05:00

score 3 · Answer 5 · edited Jun 11 '14 at 23:07

3

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

edited Jun 11 '14 at 23:07

Andreas Ley

9,109
1
47
57

answered Jun 11 '14 at 22:40

Lawrence Noronha

49
3

1

Yeah the trick here is putting it in a .bat file, otherwise it won't work – Carlos Rendon Nov 05 '14 at 18:48

score 3 · Answer 6 · edited Nov 12 '14 at 06:00

3

I was having this problem with sort in cygwin in a bash shell when using 'general-numeric-sort'. If I specified -t$'\t' -kFg, where F is the field number, it didn't work, but when I specified both -t$'\t' and -kF,Fg (e.g -k7,7g for the 7th field) it did work. -kF,Fg without the -t$'\t' did not work.

edited Nov 12 '14 at 06:00

lennon310

12,503
11
43
61

answered Nov 12 '14 at 05:40

Danny

41
3

score 2 · Answer 7 · edited Aug 18 '21 at 08:50

2

sort -t "$(printf '\t')" works for me

edited Aug 18 '21 at 08:50

Roman

2,530
2
27
50

answered Aug 10 '21 at 14:57

Jason

21
1

Smart, albeit also shell-specific. – 0xC0000022L Aug 31 '23 at 16:48

score 1 · Answer 8 · answered Jun 24 '09 at 10:08

In general keeping data like this is not a great thing to do if you can avoid it, because people are always confusing tabs and spaces.

Solving your problem is very straightforward in a scripting language like Perl, Python or Ruby. Here's some example code:

#!/usr/bin/perl -w

use strict;

my $sort_field = 2;
my $split_regex = qr{\s+};

my @data;
push @data, "7 8\t 9";
push @data, "4 5\t 6";
push @data, "1 2\t 3";

my @sorted_data = 
    map  { $_->[1] }
    sort { $a->[0] <=> $b->[0] }
    map  { [ ( split $split_regex, $_ )[$sort_field], $_ ] }
    @data;

print "unsorted\n";
print join "\n", @data, "\n";
print "sorted by $sort_field, lines split by $split_regex\n";
print join "\n", @sorted_data, "\n";

score 1 · Answer 9 · answered May 31 '20 at 07:16

1

Lars Haugseth answer only worked from the command line for me where it gives this error if executed from a shell script:

sort: multi-character tab ‘$\t’

The solution if it's coded in a shell script if anyone's looking is

sort -t'    '

the tab character is in between the quote.

answered May 31 '20 at 07:16

mightyandweakcoder

773
8
25

You are using the wrong value. It should be `$'\t'` not `'$\t'`. – Neil Mayhew Jun 23 '22 at 05:18

score 0 · Answer 10 · answered Jun 24 '09 at 09:49

0

pipe it through something like awk '{ print print $1"\t"$2"\t"$3"\t"$4"\t"$5 }'. This will change the spaces to tabs.

answered Jun 24 '09 at 09:49

Michiel Buddingh

5,783
1
21
32

@MB: I need to keep the space intact. – neversaint Jun 24 '09 at 10:00
1

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. – Michiel Buddingh Jun 24 '09 at 10:08
3

This won't work if there is a mixture of tabs and spaces that you want to preserve. – James Thompson Jun 24 '09 at 10:11

The Unfun Cat · Answer 11 · 2018-09-18T16:18:55.463

0

If you want to make it easier for yourself by only having tabs, replace the spaces with tabs:

tr " " "\t" < <file> | sort <options>

edited Sep 18 '18 at 16:18

answered Apr 17 '18 at 09:09

The Unfun Cat

29,987
31
114
156

My tr does not read files, only streams XD. `usage: tr [-Ccsu] string1 string2` – The Unfun Cat Sep 18 '18 at 06:27
1

`tr string1 string2 – Randal Schwartz Sep 18 '18 at 15:48
There are a lot of cases in where spaces cannot be transformed into tabs, e.g. logs (dates, user agents), people or country names (one, two or more words)... Just tell sort what is the split char. – xbello May 12 '23 at 12:07

How to sort a tab delimited file with sort command?

11 Answers11

Linked

Related