How to sort data based on date field by excluding header

Question

I have a scenario where I have below data in file which need to be sorted based on date column , first line is the headers it should not be sorted

NAME|AGE|COURSE|DATES
v1|31|MC|12 JUL 2019
v2|33|MB|4  JUL 2019
v3|12|GG|13 JUL 2019
v4|21|JJ|7  JUL 2019

My code :

sort -n -k k4 /d/file.txt

This above code does not sort my data

Expected Output :

NAME|AGE|COURSE|DATES
v4|21|JJ|7  JUL 2019
v2|33|MB|4  JUL 2019
v1|31|MC|12 JUL 2019
v3|12|GG|13 JUL 2019

Does this answer your question? [Is there a way to ignore header lines in a UNIX sort?](https://stackoverflow.com/questions/14562423/is-there-a-way-to-ignore-header-lines-in-a-unix-sort) — Dominique, Aug 30 '21 at 12:11

score 1 · Accepted Answer · answered Aug 30 '21 at 19:06

The way do to this is with Command Grouping where you can extract the header from an input stream, print it, and consume the remaining data:

{
    IFS= read -r header
    echo "$header"
    sort ...
} < file.txt

However, sorting dates with that format is tricky. Here's how you have to do it so the output is sorted chronologically. This assumes GNU sort:

$ cat file.txt          # I added a couple of extra records
NAME|AGE|COURSE|DATES
v1|31|MC|12 JUL 2019
v2|33|MB|4  JUL 2019
v3|12|GG|13 JUL 2019
v4|21|JJ|7  JUL 2019
11|22|33|1  JUL 2020
aa|bb|cc|10 AUG 2019

$ {
    IFS= read -r header
    echo "$header"
    sort -t'|' -n -s -k4 | sort -M -s -k 2,2 | sort -n -s -k 3,3
} < file.txt
NAME|AGE|COURSE|DATES
v2|33|MB|4  JUL 2019
v4|21|JJ|7  JUL 2019
v1|31|MC|12 JUL 2019
v3|12|GG|13 JUL 2019
aa|bb|cc|10 AUG 2019
11|22|33|1  JUL 2020

That uses the GNU sort "stable" option so you sort first by day, then by month, then by year.

TIL that GNU sort has `-M`. Thanks! – Ed Morton Sep 12 '21 at 04:18 — Ed Morton, Sep 12 '21 at 04:18

score 1 · Answer 2 · answered Sep 12 '21 at 04:27

Borrowing @glennjackman's sample input, this will work using any versions of the mandatory Unix tools awk, sort, and cut:

$ awk '
    BEGIN { FS="|"; OFS="\t" }
    {
        split($NF,d," ")
        mthNr = (index("JANFEBMARAPRMAYJUNJULAUGSEPOCTNOVDEC",d[2])+2)/3
        print (NR>1), d[3], mthNr, d[1], NR, $0
    }
' file.txt |
sort -k1,1n -k2,2n -k3,3n -k4,4n -k5,5n |
cut -f6-
NAME|AGE|COURSE|DATES
v2|33|MB|4  JUL 2019
v4|21|JJ|7  JUL 2019
v1|31|MC|12 JUL 2019
v3|12|GG|13 JUL 2019
aa|bb|cc|10 AUG 2019
11|22|33|1  JUL 2020

How to sort data based on date field by excluding header

2 Answers2