0

Merge Two files depending on a column, nth occurrence of a string in column1 of file1 should be merged with nth occurrence of the same string in column1 of file2. I tried join but the results are not as expected.

join <(sort file1) <(sort file2)| column -t | tac | sort | uniq > file3

file1

CAAX-MC oracle.log.ApplicationScript 1 7 CRM
CAAZ-TEST-MC oracle.log.ApplicationScript 1 7 CRM
DAA   oracle.log.ApplicationScript 1 7 CRM
DJF oracle.log.ApplicationScript 1 6 CRM
DJF oracle.apps.appslogger 5 6 CRM

file 2

CAAX-MC CRMDomain
CAAZ-TEST-MC CRMDomain
DJF CRMDomain
DJF CommonDomain,CRMDomain,FinancialDomain

file 3 -- desired output:

CAAX-MC oracle.log.ApplicationScript 1 7 CRM CRMDomain
CAAZ-TEST-MC oracle.log.ApplicationScript 1 7 CRM CRMDomain
DAA   oracle.log.ApplicationScript 1 7 CRM 
DJF oracle.log.ApplicationScript 1 6 CRM CRMDomain
DJF oracle.apps.appslogger 5 6 CRMCommonDomain,CRMDomain,FinancialDomain
  • 1
    Welcome on U&L. What result do you expect? Would join <(sort -r test) <(sort -r common_test.log) | uniq -w20 helps? – Costas Oct 9 '15 at 8:11
  • beside using tac | sort | uniq can be abriviated in sort -u (sort will rearrange result of tac, so tac is useless, and -u flag will make unique) – Archemar Oct 9 '15 at 8:14
  • Thank you Costas. I would like the Result to be as in FILE 3. But "join <(sort -r test) <(sort -r common_test.log) | uniq -w20" does not help – neha dhawan Oct 9 '15 at 8:14
0

Use awk:

awk 'FNR==NR{a[NR-1]=$0} 
  FNR!=NR{for(i in a){split(a[i],x," ");
    if(x[1]==$1){$0=$0" "x[2];delete a[i];break}}print;}' file2 file1

Notice the argument order: file2 is before file1.

  • FNR==NR: applies only to the first file (in the arguments list): file2.
    • a[NR-1]=$0: fills an array a with the lines of file2.
  • FNR!=NR: applies to file1.
    • for(i in a): loop trough the prevously created array a
    • split(a[i],x," "): split the value (line of file2) at the space and store it in the new array with name x.
    • if(x[1]==$1): if the first element of x (x[1]) is equal to the first field ($1) of file1 (if the first field is found in the array) then:
    • $0=$0" "x[2] set the line to be printed with the new value at the end x[2].
    • delete a[i];break since you want the next occurence of that index when it appears again in file1 (for example DJF), we need to delete that element of the array a and fall out of the for loop (break).
    • print: whether the element is found in the array or not doesn't matter, the line (of file1) should be printed anyway.

The output:

CAAX-MC oracle.log.ApplicationScript 1 7 CRM CRMDomain
CAAZ-TEST-MC oracle.log.ApplicationScript 1 7 CRM CRMDomain
DAA   oracle.log.ApplicationScript 1 7 CRM
DJF oracle.log.ApplicationScript 1 6 CRM CRMDomain
DJF oracle.apps.appslogger 5 6 CRM CommonDomain,CRMDomain,FinancialDomain
0

If files is ordered before try awk

awk '
    {
        a=$1                #store key field in var a
        b=$2                #store second field in var b
        do {
            getline <"test" #get line from test file
            if(l)           #if var l is not empty
                l=l"\n"$0   #add line to var l separated by newline
            else
                l=$0        #otherways(var l is empty) put line into var l
            }
        while(a!=$1)        #repeats above while key fields will be equal 
        print l,b           #prints stored lines + b part of first file
        l=""                #empty line store variable
     }' common_test.log

outputs:

CAAX-MC oracle.log.ApplicationScript 1 7 CRM CRMDomain

CAAZ-TEST-MC oracle.log.ApplicationScript 1 7 CRM CRMDomain

DAA   oracle.log.ApplicationScript 1 7 CRM

DJF oracle.log.ApplicationScript 1 6 CRM CRMDomain

DJF oracle.apps.appslogger 5 6 CRM CommonDomain,CRMDomain,FinancialDomain
  • Thank You. It worked.Can you explain the code briefly?? – neha dhawan Oct 9 '15 at 9:41

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.