Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

filter record from a file reading another file

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting filter record from a file reading another file
# 1  
Old 04-14-2010
filter record from a file reading another file
Hi,
I want to filter record from a file if the records in the second column matches the data in another file. I tried the below awk command but it filters the records in the filter file. I want the opposite, to include only the records in the filter file.

I tried this:
Code:
awk -F'|' 'NR==FNR{_[$1];next}!($2 in _)' filter.txt record.txt

Record.txt:
Code:
00000000048717|0003|20100408|0|001|000000.084|000000.093|000000.107|
00000000048717|0004|20100408|0|001|000000.084|000000.093|000000.107|
00000000048717|0005|20100408|0|001|000000.084|000000.093|000000.107|
00000000048717|0006|20100408|0|001|000000.084|000000.093|000000.107|

Filter file:
Code:
0003

Output should be:
Code:
00000000048717|0003|000000.084|

Note that the output needs only first, second and sixth colum from input record file.

Any help will be much appreciated.
# 2  
Old 04-14-2010
Code:
awk -F'|' 'NR==FNR{_[$1];next}$2 in _ {print $1,$2,$6}' OFS='|' filter.txt record.txt

# 3  
Old 04-14-2010
Code:
awk -F\| 'NR==FNR{f[$1];next}($2 in f){print $1"|"$2"|"$6}' filter.txt record.txt



---------- Post updated at 10:56 AM ---------- Previous update was at 10:31 AM ----------

vgersh99 I've learned something new today Smilie, I'm talking about

Code:
print $1,$2,$6

Last edited by vgersh99; 04-14-2010 at 12:37 PM.. Reason: fixed code tag
# 4  
Old 04-14-2010
Thanks guys :-)

---------- Post updated at 12:08 PM ---------- Previous update was at 12:06 PM ----------

How about if I had to check for 2 columns in the Record file and I have two values in filter file.
for eg: Filter file:
Code:
0003|20100408

I should get the output only if the second column in the record file is 0003 and third column is 20100408.
# 5  
Old 04-14-2010
Quote:
Originally Posted by gpaulose
Thanks guys :-)

---------- Post updated at 12:08 PM ---------- Previous update was at 12:06 PM ----------

How about if I had to check for 2 columns in the Record file and I have two values in filter file.
for eg: Filter file:
Code:
0003|20100408

I should get the output only if the second column in the record file is 0003 and third column is 20100408.
Code:
awk -F'|' 'NR==FNR{_[$1,$2];next}($2 SUBSEP $3) in _ {print $1,$2,$6}' OFS='|' filter.txt record.txt

# 6  
Old 04-14-2010
Then you must modify like this

Code:
awk -F\| 'NR==FNR{f[$1,$2];next}($2SUBSEP$3 in f){print $1"|"$2"|"$6}' filter.txt record.txt

I never made the try with $2,$3 instead $2SUBSEP$3 but you can make the try Smilie
# 7  
Old 04-15-2010
This worked. Thanks to all of you who helped me Smilie

Quote:
Originally Posted by kcoder24
Then you must modify like this

Code:
awk -F\| 'NR==FNR{f[$1,$2];next}($2SUBSEP$3 in f){print $1"|"$2"|"$6}' filter.txt record.txt

I never made the try with $2,$3 instead $2SUBSEP$3 but you can make the try Smilie
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

CSV File:Filter duplicate records from column1 & another column having unique record

Hi Experts, I have csv file with 30, 40 columns Pasting just 2 column for problem description. Need to print error if below combination is not present in file check for column-1 (DocumentNumber) and filter columns where value in DocumentNumber field is same. For all such rows, the field... (7 Replies)
Discussion started by: as7951
7 Replies

2. Shell Programming and Scripting

Extract timestamp from first record in xml file and it checks if not it will replace first record

I have test.xml <emp><id>101</id><name>AAA</name><date>06/06/14 1811</date></emp> <Join><id>101</id><city>london</city><date>06/06/14 2011</date></join> <Join><id>101</id><city>new york</city><date>06/06/14 1811</date></join> <Join><id>101</id><city>sydney</city><date>06/06/14... (2 Replies)
Discussion started by: vsraju
2 Replies

3. UNIX for Dummies Questions & Answers

Filter records in a huge text file from a filter text file

Hi Folks, I have a text file with lots of rows with duplicates in the first column, i want to filter out records based on filter columns in a different filter text file. bash scripting is what i need. Data.txt Name OrderID Quantity Sam 123 300 Jay 342 498 Kev 78 2500 Sam 420 50 Vic 10... (3 Replies)
Discussion started by: tech_frk
3 Replies

4. Shell Programming and Scripting

awk-filter record by another file

I have file1 3049 3138 4672 22631 45324 112382 121240 125470 130289 186128 193996 194002 202776 228002 253221 273523 284601 284605 641858 (8 Replies)
Discussion started by: biomed
8 Replies

5. Shell Programming and Scripting

How to filter only comments while reading a file including line break characters.

How do I filter only comments and still keep Line breaks at the end of the line!? This is one of the common tasks we all do,, How can we do this in a right way..!? I try to ignore empty lines and commented lines using following approach. test.sh # \040 --> SPACE character octal... (17 Replies)
Discussion started by: kchinnam
17 Replies

6. Shell Programming and Scripting

Filter record from a file

Reposting since I didnt not get any reply. I have a problem while filtering records from a file. Can somebody help please? For eg: Consider the below files Record file: 0003@00000000000190@20100401@201004012010040120100401@003@... (1 Reply)
Discussion started by: gpaulose
1 Replies

7. Shell Programming and Scripting

Reading a file (one record) in a SHL script

I am trying to read a file in a shl script (only one record) and stored in a variable file_number I got the following read -u $BANNER_HOME/xxxxxxx/misc/EFTSQL.dat file_number file_number2 = $file_number + 1 echo $file_number2 > $BANNER_HOME/xxxxxx/misc/EFTSQL.dat EOF It is not working... (2 Replies)
Discussion started by: rechever
2 Replies

8. Shell Programming and Scripting

problem in reading a record from the file

Hi Guys, I need to check whether the last column is RP, If so, then i have to second column and pass it to a select statement as sonid and fetch the value to a variable and run it. This has to be done till the last column is RW. value Fatherid sonid topid ... (8 Replies)
Discussion started by: mac4rfree
8 Replies

9. Shell Programming and Scripting

Script to search a bad record in a file then put the record in the bad file

I need to write a script that can find a bad record (for example: there is date field colom but value provided in the file for this field is N/A) then script shoud searches this pattern and then insert the whole record into the bad file. Example: File1 Name designation dateOfJoining... (2 Replies)
Discussion started by: shilendrajadon
2 Replies

10. UNIX for Advanced & Expert Users

Script to search a bad record in a file then put the record in the bad file

I need to write a script that can find a bad record (for example: there is date field colom but value provided in the file for this field is N/A) then script shoud searches this pattern and then insert the whole record into the bad file. Example: File1 Name designation dateOfJoining... (1 Reply)
Discussion started by: shilendrajadon
1 Replies
Login or Register to Ask a Question