Merge Two files on the basis of 2 columns at diffrent position

Shell Programming and Scripting
1

Hello,

I am trying to merge two files with multiple records having a common columns but on first file its on 7th column and on 2nd file it on 6th column.

First file(file1.txt) - 7th Column is unique

H|123|Alex|Ren|W|9856746|2345789|20152202|
H|97654|Ray|John||9855678|2345790|20152201|
H|91|Roj|John||9851234|2345791|20152200|

Second File(file2.txt) - 3rd column is unique and matching first file 7th column

L|23|2345789|92311|T|76|
L|24|2345789|92312|T|76|
L|25|2345789|92313|T|76|
L|91|2345790|91111|T|90|
L|101|2345791|93311|W|86|

So the output should be like this -

H|123|Alex|Ren|W|9856746|2345789|20152202|
L|23|2345789|92311|T|76|
L|24|2345789|92312|T|76|
L|25|2345789|92313|T|76|
H|97654|Ray|John||9855678|2345790|20152201|
L|25|2345789|92313|T|76|
H|91|Roj|John||9851234|2345791|20152200|
L|101|2345791|93311|W|86|

I tried using awk to swap the file 1, 7th Coumn to 3rd Column using

awk -F'|' '{t=$7 ; $7=$3 ; $3=t; print;}' OFS='|' file1.txt > formatted_file1.txt

Now I used sort using key on both the 3rd column to merge but do not have any clue to move back column 3rd to column 7th in merge file for all the records starting with "H".

Please advice or let me know something easy way to get the output.

Thank You.

2

Hi,
Can you explain how to obtain sort in red ?

H|123|Alex|Ren|W|9856746|2345789|20152202|
L|23|2345789|92311|T|76|
L|24|2345789|92312|T|76|
L|25|2345789|92313|T|76|
H|97654|Ray|John||9855678|2345790|20152201|
L|25|2345789|92313|T|76|
H|91|Roj|John||9851234|2345791|20152200|
L|101|2345791|93311|W|86|

Regards.

3

Do you mean the third column not the 6th column?

In the second file the 3rd column is not unique.
They seem to be sorted in order?
If so, and if the first file the 7th column is truly unique you could try this

awk -F\| 'NR==FNR{A[$7]=$0; next} $3 in A{print A[$3]; delete A[$3]}1' file1.txt file2.txt

H|123|Alex|Ren|W|9856746|2345789|20152202|
L|23|2345789|92311|T|76|
L|24|2345789|92312|T|76|
L|25|2345789|92313|T|76|
H|97654|Ray|John||9855678|2345790|20152201|
L|91|2345790|91111|T|90|
H|91|Roj|John||9851234|2345791|20152200|
L|101|2345791|93311|W|86|
4

Hi disedorgue,

I swaped the column 7 in first file from column 3 and now its easy to sort the file.

sort -t\| -k3,3 file1.txt file2.txt > merge_file.txt

But here problem is that when I creates a merge_file.txt. The file 1 column is already swaped so I need to change it position back to normal in merge_file.txt. Means the records starts with "H|" need to be changed - Again swapping required to change its position back to original.

Hi Scrutinizer,

Your code gives only the file2.txt results. I am not getting the desired output for multiple records.

5

I used you input samples and got the result in post #3

What did you get? What is your OS and version?

6

Sorry Scrutinizer, You code is working. Thank you so much!!

7

Try also

awk 'FNR==NR {$1=$7 "|" $1;print;next} {$1=$3 "|" $1} 1' FS="|" OFS="|"  file[34] | sort -t\| -k1,1 | cut -d\| -f2-
H|123|Alex|Ren|W|9856746|2345789|20152202|
L|23|2345789|92311|T|76|
L|24|2345789|92312|T|76|
L|25|2345789|92313|T|76|
H|97654|Ray|John||9855678|2345790|20152201|
L|91|2345790|91111|T|90|
H|91|Roj|John||9851234|2345791|20152200|
L|101|2345791|93311|W|86|