Merging Two Files columns in order

Question

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.

Answer 1

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).

Answer 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

Answer 3

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

Answer 4

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

Answer 5

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