1

I have a csv file with seven numbers per line like this:

1083,20,28,42,23,10,43
1084,20,5,29,59,40,33
1085,39,50,21,12,40,55
1086,45,4,6,23,10,2
1087,36,46,28,32,3,20

I want to keep the first number in place (column 1) and sort columns 2 to 7, making the file like

1083,10,20,23,28,42,43
1084,5,20,29,33,40,59
1085,12,21,39,40,50,55
1086,2,4,6,10,45,23
1087,3,20,28,32,36,46

How can I do that with awk, sed or whatever?

Thanks

1
1

The problem you are facing is you have files with MAC line endings (\r) whereas the commands you are running are assuming Unix line endings (\n).

Use Perl:

$ perl -l015 -F, -0015 -pae '$_ = join ",", shift @F, sort { $a <=> $b } @F' input.csv

Options:

  • -l sets ORS to \r = octal 15.
  • -0 sets RS to \r = octal 15.
  • -p will loop thru your csv file on a per record basis. A record is split from the input file by the RS, which is \r as set by the -0 option.
  • -F will set the field separator to comma for each record as it is read.
  • -a will split each record as it is being read into fields and stored in array @F.
  • Note: the order of supplying the options is important.

So the output that you will get shall also have MAC line endings. That is why you are NOT seeing your expected output, rather all your output records appear squashed into a single line based on all the solutions given here.

1
  • Now we are talking. Your answer is perfect and is the only one that works for me and the only one that fits what I was saying. Cannot upvote you enough. THANKS! – Duck Feb 27 '19 at 12:00
2

With perl:

perl -F, -lape '$_ = join ",", shift @F, sort {$a <=> $b} @F' < input.csv

With GNU awk:

gawk -F, '
  {
    split($0, a)
    printf "%s", a[1]
    delete a[1]
    n = asort(a)
    for (i = 1; i <= n ; i++) printf "%s", FS a[i]
    print ""
  }' < input.csv

Or using the join.awk library (thanks @WeijunZhou)

gawk -i join -F, -v OFS=, '
  {
    split($0, a)
    first = a[1]
    delete a[1]
    n = asort(a)
    print first, join(a, 1, n, OFS)
  }'
7
  • thanks but that is destroying part of the file. Some lines end like this ,47,50,5519 – Duck Feb 26 '19 at 10:12
  • 1
    @SpaceDog, sounds like your file has CRLF MS-DOS line endings, convert it to Unix first (using dos2unix for instance) or insert a s/\r$//; at the start of the perl code above. – Stéphane Chazelas Feb 26 '19 at 10:15
  • I don't gave gawk. I am no macOS. Your perl command is not working even on the unix converted file. – Duck Feb 26 '19 at 10:27
  • In your gawk code, -F, is not needed. Assuming nonneg numbers, with recent gawk, gawk -F, '@include "join";{split($0,a,",");a[1]=0;asort(a);print $1","join(a,2,7,",")}' – Weijun Zhou Feb 26 '19 at 10:29
  • @WeijunZhou - unfortunately I do not have gawk on macOS. – Duck Feb 26 '19 at 10:32
-2

Tried with below method combination of awk and sed

j=awk '{print NR}' filename | sort -nr| sed -n '1p'

for ((i=1;i<=$j;i++)); do k=`awk -F "," -v  i="$i" 'NR==i {print $1}' i.txt` ;echo $k;awk -F "," -v i="$i" 'NR==i {$1=" ";print $0}' i.txt| sed -r "s/^\s+//g"| perl -pne "s/ /\n/g"| sort -n| perl -pne "s/\n/ /g";echo " "| sed "s/.*/&\n/g";done|sed '/^$/d'| sed "N;s/\n/ /g"| sed "s/ /,/g"| sed "s/\,*$//g"

output

1083,10,20,23,28,42,43
1084,5,20,29,33,40,59
1085,12,21,39,40,50,55
1086,2,4,6,10,23,45
1087,3,20,28,32,36,46
1
  • I do not even know how to use this... You have a variable declared on the first line (?) then a second line... ??? – Duck Feb 26 '19 at 11:06
-2

For simple csv without quoting, try this:

while IFS= read -r l; do
    col1=$(printf '%s' "$l" | cut -d, -f1)
    printf '%s,' "$col1"
    printf '%s' "$l" | cut -d, -f2- | grep -o "[0-9]*" | sort -n | paste -sd,
done < file
8

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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