Merging 2 files and keeping the one duplicate

Question

I want to create a file that contains columns from two input files.

File1 is like:

11111111        abc12345   Y
22222222        xyz23456   Y

File2:

11111111       abc12345    
33333333       kbc34567

The Output should be like :

11111111        abc12345   Y
22222222        xyz23456   Y
33333333        kbc34567

I have tried the following and was able to find the duplicate but I need the duplicate entry from first file and other records as well and with same formatting(same amount of space between 1st, 2nd and 3rd column) :

awk 'FNR==NR{a[$1$2]=$1$2;next} ($1$2 in a) {print a[$1$2],$3}' file2 file1

Answer 1

The space is being changed because you are printing i) the 1st and 2nd field concatenated and ii) the third field. By default, awk uses a space as the output field separator (OFS), so that messes up your spacing. A simple solution is to save the line itself ($0) in the array instead of the fields:

a[$1$2]=$0;

However, your script doesn't do what you want anyway. It will only print lines from file1 that were present in file2, so anything that is only in file1 will be skipped. According to your desired output, you want to print all lines from both files and, if any line of file2 has the same 1st two fields as one in file1, print only the corresponding line from file1. You can do this in awk with:

awk 'FNR==NR{a[$1$2]=$0; print} !($1$2 in a) {print}' file1 file2 

That will save each line of file1 in the array and also print it. Then, when file2 is being processed, it will print any line whose 1st two fields aren't in a.

---

Note that you can also do this using sort:

$ sort -uk1,2 file1 file2 
11111111    abc12345    Y
22222222    xyz23456    Y
33333333    kbc34567

You just need to make sure the amount of whitespace in the two files is the same (which isn't the case in your example), or make it so with:

$ sed  's/  */\t/g' file1 file2 | sort -uk1,2 
11111111    abc12345    Y
22222222    xyz23456    Y
33333333    kbc34567

Answer 2

Depending on how large your files are, this may not be the most efficient method, but I think it will work for the specific case. It does not require that the files are in any particular order, but does require that you always prefer File1 over File2:

#!/bin/bash
# Make a list of the unique identifiers in each of the files, changing the whitespace in between into a comma.
awk '{print $1 "," $2}' File1 File2| sort | uniq |
# Loop through all the unique identifiers we just found
while read l; do
    # Create a regular expression for each identifier to use as
    #  a search term, changing the comma into "any number of whitespaces"
    searchterm=$(echo $l | sed 's/,/\\\s*/')
    # if this pattern exists in File1
    if $(grep -E "$searchterm" File1 >& /dev/null); then
        # print it out
        grep -E "$searchterm" File1
    else
        # otherwise, print it if it's in File2
        grep -E "$searchterm" File2
    fi
done 

If you want File3 you can save this as a script and send the output there

#copy to merge_uniq.sh
chmod +x merge_uniq.sh
merge_uniq.sh > File3

Answer 3

awk 'BEGIN{i=0} {if (!($1$2  in a)) {a[$1$2]=$0; index_array[i] =$1$2; i++} } END{for (j=0; j<i; j++) print a[index_array[j]]}' 1 2

11111111        abc12345   Y

22222222        xyz23456   Y
33333333       kbc34567