Match a line in File 1 with Column in File 2 and print whole line in file 2 when matched


 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Match a line in File 1 with Column in File 2 and print whole line in file 2 when matched
# 1  
Old 06-07-2011
Data Match a line in File 1 with Column in File 2 and print whole line in file 2 when matched

Hi Experts,

I am very new to scripting and have a prb since few days and it is urgent to solve so much appreciated if u help me.

i have 2 files

file1.txt
Code:
9647810043118
9647810043126
9647810043155
9647810043161
9647810043166
9647810043185
9647810043200
9647810043203
9647810043250
9647810043277


file2.txt
Code:
418200100045528,2100,A,ALL,T11,9647810042955,9647802999956
418200100045549,2100,A,ALL,T11,9647810042976,9647802999956
418200100045652,2100,A,ALL,T11,9647810043081,9647802999956
418200100045665,2100,A,ALL,T11,9647810043094,9647802999956
418200100045688,2100,A,ALL,T11,9647810043118,9647802999956
418200100045696,2301,A,ALL,T11,9647810043126,9647802999978
418200100045724,2100,A,ALL,T11,9647810043155,9647802999956
418200100045730,2100,A,ALL,T11,9647810043161,9647802999956
418200100045735,2100,A,ALL,T11,9647810043166,9647802999977
418200100045754,2301,A,ALL,T11,9647810043185,9647802999956
418200100045769,2100,A,ALL,T11,9647810043200,9647802999938
418200100045772,2100,A,ALL,T11,9647810043203,9647802999902
418200100045817,2100,A,ALL,T11,9647810043250,9647802999902
418200100045844,2100,A,ALL,T11,9647810043277,9647802999902
418200100045851,2100,A,ALL,T11,9647810043284,9647802999981
418200100045886,2100,A,ALL,T11,9647810043320,9647802999938
418200100045912,2301,A,ALL,T11,9647810043356,9647802999980
418200100045946,2100,A,ALL,T11,9647810043393,9647802999938
418200100046067,2100,A,ALL,T11,9647810043515,9647802999904
418200100046068,0,A,ALL,T11,9647810043516,9647802999938
418200100046091,2301,A,ALL,T11,9647810043540,9647802999938
418200100046132,2100,A,ALL,T11,9647810043582,9647802999980
418200100046135,2100,A,ALL,T11,9647810043585,9647802999980
418200100046168,2100,A,ALL,T11,9647810043618,9647802999923
418200100046239,2100,A,ALL,T11,9647810043691,9647802999938
418200100046285,2100,A,ALL,T11,9647810043738,9647802999980
418200100046364,2301,A,ALL,T11,9647810043818,9647802999980
418200100046382,2100,A,ALL,T11,9647810043836,9647802999980


this is just a sample, the original files are much bigger (file1 is 100K records and file 2 is 3750K records)

the result.txt should be:
Code:
418200100045688,2100,A,ALL,T11,9647810043118,9647802999956
418200100045696,2301,A,ALL,T11,9647810043126,9647802999978
418200100045724,2100,A,ALL,T11,9647810043155,9647802999956
418200100045730,2100,A,ALL,T11,9647810043161,9647802999956
418200100045735,2100,A,ALL,T11,9647810043166,9647802999977
418200100045754,2301,A,ALL,T11,9647810043185,9647802999956
418200100045769,2100,A,ALL,T11,9647810043200,9647802999938
418200100045772,2100,A,ALL,T11,9647810043203,9647802999902
418200100045817,2100,A,ALL,T11,9647810043250,9647802999902
418200100045844,2100,A,ALL,T11,9647810043277,9647802999902

which is doing matching line in 1st file to the 6th column (, separated) in file2; when matched prints the full line in file2 to a new file

the script i am using is
Code:
for line in `awk '{print $1 }' $1`   
do                                   
grep $line $2 results_$1             
done

but it is taking too long, would any one please help me in this?

Thank you in advance and much appreciated

Last edited by Franklin52; 06-07-2011 at 10:20 AM.. Reason: Please use code tags
# 2  
Old 06-07-2011
Code:
awk -F"," 'NR==FNR {a[$0]=$0;next} ($6 in a) { print $0 }' file1 file2

# 3  
Old 06-07-2011
Thank u very much for ur reply,

i got the error shown:
Code:
root@intestn2> awk -F"," 'NR==FNR {a[$0]=$0;next} ($6 in a) { print $0 }' ccc.txt ddd.txt
awk: syntax error near line 1
awk: bailing out near line 1
root@intestn2>


Last edited by Franklin52; 06-07-2011 at 10:21 AM.. Reason: Please use code tags
# 4  
Old 06-07-2011
Code:
 
Use nawk or /usr/xpg4/bin/awk on Solaris.

