CODEWITHSUNDEEP
bashcsvsedawk
user1899415
user1899415

Reputation: 3125

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

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?

Upvotes: 14

Views: 42352

Answers (11)

Mirage
Mirage

Reputation: 31548

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

Upvotes: 2

user4126057
user4126057

Reputation: 

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/'

Upvotes: 0

hennr
hennr

Reputation: 2762

Try this:

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

Upvotes: 9

welldan97
welldan97

Reputation: 3076

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(',')"`

Upvotes: 1

potong
potong

Reputation: 58371

This might work for you (GNU sed):

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

Upvotes: 2

Vijay
Vijay

Reputation: 67211

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

check here

Upvotes: 2

glenn jackman
glenn jackman

Reputation: 246754

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

Upvotes: 3

koola
koola

Reputation: 1734

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

Upvotes: 6

steveha
steveha

Reputation: 76695

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)
}

Upvotes: 2

that other guy
that other guy

Reputation: 123410

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

Upvotes: 19

Rubens
Rubens

Reputation: 14768

Use cut and tail:

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

Upvotes: 6

Related Questions