It strikes me as odd that you split on comma (,) but in your data the delimiter is the pipe (|) symbol. Maybe it would speed up things to use that instead.

Otherwise, since your wording is SQL-speech, you might feel more at home using actual SQL on your CSV text files -- if that is what you prefer -- and go for DBD::CSV (and DBI)

I for myself have been happiest with the modernised Text CSV approach found in Text::CSV_XS or Text::CSV.

Cheers, Sören

'outer' join is perhaps not the best term to use; what you describe is a full join (or full outer join; but the word 'outer' is redundant).

The easiest way is probably indeed with DBI/DBD-CSV but I wanted to run this through postgres using foreign data wrapper 'file_fdw':

cat pm-1123847.sh
#!/bin/sh
#
# File_Deal - the key here is parent_cusp,deal,tranche
#
echo "parent_cusp|cusp|isin|deal|tranche|det_date|col_type
38375U|36182D|36182D1|HMAG|HMBSWEE|20150416|mortgage
383333|361333|3618333|HABS|HABSDDE|20150330|mortgage2" > /tmp/file_dea
+l.txt

#
# File_ATT - the key here is Vendor, deal, tranche 
#
echo "Vendor|visp|barnembly|deal|tranche|Fund|subamt|colamt|basamt
38375U|3DD82D|36FF333|HMAG|HMBSWEE|9010|765423|364633|46566
38EE33|361DD3|36LLE33|H99S|HAOOODE|2330|377233|347433|34488" > /tmp/fi
+le_att.txt

echo "
drop foreign table if exists t1 cascade;
drop foreign table if exists t2 cascade;
drop server        if exists s  cascade;
" | psql -X

echo "
create server s foreign data wrapper file_fdw;

create foreign table t1 (
    parent_cusp text,
    cusp text,
    isin text,
    deal text,
    tranche text,
    det_date text,
    col_type text
) server s
options (filename '/tmp/file_deal.txt', format 'csv', delimiter E'|', 
+header 'true');

create foreign table t2 (
    vendor text,
    visp text,
    barnembly text,
    deal text,
    tranche text,
    fund text,
    subamt text,
    colamt text,
    basamt text
) server s
options (filename '/tmp/file_att.txt', format 'csv', delimiter E'|', h
+eader 'true');

select * from t1;

select * from t2;

select 
    coalesce(t1.parent_cusp, t2.vendor) as parent_cusp
  , t1.cusp
  , t1.isin
  , coalesce(t1.deal, t2.deal)          as deal
  , coalesce(t1.tranche, t2.tranche)    as tranche
  , t1.det_date
  , t1.col_type
  , t2.fund
  , t2.subamt
  , t2.colamt
  , t2.basamt

from t1 full join t2 
  on (t1.parent_cusp, t1.deal, t1.tranche) =
     (t2.vendor, t2.deal, t2.tranche)
;
" | psql -X 

echo "File_Output

parent_cusp|cusp|isin|deal|tranche|det_date|col_type|Fund|subamt|colam
+t|basamt
38375U|36182D|36182D1|HMAG|HMBSWEE|20150416|mortgage|9010|765423|36463
+3|46566
383333|361333|3618333|HABS|HABSDDE|20150330|mortgage2||||
38EE33|||H99S|HAOOODE|||2330|377233|347433|34488
"
[download]

$ ./pm-1123847.sh
DROP FOREIGN TABLE
DROP FOREIGN TABLE
DROP SERVER
CREATE SERVER
CREATE FOREIGN TABLE
CREATE FOREIGN TABLE
 parent_cusp |  cusp  |  isin   | deal | tranche | det_date | col_type
-------------+--------+---------+------+---------+----------+---------
+--
 38375U      | 36182D | 36182D1 | HMAG | HMBSWEE | 20150416 | mortgage
 383333      | 361333 | 3618333 | HABS | HABSDDE | 20150330 | mortgage
+2
(2 rows)

 vendor |  visp  | barnembly | deal | tranche | fund | subamt | colamt
+ | basamt
--------+--------+-----------+------+---------+------+--------+-------
+-+--------
 38375U | 3DD82D | 36FF333   | HMAG | HMBSWEE | 9010 | 765423 | 364633
+ | 46566
 38EE33 | 361DD3 | 36LLE33   | H99S | HAOOODE | 2330 | 377233 | 347433
