3

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.

  • 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. – Monty Harder Jan 23 '18 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 '18 at 2:50
  • Yeah it wasn't quoted. The worst part is that column was not needed at all. – PoorLifeChoicesMadeMeWhoIAm Jan 25 '18 at 1:11
13
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)

  • you dont have to use a redirect – Steven Penny Jan 23 '18 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 '18 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.