Replace columns in CSV file by number

Question

I am trying to replace the second column with the second by the last column, and also delete the last three columns. For example, I have this sample.csv

1,2,3,4,5,6 a,b,c,d,e,f g,h,i,j,k,l 

I want to output:

 1,5,3 a,e,c g,k,i 

I am using this command:

 awk 'BEGIN{FS=OFS=","} {$2=$(NF-1); NF=NF-3}'1 sample.csv 

which works fine when I view the csv file in excel. however, when I look at the .csv file in notepad, I notice that the last item on one line is associated with the first item on the next line. so i get

 1,5,3a,e,cg,k,i 

Can someone give me any tips on how to fix the problem so that I can get the .csv file to have a new paragraph for each line, such as the desired result? Thanks.

Answer 1

Adding a carriage return ( \r ) to the end of each line should help:

 awk 'BEGIN{FS=OFS=","} {$2=$(NF-1); NF=NF-3;sub(/$/,"\r");}'1 sample.csv 

Answer 2

Code for GNU sed :

 sed -r 's/(\w,)\w,(\w,)\w,(\w,)\w/\1\3\2/' file 

---

  $ cat file
 1,2,3,4,5,6
 a, b, c, d, e, f
 g, h, i, j, k, l

 $ sed -r 's / (\ w,) \ w, (\ w), \ w, (\ w,) \ w / \ 1 \ 3 \ 2 /' file
 1,5,3
 a, e, c
 g, k, i