Awk – Sort with Tab as Field Separator

awksort

I want to sort a tab-delimited file by a specific field while preserving the header. I'm using awk as described here sort and uniq in awk, but I can't figure out who to tell sort that the field separator is a tab.

Toy data:

$ echo -e "head_1\thead_2\thead_3" > file.tsv
$ echo -e "aaa zzz\tc\t300" >> file.tsv
$ echo -e "bbb yyy ooo\ta\t100" >> file.tsv
$ echo -e "ccc xxx nnn\tb\t200" >> file.tsv
$ column -ts $'\t' file.tsv
head_1       head_2  head_3
aaa zzz      c       300
bbb yyy ooo  a       100
ccc xxx nnn  b       200

$ awk -F'\t' 'NR==1; NR>1 { print | "sort -k2" }' file.tsv | column -ts $'\t' 
head_1       head_2  head_3
ccc xxx nnn  b       200           ## note these data are sorted 
bbb yyy ooo  a       100           ## based on the xxx/yyy/zzz 
aaa zzz      c       300           ## not the a/b/c

When I try to explicitly tell sort the the field separator is a tab, I get this error, which I believe is related to quoting issues:

$ awk -F'\t' 'NR==1; NR>1 { print | "sort -k2 -t $'\t'" }' file.tsv | column -ts $'\t'
sort: option requires an argument -- 't'
Try 'sort --help' for more information.
head_1  head_2  head_3

How do I specify the column separator for sort inside `awk? Thanks

SE's web interface is doing a better job of syntax highlighting than Notepad++; here are a couple of things I've tried:

$ awk -F'\t' 'NR==1; NR>1 { print | "sort -k2 -t $'$'\t''" }' file.tsv | column -ts $'\t'
head_1       head_2  head_3
aaa zzz      c       300
bbb yyy ooo  a       100
ccc xxx nnn  b       200


$ awk -F'\t' 'NR==1; NR>1 { print | "sort -k2 -t $'\t'" }' file.tsv | column -ts $'\t'
sort: option requires an argument -- 't'
Try 'sort --help' for more information.
head_1  head_2  head_3

$ awk -F'\t' 'NR==1; NR>1 { print | "sort -k2 -t "'$'\t''"" }' file.tsv | column -ts $'\t'
sort: option requires an argument -- 't'
Try 'sort --help' for more information.
head_1  head_2  head_3

$ awk -F'\t' 'NR==1; NR>1 { print | "sort -k2 -t "'$'\t'' }' file.tsv | column -ts $'\t'
sort: option requires an argument -- 't'
Try 'sort --help' for more information.
head_1  head_2  head_3

Best Answer

chose one of these options:

... | "sort -k2 -t \\\t "
... | "sort -k2 -t \"\t\" "
... | "sort -k2 -t'\''\t'\'' "
... | "sort -k2 -t \047\011\047" ## preferred 

\011 is the Octet ASCII code for Tab character/ \047 for single quote '

awk -v q="'" ... { print | "sort -k2 -t " q "\t" q }'
awk -v tb="'\t'" ... { print | "sort -k2 -t " tb }'
awk -v tb=$'\t' ... { print | "sort -k2 -t \"" tb "\"" }'
awk -v tb=$'\t' -v q="'" ... { print | "sort -k2 -t " q tb q }'

and many more …; read Shell Quoting Issues in awk; see also Escape Sequences in awk

Related Question