4

I need to sort a CSV file, but the the header row (1st row) keeps getting sorted. This is what I'm using:

cat data1.csv | sort -t"|" -k 1 -o data1.csv 

Here's a sample line:

Name|Email|Country|Company|Phone 
Brent Trujillo|tempus@mauris.org|Burkina Faso|Donec LLC|(612) 943-0167
1
  • 3
    CSV files can have newlines in the individual fields (which need to be quoted), e.g. if you have more details in your addresses or multiple telephone numbers. It is better to be defensive and use real CSV parsing as available in e.g. Perl and Python for these tasks. – Anthon Nov 30 '14 at 6:40
9

This should work and output to data2.csv:

head -n 1 data1.csv > data2.csv &&
tail -n +2 data1.csv | sort -t "|" -k 1 >> data2.csv
1
  • 1
    Thanks Christopher. It worked! I've been working on this for days now. – Frank Wilson Nov 29 '14 at 21:32
1

I think a big part of the problem here is

... -k 1 ...

sort's -key arguments imply at least a -k [num] start and ,[num] end reference for each. To say -k 1 is effectively no different than specifying no sort key at all because without an end reference sort will sort lines from the start of the line to its end.

If you wanted to sort lines on only the first |-delimited field you would need to do:

... -k1,1 ...

If you did that and if your first line began with a | then, with GNU sort you could just do...

sort -st\| -k1,1 <infile >outfile

...and the first line would remain as is - you'd get the behavior you desire.

I specify GNU sort above because POSIX specifies sort to evaluate all bytes on a line as significant to the comparison after it has exhausted all sort keys on a non-unique sort op (twice - adjacent equally-keyed lines are specified to at least drop modifiers after the first pass) and adjacent equally-keyed lines remain. This means that for...

printf '|%s\n' 9 1 | sort -nk1,1

...a POSIX sort prints...

|1
|9

...which might be counter-intuitive, as the first field - and the only one matched by any -k argument - is completely empty. By default GNU sort also prints the same , but when the -stable sort option is specified it prints...

|9
|1

...as it does not evaluate any line any further than allowed by the fields specified on the command-line in the same way it would for a -unique sort - though it doesn't remove dups for a -stable sort.

Without altering your file at all, if your header line is the first and if you are sorting with a GNU sort you can do:

{ printf \|; cat file; } | 
sort -st\| -k1,1 | 
sed 's/.//;q'

...which, as I think, will get you the results you want on stdout.

Or something like...

{ printf \|; cat file; } | 
nl -ba -s\| |
sort -t\| -k2,2 -k1n,1 |
cut -d\| -f2-

...for a sure thing with any implementation maybe

Not the answer you're looking for? Browse other questions tagged or ask your own question.