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. 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
    Jan 24, 2018 at 2:50
  • Yeah it wasn't quoted. The worst part is that column was not needed at all. 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
    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
    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/,,/,/'

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.