Compare columns of two files and retrieve data

Shell Programming and Scripting
1

Hi guys, I need your help.
I have two files:
file1

1
3
5

file2

1,XX
2,AA
3,BB
4,CC
5,DD

I would like to compare the first column and where they are equal to write that output in a new file:

1,XX
3,BB
5,DD

It is supposed that all numbers from file1 should be presented in file2.
And is it possible, just in case if the number is not available in file2, to write only the number from file1 and leave second column empty or fill it with N/A
E.g.

00,
or
00,N/A

Thanks in advance!

2 
awk -F, '
        NR == FNR {
                A[$1]
                next
        }
        $1 in A {
                B[$1]
                print
        }
        END {
                for ( k in A )
                {
                        if ( !( k in B ) )
                                print k, "N/A"
                }
        }
' OFS=, file1 file2
1 Like
3 
join -t, file1 file2 
1,XX
3,BB
5,DD
4

The join command can be slightly modified to meet OP's requirement:

join -t, -a1 -1 1 -2 1 -e "N/A" -o 1.1 2.2 file1 file2
1 Like
5

Hi,
I tried with awk, but I am getting syntax error.
This with join looks nice, but It doesn't work, just doing nothing.
My OS is:

 Solaris 10 10/08 s10s_u6wos_07b SPARC
6

Try nawk or /usr/xpg4/bin/awk, instead of awk.

1 Like
7

awk 'NR==FNR{A[$9];next}!($9 in A)' test.txt test2.txt --> change column name

above one will compare 9th column of two files and display the difference

1 Like
8

Yes, /usr/xpg4/bin/awk is working.
Thanks all!