6

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.

3
  • 4
    Please tell me the middle column is enclosed in quote marks. If it is not, tell whoever created these files they did it wrong. Commented Jan 23, 2018 at 22:06
  • 1
    If the columns are properly quoted, then you can use a language (like perl or python or even awk) that has a CSV parsing library. See also stackoverflow.com/questions/4205431/…
    – cas
    Commented Jan 24, 2018 at 2:50
  • Yeah it wasn't quoted. The worst part is that column was not needed at all. Commented Jan 25, 2018 at 1:11

3 Answers 3

16
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)

2
  • you dont have to use a redirect
    – Zombo
    Commented Jan 23, 2018 at 23:52
  • 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.
    – Jeff Schaller
    Commented Jan 24, 2018 at 0:23
1

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
1

You can use this sed too

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

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .