4

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.

  • 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 '19 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 '19 at 2:09
  • @cas yes the columns would be always consecutive. – nath Jul 29 '19 at 13:55
4
$ 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.

| improve this answer | |
13

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

| improve this answer | |
  • In contrast with Awk, it outputs the columns in the order of the input file, not in the order of the command. – PlasmaBinturong Jun 16 at 12:29
  • @PlasmaBinturong -- see edited answer ... cheers – drl Jun 19 at 19:53
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
| improve this answer | |
0

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
| improve this answer | |
  • 1
    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 '19 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 '19 at 0:51
  • @EdMorton I'm still hoping for an only awk sollution... – nath Jul 29 '19 at 13:48
  • @nath why? What's wrong with the cut solution that @drl posted? – Ed Morton Jul 29 '19 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 '19 at 13:54

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.