Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

Match string in two files and add data to one file

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Match string in two files and add data to one file
# 1  
Old 03-04-2015
Match string in two files and add data to one file
Gents,

file1
Code:
S  65733.00  19793.00  1              0        318592.8 2792489.5  29.1063000008
S  65733.00  19801.00  1              0        323120.8 2789153.6  13.3063000044
S  66009.00  19713.00  1              0        318672.7 2792538.2  30.6063000120
S  65801.00  19799.00  1              0        323136.5 2789108.6  13.3063000156
S  65733.00  19747.00  1              0        318650.8 2792581.2  30.2063000232
S  65733.00  19753.00  1              0        318631.5 2792607.3  29.7063000330
S  65799.00  19799.00  1              0        323160.6 2789067.9  13.6063000424
S  66015.00  19713.00  1              0        318598.4 2792665.9  30.0063000500
S  65733.00  19761.00  1              0        323216.1 2789042.9  15.9063000520
S  65733.00  19765.00  1              0        318575.0 2792706.1  30.0063000614
S  65795.00  19801.00  1              0        323248.5 2789007.8  19.4063000632
S  66019.00  19713.00  1              0        318551.2 2792752.3  30.3063000838
S  65797.00  19797.00  1              0        323149.6 2788993.6  15.1063000856
S  66019.00  19721.00  1              0        318727.0 2792852.7  31.9063000959
S  65733.00  19773.00  1              0        323167.6 2788954.7  17.5063001017
S  65733.00  19775.00  1              0        318749.1 2792810.5  31.1063001053

file2
Code:
   65733.00  19747.00  1221
   65733.00  19753.00  1221
   65733.00  19761.00  1222
   65733.00  19765.00  1222
   65733.00  19773.00  1223
   65733.00  19775.00  1223
   65733.00  19793.00  1224
   65733.00  19801.00  1224

output
Code:
S  65733.00  19793.00  1              0        318592.8 2792489.5  29.1063000008 1224
S  65733.00  19801.00  1              0        323120.8 2789153.6  13.3063000044 1224
S  66009.00  19713.00  1              0        318672.7 2792538.2  30.6063000120
S  65801.00  19799.00  1              0        323136.5 2789108.6  13.3063000156
S  65733.00  19747.00  1              0        318650.8 2792581.2  30.2063000232 1221
S  65733.00  19753.00  1              0        318631.5 2792607.3  29.7063000330 1221
S  65799.00  19799.00  1              0        323160.6 2789067.9  13.6063000424
S  66015.00  19713.00  1              0        318598.4 2792665.9  30.0063000500
S  65733.00  19761.00  1              0        323216.1 2789042.9  15.9063000520 1222
S  65733.00  19765.00  1              0        318575.0 2792706.1  30.0063000614 1222
S  65795.00  19801.00  1              0        323248.5 2789007.8  19.4063000632
S  66019.00  19713.00  1              0        318551.2 2792752.3  30.3063000838
S  65797.00  19797.00  1              0        323149.6 2788993.6  15.1063000856
S  66019.00  19721.00  1              0        318727.0 2792852.7  31.9063000959
S  65733.00  19773.00  1              0        323167.6 2788954.7  17.5063001017 1223
S  65733.00  19775.00  1              0        318749.1 2792810.5  31.1063001053 1223

I got the desired output with this.

Code:
awk '{x=substr($0,1,80)} FNR==NR{a[substr($0,4,15)]=$3;next} {print x, a[substr($0,4,15)] }' file2 file1

.

But what about if i have file3 is like this
Code:
65733.00  19747.00  1221   
65733.00  19753.00  1221   
65733.00  19761.00  1222   
65733.00  19765.00  1222   
65733.00  19773.00  1223   
65733.00  19775.00  1223   
65733.00  19793.00  1224   
65733.00  19801.00  1224

Where the string 1-15 are not in the same location in file2 where this string is in columns 4-18..

I notice that the code above works only if the values to match are exactly in the same location.. How to solve it if the values to match are in different locations as the example. Then how I can get the same output using file3 and file1.

Thanks for your help
# 2  
Old 03-04-2015
Use fields instead. The fields do not care about the number of spaces, just the order, which in this case is maintained.

Code:
awk 'FILENAME=="file3" { arr [$1 $2]=$3}
         FILENAME=="file1" { 
                               string=""
                               if( "$2 $3" in arr ){string=arr[$2 $3]}
                               print $0, string 
                             } '  file3 file1

