Re: How to filter a .csv file based on the integer value in one specific field (per record)



On 1/14/2008 3:25 PM, Brian Greaney wrote:
On Mon, 14 Jan 2008 14:59:17 -0600, Ed Morton wrote:


On 1/14/2008 2:40 PM, Brian Greaney wrote:

Hi, hope someone can help me (again!)
I have a large .csv file full of text & numbers.
I would like to 'filter' this file based on several 'keys', 2 text strings
(use grep I think) and the integer value of a specific field being greater
than a certain value (e.g. 100000).
I can see a way of using grep to filter on the two strings, but can't see
an elegant way of getting the numerical test on a specific field, bearing
in mind numbers occur in other fields.
The text fields I filter on are of the form ABC12 the integer from 2 to 6
digits
Hope this is clear (and not too dumb/newbie/rtfm a question) :)

There are many different ways a ".csv" file could be structured as there's no
specific standard for one (though there are some attempts at such on the
internet) so you need to post a small set of sample input and expected output.

Make sure you tell us which specific fields you're interested in. If the 2 text
fields are field 3 and field 7, and the integer's in field 12, the solution MAY
be (but probably isn't) as simple as:

awk 'BEGIN{FS=OFS=","}$3 == "ABC12" && $7 == "ABC12" && ($12 >=2) && ($12 <=6)' file

Ed.


A quick follow up, with a few minor changes your suggestion works well for
me:
awk 'BEGIN{FS=OFS=","}$56 == "CLH01" && $45 != "LAC" && ($3 >=100000)'
file

That'll find those specific text strings. See my other response to match any 3
upper case letters followed by 2 digits.

Many thanks, I'll have to get read up on awk
:)


Lurk around comp.lang.awk for a while and get the book Effective Awk
Programming, Third Edition By Arnold Robbins,
http://www.oreilly.com/catalog/awkprog3/.

Ed.

.

Relevant Pages