2

I have 2 text files

1.txt

AA;00000;
BB;11111;
GG;22222;

2.txt

KK;WW;55555;11111;
KK;FF;ZZ;11111;
KK;RR;YY;11111;

I try to generate this 3.txt output:

AA;00000;
BB;11111;KK;WW;55555;KK;FF;ZZ;KK;RR;YY;
GG;22222;

and after removing duplicates fields I should to have this

AA;00000;
BB;11111;KK;WW;55555;FF;ZZ;RR;YY;
GG;22222;

In simply words: in two files that are separated by ; (FS=";"), if a field $n from file 1 is present in any location $m in file 2, where m,n is not 1, then append $0(file2,m) to $0(file1,n). Duplicate fields must be avoided.

I'll attempt to sketch a solution

awk -f script.awk 2.txt 1.txt

where script is the following:

BEGIN {
    FS=";"
    OFS=";"
}

NR==FNR {
    allRecordsFile2[i++] = $0;
    next;
}

{
    for(r in allRecordsFile2)
    {
         split(allRecordsFile2[r],";",array)
         for(f in array)
         {
             for($2 through $n of file1 currently processed)
             {
                 if $n == f --> $0 = $0";"allRecordsFile2[r]
             }
         }
    }

    ## cleanup duplicates

    print $0
}

I still need to hammer out the cleanup duplicates, but probably this can be done by splitting $0 by ";" and using a counting array to keep track of duplicates.

But after running this script I return syntax errors

C:\Program Files (x86)\GnuWin32\bin>awk -f script.awk file2.txt file1.txt

awk: script.awk:17: for($2 through $n of filei currently processed)
awk: script.awk:17: “ syntax error
awk: script.awk:19: if $n == f --> $0 = $0";"allRecordsFile2[r]
awk: script.awk:19: “ syntax error
awk: script.awk:19: if $n == f --> $0 = $0";"allRecordsFile2[r]
awk: script.awk:19: “ syntax error
errcount:3

syntax error

  • Not 100% on this, but your whole FS=";" OFS=";" seems wrong. It should be IFS=";" and OIFS=$IFS and at the end of the script you should simply set IFS=$OIFS to set the original IFS back again. – Giacomo1968 Nov 22 at 4:30
  • @Giacomo1968 nothing change, it return to me same errors – Ansgar Wiechers Nov 22 at 4:43
  • Yes, I know. But when you set FS=";" and OFS=";" those are two variables that have nothing to do with anything. So you were never setting the IFS value. And the whole reason to set OIFS is to reset the IFS value after the script is done. – Giacomo1968 Nov 22 at 5:00
  • 1
    @Giacomo1968+ that's quite wrong. FS (input) and OFS (output) are the correct variables in awk. IFS is used only in shell and this is not a shell script. (And even in shell you only need to set IFS back if the script is 'sourced' rather than run in a subshell.) – dave_thompson_085 Nov 22 at 5:21
  • 1
    This question is more appropriate for stack overflow. – glenn jackman Nov 22 at 6:37
2

Rather than trying sequential reads and controlling based on FNR/NR, why not use getline to read from 2.txt and split on ';' and then build the output string (o below) concatenating unique components from each line? You could do something similar to:

awk '{
        printf "%s", $0
    }
    /^BB/ {
        o = ""
        while (getline tmp < "2.txt") {
            n = split (tmp,arr,";")
            for (i=1; i<=n; i++)
                if(!match($0,arr[i]) && !match(o,arr[i]))
                    o=o arr[i]";"
        }
        printf "%s", o
    }
    {
        print ""
    }
' 1.txt

Example Use/Output

With your example data in 1.txt and 2.txt (which you have misnamed 1.txt again), you would receive:

$ awk '{
>         printf "%s", $0
>     }
>     /^BB/ {
>         o = ""
>         while (getline tmp < "2.txt") {
>             n = split (tmp,arr,";")
>             for (i=1; i<=n; i++)
>                 if(!match($0,arr[i]) && !match(o,arr[i]))
>                     o=o arr[i]";"
>         }
>         printf "%s", o
>     }
>     {
>         print ""
>     }
> ' 1.txt
AA;00000;
BB;11111;KK;WW;55555;FF;ZZ;RR;YY;
GG;22222;

Which looks like what you want.


As A Script Taking Two-Filenames As Arguments

Windows should follow the same conventions using ARGV. Note, you do not include the single quotations around the rules when running within an awk script, e.g.

#!/usr/bin/awk -f 

