Shell Programming and Scripting

:::::::::
::FileA::
:::::::::
A1-------A2--------A3---A4---A5--
=================================
AC5VXVLT-XX----------------------
B57E434--XXXX1-----MMMM-ZZZ--111-
C325G20--XXXXX3----CCCC------3332
DC35S51--XXXXY1----DDDD------44X-
DC35S52--XXXXY2----DDDD------44Y-
DC35S53--XXXXY3----DDDD------44Z-
EDMDAPP--XX----------------------
FHSMJDP--XX----------------------
GFH2543--XXXXX6----LLLL------666-
I278997--XXXXXY1---HHHH------88X-
J278998--XXXXXY2---HHHH------88Y-
KIXH5DM--XXXXXX9-----------------
NKK7021--XXXXX12-------------112-
OC30H0D--XXXXXXX13-FFFF-YYY--1134
PKD30571-XXX14-----AAAA------114-
=================================

:::::::::
::FileB::
:::::::::
B1------B2-------------------B3--------B4--------B5----------
=============================================================
AAAA----+NDNXN.XNYMT---------88/29J----NX/927----00970-------
GGGGGG1-+NDNXX.TUDTON------------------TX/088----555.555.7901
CCCC----+NDMNNNZ.XNNX--------77/190--------------99178-------
DDDD----+NDMMN.BMXXMNX-------27/98B----NX/778----93010-------
EEEEEEE1+NDMMN.OONTNOT-----------------NX/T80----10238-------
FFFF----+NDRMN.MNTTNY--------08/000----DN/088----555.8180----
JJJJ----+NDRMNN.ONTTNXXON----J0/MNO----NX/798----555.555.2797
HHHH----+NDRMNN.JNOKTON------39/870--------------------------
IIII----+NDRMNN.XNYRNNON-----08/98N----TX/088----------------
BBBB----+NDRMNN.MOOXNRKXMN---72/08K----DN/088----555.555.9270
MMMM----+NDRMNN.YMXXMNMT-----27/992--------------97913-------
LLLL----+NDRMNNN.NRD-------------------OJ/880----------------
=============================================================

:::::::::::::::
::Merged File::
:::::::::::::::
A1--------A3----B2----------------B3------B4------B5------
==========================================================
B57E434---MMMM--+NDRMNN.YMXXMNMT--27/992----------97911---
C325G20---CCCC--+NDMNNNZ.XNNX-----77/190----------99178---
DC35S51---DDDD--+NDMMN.BMXXMNX----27/98B--NX/778--99010---
DC35S52---DDDD--+NDMMN.BMXXMNX----27/98B--NX/778--99010---
DC35S53---DDDD--+NDMMN.BMXXMNX----27/98B--NX/778--99010---
GFH2543---LLLL--+NDRMNNN.NRD--------------OJ/880----------
I278997---HHHH--+NDRMNN.JNOKTON---39/870------------------
J278998---HHHH--+NDRMNN.JNOKTON---39/870------------------
OC30H0D---FFFF--+NDRMN.MNTTNY-----08/000--DN/088--555.8180
PKD30571--AAAA--+NDNXN.XNYMT------88/29J--NX/927--00970---
==========================================================

Notes:
-The files do not have any actual dashes. They are there so that the tables aren't smashed together for the posting. Just ignore them if at all possible.
-FileA is sorted by A1.
-FileB is not sorted.
-A1 is unique, but multiple A1's can have the same A3.
-A3 is not required.
-B1 is unique.
-B2 field starts with a "+".

Requirements:
-The merged output file should be joined on columns A3 and B1.
-FileA is the master and should only match to FileB if A3 is present. (aka eliminate empty A3 lines from FileA)

I have tried mutiple avenues for this one: sed, awk, join, paste. Frankly, my head is starting to hurt looking at the crazy awk's and sed's out there. How should this be handled? Multiple steps? I am pretty convinced that there isn't going to be long magical sed/awk that brings this together for me. I keep getting stumped in the way these commands recognize columns(fields). For example, if B3 is missing, it seems to think B4 is actually $field3 for that line. Does this mean that I need to reference columns by bytes instead?

Anybody got any ideas on how to attack this?

try this:

Code:

nawk 'FNR==NR{fA[$3]=$1;next} $1 in fA {print fA[$1], $0}' FileA FileB

Wow that works great. Looks like I need to read up some more on nawk.

One thing though. This is eliminating the rows from FileA that have the same A3 value but the unique A1. In other words, the merged file should contain 1 row for every A1 field that contains an A3 (no matter if its the same as the one above it). This is eliminating the first 2 lines that have a "DDDD" in FileA. There needs to be 3 lines that have a "DDDD" in the merged output since FileA had 3 different A1's with the same A3. "HHHH" is the same way.

How would you alter this to allow those scenarios to be caught?


:::::::::
::FileA::
:::::::::
A1-------A2--------A3---A4---A5--
=================================
DC35S51--XXXXY1----DDDD------44X-
DC35S52--XXXXY2----DDDD------44Y-
DC35S53--XXXXY3----DDDD------44Z-
=================================

:::::::::
::FileB::
:::::::::
B1------B2-------------------B3--------B4--------B5----------
=============================================================
DDDD----+NDMMN.BMXXMNX-------27/98B----NX/778----93010-------
=============================================================

It should come out to this:

:::::::::::::::
::Merged File::
:::::::::::::::
A1--------A3----B2----------------B3------B4------B5------
==========================================================
DC35S51---DDDD--+NDMMN.BMXXMNX----27/98B--NX/778--93010---
DC35S52---DDDD--+NDMMN.BMXXMNX----27/98B--NX/778--93010---
DC35S53---DDDD--+NDMMN.BMXXMNX----27/98B--NX/778--93010---
==========================================================

But instead its coming out like this:

A1--------A3----B2----------------B3------B4------B5------
==========================================================
DC35S53---DDDD--+NDMMN.BMXXMNX----27/98B--NX/778--93010---
==========================================================

eliminating the DC35S51 & DC35S52.

try this - not tested:

Code:

nawk 'FNR==NR{fA[$3]=($3 in fA)?fA[$3] FS $1:$1;next} $1 in fA {n=split(fA[$1],t, FS); for(i=1;i<=n;i++) print t[i], $0}' FileA FileB

Most excellent!

That works. Thanks!

you're welcome.
In the future, please use the code tags when quoting code/data samples.