Selecting rows in a CSV file based on column value

Question

I have a CSV file with 4 columns: Itemname,Value,Description and component which is quite huge.

I have to generate a template from the above CSV file that displays only the rows of the specified component(say component='abc' which is the search criterion)

Unless you edit your question to make it relevant to *nix, this will probably moved to [so] and/or closed. — Anthon, Jun 24 '13 at 04:30
@Anthon how is this not relevant to *nix? As Ignacio's answer shows, awk is perfect for this. — terdon, Jun 24 '13 at 14:23
@tendon I don't see why awk is perfect for this. How does awk e.g. determine the separating character used in the CSV?. This doesn't have to be the comma, something that e.g. Ignacio assumes). This now seems more a generic programming question that belongs on [so] (if not already answered there), which is why I warned the OP. — Anthon, Jun 24 '13 at 15:18
@tendon see the top answer...https://unix.stackexchange.com/a/80472/311307 — Nic Scozzaro, Oct 14 '20 at 13:22

score 10 · Answer 1 · answered Jun 24 '13 at 04:10

10

Assuming there are no embedded commas, awk is perfect for this.

awk -F , '$4 == "abc" { print }' input.csv

answered Jun 24 '13 at 04:10

Ignacio Vazquez-Abrams

45,725

1

'print' is the default action, so you may just omit it. – gelraen Jun 24 '13 at 11:03
1

the separating character for CSV is not always a comma, e.g. not with the defaults for some versions of Excel. – Anthon Jun 24 '13 at 16:36
Replacing the comma with something else is very, very easy. – Ignacio Vazquez-Abrams Jun 24 '13 at 20:22

score 7 · Answer 2 · edited Aug 23 '18 at 22:09

I used another tool in the csvkit: csvgrep.

$ csvgrep -c 4 -m "abc" data.csv > test.csv

This is the resulting contents of the file test.csv:

Itemname,Value,Description,Component
33,34,35,abc

-c is to designate the column to look in. you can also use the header, just make sure you spell it exactly the same, capitals matter:

$ csvgrep -c Component -m "abc" data.csv > test.csv

Itemname,Value,Description,Component
33,34,35,abc

and -m is match pattern, I'm pretty sure there is a way to use regular expressions if you want to get more in-depth in your matching. then it gets put in a new file named test.csv.

score 0 · Answer 3 · answered Feb 07 '17 at 14:18

With the following data.csv:

Itemname,Value,Description,Component
1,2,3,4
5,6,7,8
9,10,11,12
13,14,15,16
17,18,19,20
21,22,23,24
25,26,27,28
29,30,31,32
33,34,35,abc
37,38,39,40
41,42,43,44
45,46,47,48
49,50,51,52
53,54,55,56
57,58,59,60
61,62,63,64
65,66,67,68
69,70,71,72
73,74,75,76
77,78,79,80
81,82,83,84
85,86,87,88
89,90,91,92
93,94,95,96
97,98,99,100

Using csvkit:

$ csvsql --query "SELECT * FROM data WHERE Component = 'abc'" data.csv
Itemname,Value,Description,Component
33,34,35,abc

Selecting rows in a CSV file based on column value

3 Answers3

Linked