NR != FNR {
    exit
}
{
    printf "%s", $0
}
/^BB/ {
    o = ""
    while (getline tmp < ARGV[2]) {
        n = split (tmp,arr,";")
        for (i=1; i<=n; i++)
            if(!match($0,arr[i]) && !match(o,arr[i]))
                o=o arr[i]";"
    }
    printf "%s", o
}
{
    print ""
}

(note: you will need to change the /usr/bin/awk interpreter to whatever you have)

Usage would be, e.g. ./test.awk 1.txt 2.txt

Let me know if that helps.

| improve this answer | |
  • C:\Program Files (x86)\GnuWin32\bin>awk -f script.awk 2.txt 1.txt awk: script.awk:1: awk -F';' '{ awk: script.awk:1: ^ invalid char ''' in expression , i.imgur.com/EjF4HwT.png – Ansgar Wiechers Nov 22 at 5:14
  • Oh, you don't include the quotes when you create a script. Those are not needed. Also, you can completely omit the -F';' the change of FS isn't needed the way I have the script working (that was from an earlier test) – David C. Rankin Nov 22 at 5:53
  • David, your windows script works but is not perfect. I try to explain why. If my 1.txt file have this row ;d(2);http://a.o/f/i.p?t=1; and into 2.txt I have this row ;Link;A_x-y.7z;http://a.o/f/i.p?t=1;d(2); I can't generate a 3.txt file with row ;d(2);http://a.o/f/i.p?t=1;Link;A_x-y.7z; – Ansgar Wiechers Nov 22 at 8:17
  • you can think line in 1.txt ;d(2);http://a.o/f/i.p?t=1; as ;AA;BB;, and you can think line in 2.txt ;Link;A_x-y.7z;http://a.o/f/i.p?t=1;d(2); as ;CC;DD;BB;AA;. 3.txt line output you can consider it as ;AA;BB;;CC;DD; – Ansgar Wiechers Nov 22 at 8:17
  • Hmm, I'll have to take a look in the morning (very late here... braindead). I suspect it has to do with the (...) being taken as a REGEX grouping in match() as the first parameter is a REGEX and by passing $0 and o both will be treated as "Dynamic Regular Expressions* in awk speak. See GNU Awk User's Guide - 3.6 Using Dynamic Regexps – David C. Rankin Nov 22 at 8:31
2

Using the keys of an associative array is handy to handle duplicate items. This requires GNU awk for the multi-dimensional array

BEGIN { FS = OFS = ";" }
NR == FNR {
    for (i=1; i<NF-1; i++)
        f2[$(NF-1)][$i] = ++n
    next
}
FNR == 1 {
    # this joins all the 2nd-level indices
    # the order of them is undefined.
    for (x in f2) {
        s = ""
        for (y in f2[x])
            s = s y OFS
        a[x] = s
    }
}
$(NF - 1) in a { $NF = a[$(NF-1)] }
1

then

gawk -f script.awk {2,1}.txt

produces

AA;00000;
BB;11111;55555;WW;KK;RR;YY;FF;ZZ;
GG;22222;

I'll need more evidence that it "doesn't work" with URLs:

$ cat 1.txt
AA;http://a.o/f/i.p?t=00000;
BB;http://a.o/f/i.p?t=11111;
GG;http://a.o/f/i.p?t=22222;

$ cat 2.txt
KK;WW;55555;http://a.o/f/i.p?t=11111;
KK;FF;ZZ;http://a.o/f/i.p?t=11111;
KK;RR;YY;http://a.o/f/i.p?t=11111;

$ gawk -f script.awk {2,1}.txt
AA;http://a.o/f/i.p?t=00000;
BB;http://a.o/f/i.p?t=11111;55555;WW;KK;RR;YY;FF;ZZ;
GG;http://a.o/f/i.p?t=22222;
| improve this answer | |
  • If I replace 00000 between ; separators with text data like http://a.o/f/i.p?t=1 still between separators ; script doesn't work. – Ansgar Wiechers Nov 22 at 8:22
  • I'm assuming that the join field is NF-1,is that right? Your sample input and output suggests that. – glenn jackman Nov 22 at 14:30
  • yes, it is, Problem is about text data in field, because if text is AA it works but if text is http://a.o/f/i.p?t=1 it doesn't – Ansgar Wiechers Nov 22 at 14:37
  • syntax errors for line 4 and 12, i.imgur.com/za60qAf.png – Ansgar Wiechers Nov 22 at 15:47
  • I did say this solution requires GNU awk. – glenn jackman Nov 22 at 20:30

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.