Compare and matching column entries in 2 files and
I have 2 files. File 1 has more columns (6 columns but the last column has spaces) than file 2 (file 2 has 4 columns). The entries in file 1 do not change but column 4 in file 2 can be different from the the entry in file 1. I want to create a script that reads in file 1 and then uses column 1 2 and 3 from file 1 to find the same entries in column 1 2 and 3 from file 2. When this happens I want to print out column 1 2 and 3 from file 1 and column 4 from file 2 and column 5 and 6 from file 1 all on the same line.
So file one could like like this
Code:
abc xxx hhh 555 678 line label 99 in prod support
cde rrr kkk 321 543 line label 55 in dev
And file 2 could look like this
Code:
abc xxx hhh 555 444
cde rrr kkk 321 789
I want to print out
Code:
abc xxx hhh 555 444 line label 99 in prod support
cde rrr kkk 321 789 line label 55 in dev
Note the last column in file 1 can have many characters "line label 99 in prod support"
So I have been trying to use "while read" to read in file 1 and compare it with file 2 but I can't seem to get the right syntax. I have also tried using various awk commands. Here is the latest while read I tried....
Code:
while read VNX2 DM2 FS2 SZ2 GL_NUM2 GL_NAME2
do
if egrep "$VNX2 $DM2 $FS2" f2.out
then
SZ=`cat f2.out | egrep "$VNX2 $DM2 $FS2" | awk '{print $4}'`
echo "$VNX2 $DM2 $FS2 $SZ $GL_NUM2 $GL_NAME2"
else
echo "Error...."
fi
done < f1.out
I'd appreciate any tips or help on how to do this. Thanks!
---------- Post updated at 04:25 PM ---------- Previous update was at 04:19 PM ----------
Sorry I got my columns mixed up. Column 4 needs to be swapped with column 5 in the 2 line sample I submitted. Hope this make sense. Thanks!
@kieranfoley. There seem to be some contradictions between your input and output files and your description. Although the solutions given apparently "work perfectly", could you please clarify what you mean, so this thread becomes more comprehensible?
Is the input file TAB-separated or space-separated?
The second input file seems to contain 5 columns, not 4
The order of the fields seems to be fields 1,2,3,4 from file1, field 5 from file 2 and the rest from file 1 again?
--edit--
If point 2 and 3 are so, then this would also produce that output:
Code:
awk '{i=$1 FS $2 FS $3} NR==FNR{A[i]=$5; next} i in A{$5=A[i]}1' file1 file2
Last edited by Scrutinizer; 02-14-2014 at 05:39 PM..
Example:
I have files in below format
file 1:
zxc,133,joe@example.com
cst,222,xyz@example1.com
File 2 Contains:
hxd
hcd
jws
zxc
cst
File 1 has 50000 lines and file 2 has around 30000 lines :
Expected Output has to be :
hxd
hcd
jws (5 Replies)
Hi,
I hope somebody can help me with this problem, since I would like to solve this problem using awk, but im not experienced enough with this.
I have two files which i want to match, and output the matching column name and row number.
One file contains 4 columns like this:
FILE1:
a ... (6 Replies)
Dear All,
I would like to compare two files and return the number of matches found.
Example
File A
Lx2
L1_Mus1
L1Md_T
Lx5
L1M2
L1_Mus3
Lx3_Mus
Lx9
Lx2A
L1Md_A
L1Md_F2
File B
L1_Mus3
L1_Mus3 (3 Replies)
Dear All,
I would appreciate any help..At the moment, my work is stuck cos of my inability to resolve this issue.
Which is following:
I have two files with the arrngment like this
file-1
190645 12 3596022
190645 12 3764915
190645 16 3803981
190645 12 3854102
190645 12 4324593
190645... (12 Replies)
Hi,
I am writing a comparator script, which comapre two txt files(column by column)
below are the precondition of this comparator
1)columns of file are not seperated
Ex.
file1.txt
8888812341181892
1243548895685687
8945896789897789
1111111111111111
file2.txt
9578956789567897... (2 Replies)
Hi,
Can anyone help me to compare two files and get the matching data... say i have file1 and file2 ... file1 has 300 unique data with that i need to match with file2 to see how may are matching.. file2 have 1000 records. (4 Replies)
Here is my situation. I need to compare two tab separated files (diff is not useful since there could be known difference between files).
I have found similar posts , but not fully matching.I was thinking of writing a shell script using cut and grep and while loop but after going thru posts it... (2 Replies)
I am using solaris, need either awk/shell or perl script to compate two files.
both of these file1 and file2 are located in two diffent location path in the same server.
file1 location: /export/db/mna
file2: /etc/dba
The script will compare in file1 first field (e.g. abc00asp)before colon:... (8 Replies)