Swap two columns in a CSV using SED

Question

I have a CSV file that contains 10 different fields (, is the deliminator). Example data:

student-id,last,first,hwk1,hwk2,hwk3,exam1,hwk4,hwk5,exam2
pts-avail,,,100,150,100,200,150,100,300
991-78-7872,Thompson,Ken,95,143,79,185,135,95,259

I need to swap field2 and field3 using sed but having a difficult time understanding how to write the regular expression.

I have tried along with other variations:

sed 's/\(.*[,]\)\(.*[,]\)\(.*[,]\)/\1\3\2/g' test

In my test file:

abc,def,ghi,jkl
1234,5678,abcd,efgh

It works fine… I have been looking at this for a while and can't figure it out. Anyone able to provide some direction?

Solution 1

Try:

sed 's/^\([^,]*,\)\([^,]*,\)\([^,]*\)/\1\3\2/'

Broken down:

'^'     start at the beginning of the line
\(  \)  a grouping
[^,]    any character except ','
*       zero or more times
,       the character ','

The \([^,]*,\) is repeated three times. The rest of the line is unchanged and unmatched.

With awk:

awk 'BEGIN {FS=OFS=","}{t=$2;$2=$3;$3=t;print}'

Solution 2

Non-sed solution using q:

$ q -d, -H -O  'select [student-id],first,last,hwk1,hwk2,hwk3,exam1,hwk4,hwk5,exam2 from sample.csv' 
student-id,first,last,hwk1,hwk2,hwk3,exam1,hwk4,hwk5,exam2
pts-avail,,,100,150,100,200,150,100,300
991-78-7872,Ken,Thompson,95,143,79,185,135,95,259

Comments

[Admin]

Why not awk? awk -F, -v OFS=, '{tmp=$2;$2=$3;$3=tmp;print}'

[Simon Gates]

Please bear in mind that these won't work if the CSV file contains fields with commas. The easiest way to cover that is to use a parser that's fully aware of CSV.