This User Gave Thanks to jim mcnamara For This Post:
jiam912
# 3  
Old 03-05-2015
Thanks Smilie
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. UNIX for Beginners Questions & Answers

Data match 2 files based on first 2 columns matching only and join if match

Hi, i have 2 files , the data i need to match is in masterfile and i need to pull out column 3 from master if column 1 and 2 match and output entire row to new file I have tried with join and awk and i keep getting blank outputs or same file is there an easier way than what i am... (4 Replies)
Discussion started by: axis88
4 Replies

2. Shell Programming and Scripting

Match string from two files and print line

Hi, I have been trying to find help with my issue and I'm thinking awk may be able to do it. I have two files eg file1.txt STRING1 230 400 0.36 STRING2 400 230 -0.13 STRING3 130 349 1 file2.txt CUFFFLINKS 1 1394 93932 . + STRING1 CUFFFLINKS ... (9 Replies)
Discussion started by: zward
9 Replies

3. Shell Programming and Scripting

awk partial string match and add specific fields

Trying to combine strings that are a partial match to another in $1 (usually below it). If a match is found than the $2 value is added to the $2 value of the match and the $3 value is added to the $3 value of the match. I am not sure how to do this and need some expert help. Thank you :). file ... (2 Replies)
Discussion started by: cmccabe
2 Replies

4. Shell Programming and Scripting

Compare 2 files of csv file and match column data and create a new csv file of them

Hi, I am newbie in shell script. I need your help to solve my problem. Firstly, I have 2 files of csv and i want to compare of the contents then the output will be written in a new csv file. File1: SourceFile,DateTimeOriginal /home/intannf/foto/IMG_0713.JPG,2015:02:17 11:14:07... (8 Replies)
Discussion started by: refrain
8 Replies

5. Shell Programming and Scripting

Match and extract data using two files

Hello, Using the information in file 1, I would like to extract from file2 all rows which matchs in column 3. file 1 1233 1230 1231 1232 file2 65733.00 19775.00 1220 65733.00 19793.00 1220 65733.00 19801.00 1220 65733.00 19809.00 1231 65733.00 19817.00 ... (2 Replies)
Discussion started by: jiam912
2 Replies

6. Shell Programming and Scripting

awk help: Match data fields from 2 files & output results from both into 1 file

I need to take 2 input files and create 1 output based on matches from each file. I am looking to match field #1 in both files (Userid) and create an output file that will be a combination of fields from both file1 and file2 if there are any differences in the fields 2,3,4,5,or 6. Below is an... (5 Replies)
Discussion started by: ambroze
5 Replies

7. Shell Programming and Scripting

Compare 2 files and match column data and align data from 3 column

Hello experts, Please help me in achieving this in an easier way possible. I have 2 csv files with following data: File1 08/23/2012 12:35:47,JOB_5330 08/23/2012 12:35:47,JOB_5330 08/23/2012 12:36:09,JOB_5340 08/23/2012 12:36:14,JOB_5340 08/23/2012 12:36:22,JOB_5350 08/23/2012... (5 Replies)
Discussion started by: asnandhakumar
5 Replies

8. Shell Programming and Scripting

AWK to match and merge data from 2 files into 1.

Hello, hopefully this is an easy on for the AWK guru's out there. I'm having some trouble figuring out how to match+merge data in 2 files into 1 single report. I've got my 2 files filtered and delimited, just need to MATCH $3 in file1 to $1 in file2, then put $0 from File1 and $2+$3 from File2... (6 Replies)
Discussion started by: right_coaster
6 Replies

9. Shell Programming and Scripting

How to add data from 2 input files and save it in 1 output file

Hi, i have 2 input files which are file1.txt and file2.txt. I need to extract data from file1.txt and file2.txt and save it in file3.txt like example below:- File1.txt ID scrap1 Name scrap1 start 1 end 10 ID scrap2 Name scrap2 start 11 end ... (4 Replies)
Discussion started by: redse171
4 Replies

10. Shell Programming and Scripting

Appending string to match pattern (data processing)

Hello i have go the following result from performing 2 testing using the same file. I have used unix script to extract the result because the files are many as shown below. 01_gravity.f.tcov 7 3 42.86 02_gravity.f.tcov 9 4 80.86... (4 Replies)
Discussion started by: ganiel24
4 Replies
Login or Register to Ask a Question