+ | 34488
(2 rows)

 parent_cusp |  cusp  |  isin   | deal | tranche | det_date | col_type
+  | fund | subamt | colamt | basamt
-------------+--------+---------+------+---------+----------+---------
+--+------+--------+--------+--------
 38375U      | 36182D | 36182D1 | HMAG | HMBSWEE | 20150416 | mortgage
+  | 9010 | 765423 | 364633 | 46566
 383333      | 361333 | 3618333 | HABS | HABSDDE | 20150330 | mortgage
+2 |      |        |        |
 38EE33      |        |         | H99S | HAOOODE |          |         
+  | 2330 | 377233 | 347433 | 34488
(3 rows)

File_Output

parent_cusp|cusp|isin|deal|tranche|det_date|col_type|Fund|subamt|colam
+t|basamt
38375U|36182D|36182D1|HMAG|HMBSWEE|20150416|mortgage|9010|765423|36463
+3|46566
383333|361333|3618333|HABS|HABSDDE|20150330|mortgage2||||
38EE33|||H99S|HAOOODE|||2330|377233|347433|34488
[download]

'Export' is easy with

copy ( <some SQL> ) to '/tmp/file_output.txt'
with (format csv, delimiter '|', header true)
[download]

I've not decided for myself how this compares to a variant with DBI.

I guess I'll go make such a DBI/DBD-CSV version now...

UPDATE: added the actual output file generation; some small text edits.

Could not resist, here is a column index driven approach.

use strict;
use warnings;
use Data::Dumper;

my $data1 = <<EO1;
38375U|36182D|36182D1|HMAG|HMBSWEE|20150416|mortgage
383333|361333|3618333|HABS|HABSDDE|20150330|mortgage2
EO1

my $data2 = <<EO2;
38375U|3DD82D|36FF333|HMAG|HMBSWEE|9010|765423|364633|46566
38EE33|361DD3|36LLE33|H99S|HAOOODE|2330|377233|347433|34488
EO2

sub process_file {
  my ( $file, $keys, $in, $out, $hash ) = @_;
  open my $fh, "<", $file;
  while(<$fh>) {
    chomp;
    my @attr = split /[|]/;
    @{ $hash->{join '|', @attr[@$keys]} }[ @$out ] = @attr[ @$in ];
  }
  close $fh;
}

my %result;
process_file( \$data1, [0,3,4], [0..6], [0..6], \%result );
process_file( \$data2, [0,3,4], [0,3,4,5..8], [0,3,4,7..10], \%result 
+);

print Dumper \%result;
[download]

Dealing with ignoring the header line and printing is left as an exercise...

Thanks very much to all. I used some of your ideas and got it to work

Here is a method, if I understand your wording right, to use header TEXT indexes.

use strict;
use warnings;

my $File_Deal = $ARGV[0];
my $File_ATT = $ARGV[1];

open(F1, "<", $File_Deal) or die $!;

my $header_line;
my @header_array;

# File_Deal
chomp( $header_line = <F1> );
@header_array = split('\|', $header_line );
my %HEADER_File_Deal = map { $header_array[$_] => $_ } (0..@header_arr
+ay-1);


my %hash = ();
while( <F1> ) {
  chomp;
  my @values = split(/\|/, $_);
  # parent_cusp|cusp|isin|deal|tranche|det_date|col_type
  my $key = $values[$HEADER_File_Deal{"parent_cusp"}]
    . $values[$HEADER_File_Deal{"deal"}]
    . $values[$HEADER_File_Deal{"tranche"}];

  print "key=$key is ( @values )\n";
}
[download]

...or not... here is the way to fetch it on index

use strict;
use warnings;

my $File_Deal = $ARGV[0];
my $File_ATT = $ARGV[1];

open(F1, "<", $File_Deal) or die $!;

my $header_line;
my @header_array;

# File_Deal header, throw away
chomp( $header_line = <F1> );


my %hash = ();
while( <F1> ) {
  chomp;
  my @values = split(/\|/, $_);
  # 0=parent_cusp|1=cusp|2=isin|3=deal|4=tranche|5=det_date|6=col_type
  my $key = $values[1-1]
    . $values[4-1]
    . $values[5-1];

  print "key=$key is ( @values )\n";
}
[download]