5

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)

4
  • Unless you edit your question to make it relevant to *nix, this will probably moved to Stack Overflow and/or closed.
    – Anthon
    Commented Jun 24, 2013 at 4:30
  • @Anthon how is this not relevant to *nix? As Ignacio's answer shows, awk is perfect for this.
    – terdon
    Commented Jun 24, 2013 at 14:23
  • 2
    @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 Stack Overflow (if not already answered there), which is why I warned the OP.
    – Anthon
    Commented Jun 24, 2013 at 15:18
  • @tendon see the top answer...unix.stackexchange.com/a/80472/311307 Commented Oct 14, 2020 at 13:22

4 Answers 4

10

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

awk -F , '$4 == "abc" { print }' input.csv
3
  • 1
    'print' is the default action, so you may just omit it.
    – gelraen
    Commented Jun 24, 2013 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
    Commented Jun 24, 2013 at 16:36
  • Replacing the comma with something else is very, very easy. Commented Jun 24, 2013 at 20:22
7

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.

0

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
0

Slightly different from Ignacio Vazquez-Abrams’s answer (which didn't quite work for me because of some newline characters):

awk -F "\"*,\"*" '$4 == "abc"' input.csv

This answer takes into account the default action of awk to print, as pointed out by @gelraen (therefore, there's no need to specify if you don't want to).

2
  • (1) Like Ignacio Vazquez-Abrams’s answer, this assumes that there are no embedded commas.  That’s a fairly benign assumption, in general.  But you have modified that answer to handle field values in quotes, which (in CSV format) would allow embedded commas.  So you really need to reiterate that assumption explicitly. (2) What do you mean by “because of some newline characters”?  Can you show an example of input data for which your answer works and Ignacio’s answer doesn’t? … (Cont’d) Commented Apr 29 at 21:50
  • (Cont’d) …  (3) Please explain how your answer differs from Ignacio’s answer; i.e., what -F "\"*,\"*" means.         Please do not respond in comments; edit your answer to make it clearer and more complete. Commented Apr 29 at 21:50

You must log in to answer this question.

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