How to use Unix Shell to show only the first n columns and last n columns?

Question

I have many csv files. The original design was supposed to have five columns.

I just found out that the middle column of the csv file has a string with arbitrary number of commas in it and it is not quoted properly. This leads to rows with arbitrary number of columns.

How do I get just the first two and last two columns of these csv files?

Since the number of commas can change from row to row I need a way to specify first two and last two columns.

Solution 1

awk -F, '{print $1, $2, $(NF-1), $NF}'  < input

---

More generally (per the Question's title), to print the first and last n columns of the input -- without checking to see whether that means printing some columns twice --

awk -v n=2 '{ 
  for(i=1; i <= n && i <= NF; i++)
      printf "%s%s", $i, OFS
    for(i=NF-n+1; i <= NF && i >= 1; i++)
      printf "%s%s", $i, OFS
    printf "%s", ORS
  }' < input

(using -F as needed for the delimiter)

Solution 2

perl:

echo a,b,X,X,X,X,c,d | perl -F, -slane 'print join ",", @F[0..$n-1, -$n..-1]' -- -n=2

a,b,c,d

Solution 3

You can use this sed too

sed -E 's/(([^,]*,){2}).*((,[^,]*){2})/\1\3/;s/,,/,/'

Comments

[user1730706]

you dont have to use a redirect

[Jeff Schaller]

That’s correct. It’s a habit I’m trying to form after seeing advice in that direction from other U&L members here. It means that the shell reports errors/failures instead of the utility — more consistency. It also prevents the utility from running if the IO redirection fails.