12

if I have a csv file in the following format:

column1,column2,column3,column4,column5,column6,column7,column8

and I want awk to only print columns 2 till 7 I would use:

awk -F',' '{print $2 "," $3 "," $4 "," $5 "," $6 "," $7}' file.csv

and get:

column2,column3,column4,column5,column6,column7

is there a way to concatenate the columns 2-7 to simplify the command. As I'm thinking of a file with quite a bit more columns, my awk command would get horribly long.

5
  • 2
    are you always going to want a consecutive set of columns (e.g. 2-7, or 5-15 or whatever)? if so, you can use a loop to print the columns. If not, there's no avoiding listing the individual columns you want (although you can mix in some loops where needed). Also, how many columns?
    – cas
    Jul 29, 2019 at 2:07
  • 1
    Finally, you might want to consider a language like perl or python with a good CSV parser....especially if you have the column names in the first line (several of perl's CSV parsing modules can use these to construct a hash with the column names as keys. i'm pretty sure python's CSV parsers can do similar). perl also has excellent array & hash splicing operators.
    – cas
    Jul 29, 2019 at 2:09
  • @cas yes the columns would be always consecutive.
    – nath
    Jul 29, 2019 at 13:55
  • 1
  • Also on StackOverflow
    – cachius
    Mar 14, 2023 at 19:38

5 Answers 5

21

The utility cut has a compact notation:

cut -d, -f2-7 <input-file>

producing:

column2,column3,column4,column5,column6,column7

Answering the comment by @PlasmaBinturong: my intent was address the issue of a short calling sequence: "... my awk command would get horribly long ...". However, one can also find codes that arrange the fields as one might desire. As much as I like awk, perl, python, I have often found it useful to build a specific utility to extend the capabilities of standard *nix. So here is an excerpt from a test script, s2, showing utilities recut and arrange, both allow re-arrangement and duplication, with arrange also allowing decreasing field ranges:

FILE=${1-data1}

# Utility functions: print-as-echo, print-line-with-visual-space.
pe() { for _i;do printf "%s" "$_i";done; printf "\n"; }
pl() { pe;pe "-----" ;pe "$*"; }

pl " Input data file $FILE:"
head $FILE

pl " Results, cut:"
cut -d, -f2-7 $FILE

pl " Results, recut (modified as my-recut):"
my-recut -d "," 7,6,2-5 < $FILE

pl " Results, arrange:"
arrange -s "," -f 5,3-1,7,5,3-4,5 $FILE

producing results from these versions:

OS, ker|rel, machine: Linux, 3.16.0-10-amd64, x86_64
Distribution        : Debian 8.11 (jessie) 
bash GNU bash 4.3.30
cut (GNU coreutils) 8.23
recut - ( local: RepRev 1.1, ~/bin/recut, 2010-06-10 )
arrange (local) 1.15

-----
 Input data file data1:
column1,column2,column3,column4,column5,column6,column7,column8

-----
 Results, cut:
column2,column3,column4,column5,column6,column7

-----
 Results, recut (modified as my-recut):
column7,column6,column2,column3,column4,column5

-----
 Results, arrange:
column5,column3,column2,column1,column7,column5,column3,column4,column5

The my-recut is a slight modification the textutils code recut, and arrange is our version of an extended cut. More information:

recut   Process fields like cut, allow repetitions and re-ordering. (what)
Path    : ~/bin/recut
Version : - ( local: RepRev 1.1, ~/bin/recut, 2010-06-10 )
Length  : 56 lines
Type    : Perl script, ASCII text executable
Shebang : #!/usr/bin/perl
Home    : http://www1.cuni.cz/~obo/textutils/ (doc)
Modules : (for perl codes)
 Getopt::Long   2.42

arrange Arrange fields, like cut, but in user-specified order. (what)
Path    : ~/bin/arrange
Version : 1.15
Length  : 355 lines
Type    : Perl script, ASCII text executable
Shebang : #!/usr/bin/perl
Modules : (for perl codes)
 warnings       1.23
 strict 1.08
 Carp   1.3301
 Getopt::Euclid 0.4.5

Best wishes ... cheers, drl

2
  • In contrast with Awk, it outputs the columns in the order of the input file, not in the order of the command. Jun 16, 2020 at 12:29
  • @PlasmaBinturong -- see edited answer ... cheers
    – drl
    Jun 19, 2020 at 19:53
10
$ awk -v b=2 -v e=7 'BEGIN{FS=OFS=","} {for (i=b;i<=e;i++) printf "%s%s", $i, (i<e ? OFS : ORS)}' file
column2,column3,column4,column5,column6,column7

b=beginning field number, e=end field number. If you need to handle CSVs with quoted fields, embedded commas, newlines, etc. then see https://stackoverflow.com/q/45420535/1745001.

0
3
sed -e '
  s/,/\n/7        ;# tag the end of col7
  s/^/,/          ;# add a comma
  s/,/\n/2        ;# tag beginning of col2
  s/.*\n\(.*\)\n.*/\1/ ;# perform surgery 
' file.csv

Results:

column2,column3,column4,column5,column6,column7
1

Tested with below command and it worked fine

awk -F "," 'OFS=","{$1="";$NF="";print $0}' o| sed "s/^,//g"|sed "s/,$//g"

output

column2,column3,column4,column5,column6,column7
6
  • 2
    thanks for the answer, works fine without the "o" before first sed pipe :-) one could concatenate the two sed commands into one: sed "s/^,//g; s/,$//g"
    – nath
    Jul 28, 2019 at 18:45
  • 3
    Why are you testing the result of executing OFS="," to decide whether or not to print the line? There are other issues too (e.g. you do NOT need pipes to sed commands when you're using awk!) but that OFS test part makes absolutely no sense...
    – Ed Morton
    Jul 29, 2019 at 0:51
  • @EdMorton I'm still hoping for an only awk sollution...
    – nath
    Jul 29, 2019 at 13:48
  • @nath why? What's wrong with the cut solution that @drl posted?
    – Ed Morton
    Jul 29, 2019 at 13:50
  • 1
    @EdMorton no you are right, it works fine. I thought it could not be too complicated with awk and was curious rather for the matter of interest then for a technical reason :-)
    – nath
    Jul 29, 2019 at 13:54
0

This works for me:

awk '{ for (i=2; i<=7;i++){ printf $i; if (i != 7){ printf "," }} print "" }'

Doesn't make a lot of sense for small amount of columns (as far as complexity of the command is concerned). But if your input has really a lot of columns this approach is practical.

You must log in to answer this question.

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