title: Merge files with awk
body: <p>I am attempting to merge <code>file1</code> and <code>file2</code> based on the number in column2 from <code>file2</code> and the number in column1 from <code>file1</code> delimited mainly by a comma. There is also a <code>.</code> delimiter between the number I am trying to match in <code>file2</code></p>
  <p>Here is file 1.</p>
  <pre><code>1,Mary,24 Fuller Rd
  2,Fred,19 St Johns
  3,Jonathan,8 Poplar Drive
  4,Susan,116 Shepherds Way
  5,Michael,4 Nerthern Court
  </code></pre>
  <p>and file 2</p>
  <pre><code>Dawning,Order.5.DHL
  Hawkins,Order.3.FedEx
  Jacob,Order.2.Yodel
  Plateu,Order.4.DPD
  Martins,Order.1.Hermes
  </code></pre>
  <p>My approach is to extract the key from <code>file2</code> with <code>split</code>. As a single file, this works, but when working with multiple files, the behaviour is weird and not the expected results.</p>
  <pre><code>awk -F, '{{split($2,i,&quot;.&quot;)}{ print i[2]}' file2
  5
  3
  2
  4
  1
  </code></pre>
  <pre><code>awk -F, 'NR==FNR{split($2,i,&quot;.&quot;); next}{ print i[2]}' file2 file1
  1
  1
  1
  1
  1
  </code></pre>
  <p>I only get the expected result if I remove <code>split</code> but then have no way of extracting the match.</p>
  <pre><code>awk -F, 'NR==FNR{array[$2]}END{ for (i in array) print i}' file2 file1
  Order.4.DPD
  Order.3.FedEx
  Order.1.Hermes
  Order.5.DHL
  Order.2.Yodel
  </code></pre>
  <p>There are many other steps i have taken and failed at but it may make the question overly bloated so if more information regarding this is needed, please ask.</p>
  <p>My expected result would look like this</p>
  <pre><code>Mary Martins 24 Fuller Rd
  Fred Jacob 19 St Johns
  Jonathan Hawkins 8 Poplar Drive
  Susan Plateu 116 Shepherds Way
  Michael Dawning 4 Nerthern Court
  </code></pre>
  <p>Where column2 from <code>file2</code> and column1 in <code>file1</code> match based on the number therefore printing <code>$2</code> and <code>$NF</code> from <code>file1</code> and <code>$1</code> from <code>file2</code></p>
  <p>Here are a few of my failed attempts amongst many</p>
  <pre><code>awk -F, 'NR==FNR {M=$1; array[$2]; next}{($1 in array)}END{ for (i in array) print $2, M, $NF}' file2 file1
  awk -F, 'NR==FNR {M=$1; array[$2]; for (i in array) split(i,a,&quot;.&quot;); next} $1==a[2]{print $2,M, $3}' file2 file1
  awk -F, 'NR==FNR {M=$1; array[$2]; next}END { for (i in array) split(i,a,&quot;.&quot;)}($1~a[2]){ print $2,M}' file2 file1
  </code></pre>
  <p>I have included the <code>perl</code> tag as I'd be interested in solutions with <code>perl</code> but primarily want to do this with <code>awk</code> if possible.</p>
  <p>Thank you.</p>