# 5  
Old 06-07-2011
Hi, the awk worked, my files names were wrong, now there is no output Smilie
# 6  
Old 06-07-2011
I believe your files are not "," separated then; For the input files you provided , I got the desired output.

Please also make sure that , file1 does not have trailing spaces!!
# 7  
Old 06-07-2011
Hi, the files are the same as i pasted ..

file1 has no separators it has just those lines with numbers and the , separator has no space before or after the word or the number in file 2, am i missing something?
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. UNIX for Beginners Questions & Answers

Compare 1st column from 2 file and if match print line from 1st file and append column 7 from 2nd

hi I have 2 file with more than 10 columns for both 1st file apple,0,0,0...... orange,1,2,3..... mango,2,4,5..... 2nd file apple,2,3,4,5,6,7... orange,2,3,4,5,6,8... watermerlon,2,3,4,5,6,abc... mango,5,6,7,4,6,def.... (1 Reply)
Discussion started by: tententen
1 Replies

2. UNIX for Beginners Questions & Answers

Match Fields between two files, print portions of each file together when matched in ([g]awk)'

I've written an awk script to compare two fields in two different files and then print portions of each file on the same line when matched. It works reasonably well, but every now and again, I notice some errors and cannot seem to figure out what the issue may be and am turning to you for help. ... (2 Replies)
Discussion started by: jvoot
2 Replies

3. Shell Programming and Scripting

Print next line beside preceding line on column match

Hi, I have some data like below: John 254 Chris 254 Matt 123 Abe 123 Raj 487 Moh 487 How can i print it using awk to have: 254 John,Chris 123 Matt,Abe 487 Raj,Moh Thanks. (4 Replies)
Discussion started by: james2009
4 Replies

4. UNIX for Beginners Questions & Answers

Match Strings between two files, print portions of each file together when matched ([g]awk)

I have two files and desire to use the strings from $1 of file 1 (file1.txt) as search criteria to find matches in $2 of file 2 (file2.txt). If matches are found I want to output the entire line of file 2 (file2.txt) followed by fields $2-$11 of file 1 (file1.txt). I can find the matches, I cannot... (7 Replies)
Discussion started by: jvoot
7 Replies

5. Shell Programming and Scripting

Input file needs to match a column and print the entire line

I have a file with class c IP addresses that I need to match to a column and print the matching lines of another file. I started playing with grep -if file01.out file02.out but I am stuck as to how to match it to a column and print the matching lines; cat file01.out 10.150.140... (5 Replies)
Discussion started by: lewk
5 Replies

6. Shell Programming and Scripting

Regex: print matched line and exact pattern match

Hi experts, I have a file with regexes which is used for automatic searches on several files (40+ GB). To do some postprocessing with the grep result I need the matching line as well as the match itself. I know that the latter could be achieved with grep's -o option. But I'm not aware of a... (2 Replies)
Discussion started by: stresing
2 Replies

7. Shell Programming and Scripting

Match multiple patterns in a file and then print their respective next line

Dear all, I need to search multiple patterns and then I need to print their respective next lines. For an example, in the below table, I will look for 3 different patterns : 1) # ATC_Codes: 2) # Generic_Name: 3) # Drug_Target_1_Gene_Name: #BEGIN_DRUGCARD DB00001 # AHFS_Codes:... (3 Replies)
Discussion started by: AshwaniSharma09
3 Replies

8. Shell Programming and Scripting

read file line by line print column wise

I have a .csv file which is seperated with (;) inputfile --------- ZZZZ;AAAA;BBB;CCCC;DDD;EEE; YYYY;BBBB;CCC;DDDD;EEE;FFF; ... ... reading file line by line till end of file. while reading each line output format should be . i need to print only specific columns let say 5th... (2 Replies)
Discussion started by: rocking77
2 Replies

9. Shell Programming and Scripting

Strings from one file which exactly match to the 1st column of other file and then print lines.

Hi, I have two files. 1st file has 1 column (huge file containing ~19200000 lines) and 2nd file has 2 columns (small file containing ~6000 lines). ################################# huge_file.txt a a ab b ################################## small_file.txt a 1.5 b 2.5 ab ... (4 Replies)
Discussion started by: AshwaniSharma09
4 Replies

10. Shell Programming and Scripting

Awk+Grep Input file needs to match a column and print the entire line

I'm having problems since few days ago, and i'm not able to make it works with a simple awk+grep script (or other way to do this). For example, i have a input file1.txt: cat inputfile1.txt 218299910417 1172051195 1172070231 1172073514 1183135117 1183135118 1183135119 1281440202 ... (3 Replies)
Discussion started by: poliver
3 Replies
Login or Register to Ask a Question