1

I'm trying to compare two files and print the output if they match on some fields.

File1.txt:

bart:29482164591748
 apu:29482164591748
smithers:68468468468464
lisa:68468468468464
maggie:2348578903247548

File2.txt:

68468468468464:keyboard
68463894578424:user
29482164591748:computer

I would like this output:

bart:29482164591748:computer
8
  • 3
    Does it have to be awk?
    – Sobrique
    Dec 23 '15 at 11:11
  • You may be more successful with join, IIRC.
    – user86969
    Dec 23 '15 at 11:11
  • Hi, nope, anything that will get the job done. Cheers
    – Chris J
    Dec 23 '15 at 11:12
  • 2
    Please edit your question and clarify. Both lisa's and smither's codes are present in File1, why should only bart be printed?
    – terdon
    Dec 23 '15 at 11:15
  • 1
    What is the exact output (post it with all example lines please) you want from this 2 files?
    – chaos
    Dec 23 '15 at 12:34
6

A classic with join:

join -t: -1 2 -2 1 -o 2.1,1.1,1.2 <(sort -t: -k1,1 file1) <(sort -t: -k2,2 file2)
  • -t: specifies the colon as separator.
  • -1 2 file1's join field is the second one
  • -2 1 file2's join field is the first one
  • -o 2.1,1.1,1.2 the output format.
  • <(...): both files must be sorted on the join field (-k1,1 and -k2,2), -t: specifies the colon as separator for sort.
4

With awk:

awk -F: 'NR==FNR{a[$1]=$2;next}a[$2]{print $1":"$2":"a[$2]}' file1 file2

Output:

bart:29482164591748:computer
smithers:68468468468464:keyboard
lisa:68468468468464:keyboard

Explanation:

  • awk -F: start awk treating colon as a fields delimiter
  • NR==FNR{} process only the first file
  • a[$1]=$2;next build an array a indexed by the first field with values of the second field then skip to the next row
  • a[$2]{} process only if value of previously build array with the index of the current second field is not empty (this is done only for the file2, because of the next word in the previous expression)
  • print $1":"$2":"a[$2] print everything as desired

After question edit:

awk -F: 'NR==FNR{a[$1]=$2;next}a[$2]{print $1":"$2":"a[$2]}' file2 file1

Output:

bart:29482164591748:computer
 apu:29482164591748:computer
smithers:68468468468464:keyboard
lisa:68468468468464:keyboard
12
  • Hi, I've just tested and it seems to work however what i stupidly failed to mention is that there will be duplicate lines, any ideas please
    – Chris J
    Dec 23 '15 at 11:47
  • 1
    @ChrisJ There are a lot of ideas, but you need to show sample input and expected output with duplicate lines.
    – jimmij
    Dec 23 '15 at 11:51
  • 1
    @ChrisJ Definitely there is no 400 lines limit in awk.
    – jimmij
    Dec 23 '15 at 12:10
  • 1
    @BumblingBadger See the edit.
    – jimmij
    Nov 14 '20 at 10:09
  • 1
    @BumblingBadger The question was modified multiple times and honestly I do not understand what OP wanted to do. Other answers in this topic don't help solving this mystery either. Looking at it right now I think that the most sense is to match bart-computer, apu-computer, smithers-keyboard and lisa-keyboard, so I changed my second answer once again accordingly. So I just switched input files and left "duplicates'. If you have similar problem and just want to select first record add ,a[$2]="" before last }.
    – jimmij
    Nov 16 '20 at 8:55
0

Wouldn't use awk, but would use perl instead.

#!/usr/bin/env perl
use strict;
use warnings;

#open both files for reading
open( my $input1, '<', "file1.txt" ) or die $!;
open( my $input2, '<', "file2.txt" ) or die $!;

#read the key-values into a hash called lookup. 
my %lookup = do { local $/; <$input1> =~ m/(\d+):(\w+)/g; };

#iterate by line of second file
while ( <$input2> ) { 
    #trim trailing linefeeds
    chomp;
    #split current line on :
    my ( $user, $key ) = split /:/;
    #if exists in original lookup, display record 
    if ( $lookup{$key} ) {
        print join ( ":", $user, $key, $lookup{$key}),"\n";
    }
}

I get a slightly different output though - specifically:

bart:29482164591748:computer
smithers:68468468468464:keyboard
lisa:68468468468464:keyboard

I'm not sure why the second 2 shouldn't be printed based on matching key-values.

If you want a one liner that's basically the same:

perl -F: -lane "print $k{$F[0]}.':'.$_ if $k{$F[0]}; $k{$F[1]}//=$F[0];" file2.txt file1.txt
4
  • I think it's a little bit overkill for this usecase
    – alexises
    Dec 23 '15 at 15:24
  • Why? It's doing basically the same thing as that awk fragment, although I'll grant it's more verbose. Didn't think we were playing code golf here.
    – Sobrique
    Dec 23 '15 at 15:44
  • I just place the question in the context of bash script, use perl oneliner could be usefull, but add a significent peace of other language code should be avoided if possible.
    – alexises
    Dec 23 '15 at 15:46
  • I don't see that in the OP's question. I do see "Hi, nope, anything that will get the job done."
    – Sobrique
    Dec 23 '15 at 16:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.