Reorder columns in a CSV file by number

Question

i am trying to replace the second column with the second to last column and also remove the three last column. 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 perfectly 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 row is connected to the first item in the next row. so I am getting

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

Can anyone give me any advice on how to fix the problem so I can get the .csv file to have a new paragraph for each row like the desired output? 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