0

Have two files which need to compare the first two columns.

Sample input file1:

|CID|SID|order|orderdate|confirmdate
|151244127820|7177638911583| 2|2015-02-04 07:14:44|2015-02-04 07:15:32
|151244127820|7177638922976| 4|2015-02-04 07:16:19|2015-02-04 07:19:47
|151244127824|7177638920385| 2|2015-02-04 07:14:22|2015-02-04 07:18:48
|151244127824|7177638924073| 3|2015-02-04 07:18:40|2015-02-04 07:20:11
|151244127825|7177638921040| 1|2015-02-04 07:12:58|2015-02-04 07:19:02
|151244127827|7177638917056| 2|2015-02-04 07:14:17|2015-02-04 07:17:31
|151244127827|7177638968972| 3|2015-02-04 07:17:36|2015-02-04 07:36:22

input file2:

|cID|SID|order|orderdate|confirmdate
|151244127820|7177638911583|   2|2015-02-04 07:14:44|2015-02-04 07:15:32
|151244127820|7177638922976|   4|2015-02-04 07:16:19|2015-02-04 07:19:47
|151244127834|7177638920385|   2|2015-02-04 07:14:22|2015-02-04 07:18:48
|151244127834|7177638924073|   3|2015-02-04 07:18:40|2015-02-04 07:20:11
|151244126585|7177638921040|   1|2015-02-04 07:12:58|2015-02-04 07:19:02
|151244126585|7177638917056|   2|2015-02-04 07:14:17|2015-02-04 07:17:31
|151244127827|7177638968970|   3|2015-02-04 07:17:36|2015-02-04 07:36:22

If the CID in the input file2 is not found in file1 then write the complete row in a new file. And if the CID in input file2 is found in file1, but the SID is not found, then write the complete row in a new file.

  • In file1 the CID nos. are "2,4,2,3..."? (1st column) and file 2 the nos are "151244127820,151244127820...."(1st column)?Please clarify and show your expected output. – repzero Feb 15 '15 at 8:53
2
awk -F'|' 'FNR==NR{a[$2]=$2;b[$3]=$3;next};{if($2 in a){print $0 > "new_file_1"};if(($2 in a )&& !($3 in b)){print $0 > "new_file_2"}} file1 file2

details ...

{if($2 in a){print $0 > "new_file_1"} : if SID in file2  matches SID in file1 redirect to a file called new_file_1

..

if(($2 in a )&& !($3 in b)){print $0 > "new_file_2"} :if SID in file2 matches SID in file1 but CIDs does not match, redirect to a file called new_file_2
1
awk -F'|' '
FNR==NR{
    cid[$2]=1
    sid[$3]=1
    next
    }
{
    if (!($2 in cid))
        print
    else
        if (!($3 in sid))
            print
    }' file1 file2

Brief explanation:

Build two unique arrays (cid and sid values) from 1st file.

Than compare it to 2nd and 3rd fields (the 1st field is counted from beginning of line to first delimeter so it is empty) of each row of 2nd file and, according terms, print row.

0

I think this gets what you want:

for n in 3 0
do  nl -w1 -s."$n" <&"$n"
done 3<file1 <file2      |
sort -t\| -unk2,2 -nk3,3 |
sort -t\| -nk1,1         |
grep '^[^|]*0|'

...which in my tests printed...

4.0|151244127834|7177638920385|   2|2015-02-04 07:14:22|2015-02-04 07:18:48
5.0|151244127834|7177638924073|   3|2015-02-04 07:18:40|2015-02-04 07:20:11
6.0|151244126585|7177638921040|   1|2015-02-04 07:12:58|2015-02-04 07:19:02
7.0|151244126585|7177638917056|   2|2015-02-04 07:14:17|2015-02-04 07:17:31
8.0|151244127827|7177638968970|   3|2015-02-04 07:17:36|2015-02-04 07:36:22

And look - it's not only very simple (and likely very fast even for very large inputs) - but you even get to keep the line numbers. But if you don't want them you can just append...

... | cut -d\| -f2-

... to the tail of the pipeline.

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.