Compare and matching column entries in 2 files and


 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Compare and matching column entries in 2 files and
# 1  
Old 02-14-2014
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!
# 2  
Old 02-14-2014
please fix your request so that the requirements are clear.
# 3  
Old 02-14-2014
Here is an awk approach:
Code:
awk '
        NR == FNR {
                k = $1 FS $2 FS $3 FS $4
                $1 = $2 = $3 = $4 = $5 = ""
                sub ( /^ */, x )
                A[k] = $0
                next
        }
        {
                k = $1 FS $2 FS $3 FS $4
                if ( k in A )
                        print k, $5, A[k]
        }
' file1 file2

# 4  
Old 02-14-2014
Code:
awk 'function key(){
                      return $1 FS $2 FS $3 FS $4
                   }
            FNR==NR{
                     A[key()]=$0
                     next
                   }
   ((v=key()) in A){
                     while(++i<=5)$i=x;
                     $0=$0; $1=$1
                     print A[v],$0;i=0
                   }
    ' file2 file1

Code:
abc   xxx    hhh    555 444 line label 99 in prod support
cde   rrr     kkk     321 789 line label 55 in dev

# 5  
Old 02-14-2014
Thank you. This works perfect!Smilie
# 6  
Old 02-14-2014
@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 04:39 PM..
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Need awk or Shell script to compare Column-1 of two different CSV files and print if column-1 matche

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)
Discussion started by: TestPractice
5 Replies

2. Shell Programming and Scripting

Matching two files per column

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)
Discussion started by: Jenna.bos
6 Replies

3. Shell Programming and Scripting

Compare two files and count number of matching lines

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)
Discussion started by: paolo.kunder
3 Replies

4. Shell Programming and Scripting

Compare two files with different column entries..:-(

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)
Discussion started by: emily
12 Replies

5. Shell Programming and Scripting

Compare Two Files(Column By Column) In Perl or shell

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)
Discussion started by: kumar96877
2 Replies

6. Shell Programming and Scripting

Compare two files and get matching data

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)
Discussion started by: zooby
4 Replies

7. Shell Programming and Scripting

Compare files column to column based on keys

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)
Discussion started by: blackjack101
2 Replies

8. Shell Programming and Scripting

compare two columns of different files and print the matching second file..

Hi, I have two tab separated files; file1: S.No ddi fi cu o/l t+ t- 1 0.5 0.6 o 0.1 0.2 2 0.2 0.3 l 0.3 0.4 3 0.5 0.8 l 0.1 0.6 ... (5 Replies)
Discussion started by: vasanth.vadalur
5 Replies

9. Shell Programming and Scripting

compare two files and to remove the matching lines on both the files

I have two files and need to compare the two files and to remove the matching lines from both the files (4 Replies)
Discussion started by: shellscripter
4 Replies

10. Shell Programming and Scripting

compare two files for matching in solaris

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)
Discussion started by: amir07
8 Replies
Login or Register to Ask a Question