question on column searching with grep

atjurhs · 02-01-2013, 10:40 AM

Hi guys,

i'm working with some VERY large csv data files (bigger than 100Mb) and i'm looking for a specific number that i know before i search and i know what column it will be in if it's in the file before i search. right now I've been using

Code:

 grep -w "12345" file.txt > found.txt

but in a very large file 12345 can occur in several places that are not in the column that i want to search

is there an option in grep that i can use to specify which column ti search for the 12345 in? i couldn't find one

thanks, Tabby

druuna · 02-01-2013, 11:24 AM

This looks like a job for awk, not grep:

Code:

 awk -F";" ' $4 ~ "^12345$" { print }' infile

The above assumes a ; as separator (green part). The blue part is the column number.

danielbmartin · 02-01-2013, 12:36 PM

Quote:

Originally Posted by atjurhs

i'm working with some VERY large csv data files ...

druuna gave good advice. I'll offer another idea which may be helpful when dealing with VERY large files.

Sometimes you are searching for a specific string and know there is one and only one match. As soon as that match is found there is no value in continuing the search through the rest of the file. In that case you may terminate the seach by using the exit option.

Similarly you may quit after finding the third match (or whatever suits your purposes).

Here are some examples which use the famous poem by Edgar Allen Poe, "The Raven."

Code:

 
echo; echo "Method of LQ Member danielbmartin #1"
awk ' $4 ~ "Nevermore" {print}' $Raven > $OutFile
echo "ALL lines containing 'Nevermore' in the fourth word ..."; cat $OutFile

echo; echo "Method of LQ Member danielbmartin #2"
awk ' $4 ~ "Nevermore" {print;exit}' $Raven > $OutFile
echo "The FIRST line containing 'Nevermore' in the fourth word ..."; cat $OutFile

echo; echo "Method of LQ Member danielbmartin #3"
awk ' $4 ~ "Nevermore" {if (++k==2) {print; exit}}' $Raven > $OutFile
echo "The SECOND line containing 'Nevermore' in the fourth word ..."; cat $OutFile

echo; echo "Method of LQ Member danielbmartin #4"
awk ' $4 ~ "Nevermore" {print; if (++k==3) {exit}}' $Raven > $OutFile
echo "The FIRST 3 lines containing 'Nevermore' in the fourth word ..."; cat $OutFile

This is the output generated by that code.

Code:

Method of LQ Member danielbmartin #1
ALL lines containing 'Nevermore' in the fourth word ...
Quoth the raven, 'Nevermore.'
Meant in croaking 'Nevermore.'
Quoth the raven, 'Nevermore.'
Quoth the raven, 'Nevermore.'
Quoth the raven, 'Nevermore.'
Quoth the raven, 'Nevermore.'

Method of LQ Member danielbmartin #2
The FIRST line containing 'Nevermore' in the fourth word ...
Quoth the raven, 'Nevermore.'

Method of LQ Member danielbmartin #3
The SECOND line containing 'Nevermore' in the fourth word ...
Meant in croaking 'Nevermore.'

Method of LQ Member danielbmartin #4
The FIRST 3 lines containing 'Nevermore' in the fourth word ...
Quoth the raven, 'Nevermore.'
Meant in croaking 'Nevermore.'
Quoth the raven, 'Nevermore.'

Daniel B. Martin

allend · 02-01-2013, 06:33 PM

This could also be done using

Code:

cut -d, -f<column number> file.txt | grep "12345" > found.txt

David the H. · 02-04-2013, 09:57 AM

Quote:

Originally Posted by allend

This could also be done using

Code:

cut -d, -f<column number> file.txt | grep "12345" > found.txt

Except that that first discards all the other columns, then grep only matches the number, which you already know, from the remaining one. It may be useful for determining if the value appears in the file, and perhaps what line number it's on (with the -n switch), but not for much else.

Speaking of grep, it is usually possible to create a regex that matches everything up to, and including, the column you want.

Code:

grep -Ew '^([0-9]+[ ]+){4}12345' infile.txt

This will match the fifth column, assuming that the file is space-delimited and the columns only contain digits. It would have to be customized to suit each individual data format you'd want to use it on.

It's much better to just use awk for this.

atjurhs · 02-04-2013, 12:47 PM

yep,

druuna's answer looks very straight forward