using bash (sed/awk) to extract rows AND columns in CSV files?

Question

Is bash capable of handling extracting rows and columns from csv files? Hoping I don't have to resort to python..

My 5-column csv file looks like:

Rank,Name,School,Major,Year
1,John,Harvard,Computer Science,3
2,Bill,Yale,Political Science,4
3,Mark,Stanford,Biology,1
4,Jane,Princeton,Electrical Engineering,3
5,Alex,MIT,Management Economics,2

I only want to extract the 3rd, 4th, and 5th column contents, ignoring the first row, so output looks like:

Harvard,Computer Science,3
Yale,Political Science,4
Stanford,Biology,1
Princeton,Electrical Engineering,3
MIT,Management Economics,2

So far I can only get awk to print out either each row, or each column of my CSV file, but not specific cols/rows like this case! Can bash do this?

it's odd that you're struggling to get awk to do this since printing fields (columns) and rows (records) is the most basic thing awk that is designed to do. Makes me think there must be more to this than you've described so far.... — Ed Morton
– Ed Morton, Commented Jan 24, 2013 at 6:28

that other guy · Accepted Answer · 2013-01-24 01:55:11Z

19

awk -F, 'NR > 1 { print $3 "," $4 "," $5 }'

NR is the current line number, while $3, $4 and $5 are the fields separated by the string given to -F

answered Jan 24, 2013 at 1:55

that other guy

125k12 gold badges187 silver badges214 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

jordanm Over a year ago

You can set OFS=',' so that you don't have to concatenate commas in the print.

hennr · Accepted Answer · 2015-05-08 17:26:14Z

9

Try this:

tail -n+2 file.csv | cut --delimiter=, -f3-5

edited May 8, 2015 at 17:26

answered Oct 2, 2013 at 9:54

hennr

2,7623 gold badges26 silver badges26 bronze badges

1 Comment

Dwight Spencer Over a year ago

Most simple and elegant solution yet.

koola · Accepted Answer · 2013-01-24 03:42:29Z

6

Bash solutions;

Using IFS

#!/bin/bash
while IFS=',' read -r rank name school major year; do
    echo -e "Rank\t: $rank\nName\t: $name\nSchool\t: $school\nMajor\t: $major\nYear\t: $year\n"
done < file.csv
IFS=$' \t\n'

Using String Manipulation and Arrays

#!/bin/bash
declare -a arr
while read -r line; do
    arr=(${line//,/ })
    printf "Rank\t: %s\nName\t: %s\nSchool\t: %s\nMajor\t: %s\nYear\t: %s\n" ${arr[@]}
done < file.csv

answered Jan 24, 2013 at 3:42

koola

1,7641 gold badge13 silver badges15 bronze badges

2 Comments

icedwater Over a year ago

Fairly unwieldy, but I like the use of arrays which I will probably refer to again at some point. Not to mention it's a bash-only solution.

gondo Over a year ago

this fails to ignore commas in quotes. example csv line: "some, text",1,2 will be parsed as: some, text, 1, 2 instead of some text, 1, 2

Rubens · Accepted Answer · 2013-01-24 01:53:09Z

6

Use cut and tail:

tail -n +2 file.txt | cut -d ',' -f 3-

answered Jan 24, 2013 at 1:53

Rubens

14.8k11 gold badges68 silver badges94 bronze badges

1 Comment

Rubens Over a year ago

The OP wanted to skip the first line, that's why we used tail.

glenn jackman · Accepted Answer · 2013-01-24 04:34:42Z

3

sed 1d file.csv | while IFS=, read first second rest; do echo "$rest"; done

answered Jan 24, 2013 at 4:34

glenn jackman

249k42 gold badges233 silver badges362 bronze badges

Comments

steveha · Accepted Answer · 2013-01-24 01:57:21Z

2

Here you go, a simple AWK program.

#!/usr/bin/awk -f

BEGIN {
    # set field separator to comma to split CSV fields
    FS = ","
}

# NR > 1 skips the first line
NR > 1 {
    # print only the desired fields
    printf("%s,%s,%s\n", $3, $4, $5)
}

answered Jan 24, 2013 at 1:57

steveha

77.1k21 gold badges94 silver badges119 bronze badges

1 Comment

glenn jackman Over a year ago

If you set OFS=",", you can simply write print $3, $4, $5

Vijay · Accepted Answer · 2013-01-24 07:27:22Z

2

perl -F, -lane 'if($.!=1){print join ",",@F[2,3,4];}' your_file

check here

answered Jan 24, 2013 at 7:27

Vijay

67.7k94 gold badges238 silver badges327 bronze badges

Comments

potong · Accepted Answer · 2013-01-24 07:42:18Z

2

This might work for you (GNU sed):

sed -r '1d;s/([^,]*,){2}//' file

answered Jan 24, 2013 at 7:42

potong

59.3k6 gold badges55 silver badges92 bronze badges

Comments

whoan · Accepted Answer · 2015-09-21 15:10:24Z

2

try this

awk -F, 'NR > 1 { OFS=",";print $3, $4, $5 }' temp.txt

or this

sed -re '1d;s/^[0-9],\w+,//g' temp.txt

edited Sep 21, 2015 at 15:10

whoan

8,6094 gold badges43 silver badges50 bronze badges

answered Jan 24, 2013 at 12:01

Mirage

31.7k64 gold badges173 silver badges266 bronze badges

2 Comments

Jon Egerton Over a year ago

Can you provide some explanation of what your fixes are doing.

Mirage Over a year ago

@JonEgerton , in the awk i added the OFS and in sed i made more clearer so that the new users can see what i am matching . in previous answers regex are shhort but hard to comprehend for new user of regex. mine may not be perfect but at least are visible what they are doing. and they work

welldan97 · Accepted Answer · 2013-04-11 14:02:46Z

1

I have created package for this kind of tasks - gumba If you feel comfortable with coffeescript you can give it a try

cat file.csv | tail -n +2 | \
gumba "words(',').take((words)-> words.last(3)).join(',')"`

answered Apr 11, 2013 at 14:02

welldan97

3,0962 gold badges26 silver badges28 bronze badges

Comments

whoan · Accepted Answer · 2015-09-21 15:09:38Z

0

grep '^,' outlook.contacts.csv | sed 's/^,\([^,]*\),[^,]*,\([^,]*\),.*/\1 \2/'

Get all lines that starts with a , then using sed to replace blank fields with first and second name.

Be careful for some reason once you paste it changes the line to this so maybe you better to carefully do it manually.

grep '^,' outlook.contacts.csv | sed 's/^,([^,]),[^,],([^,]),./\1 \2/'

edited Sep 21, 2015 at 15:09

whoan

8,6094 gold badges43 silver badges50 bronze badges

answered Oct 9, 2014 at 15:32

user4126057

Collectives™ on Stack Overflow

using bash (sed/awk) to extract rows AND columns in CSV files?

11 Answers 11

1 Comment

1 Comment

2 Comments

1 Comment

Comments

1 Comment

Comments

Comments

2 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

11 Answers 11

1 Comment

1 Comment

2 Comments

1 Comment

Comments

1 Comment

Comments

Comments

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related