I have the following input csv file:
"aaa","1","xxx" "ccc, Inc.","6100","yyy" "bbb","609","zzz"
I wish to sort by the second column as numbers, I tried
sort --field-separator=',' --key=2n
the problem is that since all values are quoted, they don't get sorted correctly by -n (numeric) option. is there a solution?
For a quoted csv use a language that has a proper csv parser. Here is an example using perl.
perl -MText::ParseWords -lne '
chomp;
push @line, [ parse_line(",", 0, $_) ];
}{
@line = sort { $a->[1] <=> $b->[1] } @line;
for (@line) {
local $" = qw(",");
print qq("@$_");
}
' file
Output:
"aaa","1","xxx"
"bbb","609","zzz"
"ccc, Inc.","6100","yyy"
Explanation:
chomp function. END block, sort the array of array on second column and assign it to the original array of array. "," and we print it with preceding and trailing " to create the lines in original format. Dropping your example into a file called sort2.txt I found the following to work well.
sort -t'"' -k4n sort2.txt
Using sort with the following commands (thank you for the refinements Jonathan)
Hope this helps!
less behaves like cat when its output goes to a pipe, and sort is perfectly capable of reading the file (so sort -t '"' -k 4n sort2.txt would be better; it would sort numerically, too)."Joe ""The Man"" Bloggs" (valid CSV) would throw your field count off horribly. You can level that complaint at my answer, too, but it would require something like "Joe ""The Man"",""The Guy"" Bloggs" to confuse it, which is even more esoteric (and one of my 'reasonable assumptions' is that such a string would not appear — there'd be a space after the embedded comma).
There isn't going to be a really simple solution. If you make some reasonable assumptions, then you could consider:
sed 's/","/^A/g' input.csv |
sort -t'^A' -k 2n |
sed 's/^A/","/g'
This replaces the "," sequence with Control-A (shown as ^A in the code), then uses that as the field delimiter in sort (the numeric sort on column 2), and then replace the Control-A characters with "," again.
If you use bash, you can use the ANSI C quoting mechanism $'\1' to embed the control characters visibly into the script; you just have to finish the single-quoted string before the escape, and restart it afterwards:
sed 's/","/'$'\1''/g' input.csv |
sort -t$'\1' -k 2n |
sed 's/'$'\1''/","/g'
Or play with double quotes instead of single quotes, but that gets messy because of the double quotes that you are replacing. But you can simply type the characters verbatim and editors like vim will be happy to show them to you.
^A, or should it use $'\1' as well?$'\1' uses printable characters only (backslash and digit 1) whereas the ^A in the first command sequence has to be typed as control-A. They map to the same character. You can use either; you could use ^A in one and $'\1' in the other, but that would probably confuse people unnecessarily. (And yes, my code was self-inconsistent; apologies, and thanks again, Dale Anderson!)Sometimes the values in the CSV file are optionally quoted, only when necessary. In this case, using " as a separator is not reliable.
Example:
"Forest fruits",198
Apples,456
bananas,67
Using awk, sort and cut, you can sort the original file, here by the first column :
awk -F',' '{
a = $1; # or the column index you want
gsub(/(^"|"$)/, "", a);
print a","$0
}' file.csv | sort -k1 | cut -d',' -f1 --complement
This will bring the column you want to sort on in front without quotes, then sort it the way you want, and remove this column at the end.
