3

I want to merge two files.

Files A.txt

001;abc;def;ghi;jkl;pqr
002;abc;def;ghi;jkl;pqr
003;abc;def;ghi;jkl;pqr
004;abc;def;ghi;jkl;pqr
005;abc;def;ghi;jkl;pqr

. Second File B.txt

001;mno
002;mno
003;mno
004;mno
005;mno

to have a text file C.txt

001;abc;def;ghi;jkl;mno;pqr

I am able to merge these two files but I don't know how to insert the output from file B mno before pqr.

  • Does this have to be done with awk? I know you tagged it as such but there are simpler ways. – slm Mar 13 '14 at 21:08
  • 1
    Related: unix.stackexchange.com/questions/86849/…. I'm very tempted to mark this Q as a duplicate, there are literally dozens of examples on the site that show how to do this. – slm Mar 13 '14 at 21:49
1

join will print each line of sorted input files that have the same first *(by default) field. So, setting the field delimiter (-t) to ; you get:

$ join -t\; A.txt B.txt 
001;abc;def;ghi;jkl;pqr;mno
002;abc;def;ghi;jkl;pqr;mno
003;abc;def;ghi;jkl;pqr;mno
004;abc;def;ghi;jkl;pqr;mno
005;abc;def;ghi;jkl;pqr;mno

Combining that with awk to switch the field positions around:

$ join -t\; A.txt B.txt | 
    awk -F';' -v OFS=';' '{k=$NF; $NF=$(NF-1); $(NF-1)=k; print;}'
001;abc;def;ghi;jkl;mno;pqr
002;abc;def;ghi;jkl;mno;pqr
003;abc;def;ghi;jkl;mno;pqr
004;abc;def;ghi;jkl;mno;pqr
005;abc;def;ghi;jkl;mno;pqr
| improve this answer | |
  • 1
    You can tell join to print the fields in a different order to begin with. That won't work for variable numbers of fields, of course, but it saves a process if it's a predictable file. – Kevin Mar 14 '14 at 18:54
6

You can use join

join -t\; -o0,1.2,1.3,1.4,1.5,2.2,1.6 a.txt b.txt

-t\; uses semicolons as a field separator, and the -o list tells it which fields to print in what order. This will not print lines in either file with no match in the other; to get that you can add -a1 (for the first file) or -a2 (for the second).

| improve this answer | |
  • Kevin awsome never thought of doing it in such an easy way.Thanks – user62687 Mar 13 '14 at 22:58
2

Try this:

awk -F';' 'FNR==NR{a[$1]=$NF;next}{
    for(i=1;i<=NF;i++){
        if($i<a[$1]){
            printf("%s;",$i)
        } else {
            printf("%s;%s",a[$1],$i)
        }
    }
    print ""}' B.txt A.txt

Output:

001;abc;def;ghi;jkl;mno;pqr
002;abc;def;ghi;jkl;mno;pqr
003;abc;def;ghi;jkl;mno;pqr
004;abc;def;ghi;jkl;mno;pqr
005;abc;def;ghi;jkl;mno;pqr
| improve this answer | |
  • @user62687 - if this A solved your problem would you kindly mark it as the accepted A so other users of the site know your issue's been resolved? thanks. – slm Mar 14 '14 at 8:22
1

Here is a shorter awk:

awk 'BEGIN{FS=OFS=";"}NR==FNR{a[$1]=$2;next}$NF=a[$1]FS$NF' fileb filea

$ cat filea
001;abc;def;ghi;jkl;pqr
002;abc;def;ghi;jkl;pqr
003;abc;def;ghi;jkl;pqr
004;abc;def;ghi;jkl;pqr
005;abc;def;ghi;jkl;pqr

$ cat fileb     # Intentionally modified fields to show they map correctly
001;mno
002;nno
003;qno
004;fno
005;sno

$ awk 'BEGIN{FS=OFS=";"}NR==FNR{a[$1]=$2;next}$NF=a[$1]FS$NF' fileb filea
001;abc;def;ghi;jkl;mno;pqr
002;abc;def;ghi;jkl;nno;pqr
003;abc;def;ghi;jkl;qno;pqr
004;abc;def;ghi;jkl;fno;pqr
005;abc;def;ghi;jkl;sno;pqr
| improve this answer | |
1

So this is what I used to get the source material:

cat <<F1 >/tmp/f1 ; cat <<F2 >/tmp/f2
$(for i in 1 2 3 4 5 ; do { \
    printf "00%s" $i ; printf ";%s" \
        abc def ghi jkl pqr ; echo ; } ; done)
 F1
 $(for i in 1 2 3 4 5 ; do {\ 
    printf "00%s" $i ; echo ";mno" ; } ; done)
 F2

It supplies:

001;abc;def;ghi;jkl;pqr
002;abc;def;ghi;jkl;pqr
003;abc;def;ghi;jkl;pqr
004;abc;def;ghi;jkl;pqr
005;abc;def;ghi;jkl;pqr
001;mno
002;mno
003;mno
004;mno
005;mno

I tested this in various ways, and this is the resulting command:

% sed -e 'R /tmp/f2' /tmp/f1 |\
    sed -r 'N;s/(.*)(;[^;]*)\n[^;]*(.*)/\1\3\2/'

This is GNU sed only - because GNU offers the R function we can read in a separate file line-by-line in lockstep with our input. This means no branching and no looping. In this way, I expect sed would work more efficiently than awk because it wouldn't have to read the contents entirely into memory before operating and it can operate on a live stream.

I attempted to make this work without the |pipe and second sed invocation, but because sed appends /tmp/f2 to its own stdout nothing I tried allowed me to edit in stream without the |pipe. sed first collates the two files in stream and edits the result on the other end of the |pipe.

Anyway, one |pipe though and it's still streamed, but you need two seds . Run the above sed command on your data and:

OUTPUT
> 001;abc;def;ghi;jkl;mno;pqr
> 002;abc;def;ghi;jkl;mno;pqr
> 003;abc;def;ghi;jkl;mno;pqr
> 004;abc;def;ghi;jkl;mno;pqr
> 005;abc;def;ghi;jkl;mno;pqr

Here's how it works:

sed -r 'N;s/(.*)(;[^;]*)\n[^;]*(.*)/\1\3\2/'
  • N because we already know that sed is appending each successive line from f2 to those in f1 the very first thing we do after receiving a line is to pull in the Next one.
  • s having joined the two lines we need in pattern space, we begin the search and replace function

    001;abc;def;ghi;jkl;pqr\n001;mno

  • \1 (.*) first tell sed to ( group * everything it ) finds from the left-most portion of the pattern space into the \1 back-reference until...

    001;abc;def;ghi;jkl*;pqr*\n001;mno

  • \2 (;[^;]*)\n it encounters a string consisting of a...

    • ; semicolon then ...
    • [^;]* a string consisting entirely of ^no ;semicolons immediately followed by
    • \n the \newline character added when we pulled in f2's line with N and which will be discarded
    • () In this way we back reference the ( last semicolon delimited field ) from the line in f1 to \2
  • [^;]* beginning with f2's line we search through and discard all characters until we encounter a semicolon, and

    001;abc;def;ghi;jkl;pqr\n001*;mno*

  • \3 (.*) we store everything that remains in the \3 backreference

  • \1\3\2 once we've split out the string as we require all that remains is to put it back together in the right order, so we insert \3 before \2 and we're through with this search replace cycle until we're fed a new line

| improve this answer | |

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.