finding a field's max index from array of hashes without loop

[1DMF]

Hi,

I have an array of hashes (recordset)

I have formatted the address field to be CSV.

However, as i'm genearating an XLS output file, I need to know the max number of 'columns' spliting out the address field will produce so I can create the correct number of column headings i.e. Add1,Add2,Add3 etc..

And each row supply the correct number of CSV's.

So a record where there is only 4 address lines, but the max is six address lines everything is formatted correctly.

Now i currently loop the array of hashes to format the addresss field and perform other data formating and generate the final output string like so...

    # set Vars
    my $vars = "Sub_Date,Regulated,Members.CompanyName,Members.FirstName + ' ' + Members.LastName AS Adviser,CNames,Address,Postcode...truncated!"; 

my $xls = "Date app received,Regulated,AR Company,Adviser,Client name(s),Property Address,Postcode...truncated!\n";   

my @head = split(/\,/,$vars);
my $time = time;

    foreach my $row (@nbcs){

        #format address
        if($row->{'Address'}){
            $row->{'Address'} =~ s/(\s*\r\n)+/\r\n/g;        
            $row->{'Address'} =~ s/\s*,*\s*(\r\n)/,/g; 
            $row->{'Address'} =~ s/,$//g;            
        }

        #format Sub Date
        if($row->{'Sub_Date'}){
            $row->{'Sub_Date'} = substr($row->{'Sub_Date'},0,10);
            my @dte = split(/-/,$row->{'Sub_Date'});
            $row->{'Sub_Date'} = "$dte[2]/$dte[1]/$dte[0]";
        }

        #format Comp Date
        if($row->{'Comp_Date'}){
            $row->{'Comp_Date'} = substr($row->{'Comp_Date'},0,10);
            my @dte = split(/-/,$row->{'Comp_Date'});
            $row->{'Comp_Date'} = "$dte[2]/$dte[1]/$dte[0]";
        }

        #format Offer Date
        if($row->{'Offer_Date'}){
            $row->{'Offer_Date'} = substr($row->{'Offer_Date'},0,10);
            my @dte = split(/-/,$row->{'Offer_Date'});
            $row->{'Offer_Date'} = "$dte[2]/$dte[1]/$dte[0]";
        }

        #format DOB1
        if($row->{'DOB1'}){
            $row->{'DOB1'} = substr($row->{'DOB1'},0,10);
            my @dte = split(/-/,$row->{'DOB1'});
            $row->{'DOB1'} = "$dte[2]/$dte[1]/$dte[0]";
        }

        #format DOB2
        if($row->{'DOB2'}){
            $row->{'DOB2'} = substr($row->{'DOB2'},0,10);
            my @dte = split(/-/,$row->{'DOB2'});
            $row->{'DOB2'} = "$dte[2]/$dte[1]/$dte[0]";
        }

        #format NPW Date
        if($row->{'NPW_Date'}){
            $row->{'NPW_Date'} = substr($row->{'NPW_Date'},0,10);
            my @dte = split(/-/,$row->{'NPW_Date'});
            $row->{'NPW_Date'} = "$dte[2]/$dte[1]/$dte[0]";
        }

        #format Review Date
        if($row->{'Review_Date'}){
            $row->{'Review_Date'} = substr($row->{'Review_Date'},0,10);
            my @dte = split(/-/,$row->{'Review_Date'});
            $row->{'Review_Date'} = "$dte[2]/$dte[1]/$dte[0]";
        }

        #format Init Period
        if($row->{'Init_Period'}){
            $row->{'Init_Period'} = substr($row->{'Init_Period'},0,10);
            my @dte = split(/-/,$row->{'Init_Period'});
            $row->{'Init_Period'} = "$dte[2]/$dte[1]/$dte[0]";
        }

        #format Diary Date
        if($row->{'Diary_Date'}){
            $row->{'Diary_Date'} = substr($row->{'Diary_Date'},0,10);
            my @dte = split(/-/,$row->{'Diary_Date'});
            $row->{'Diary_Date'} = "$dte[2]/$dte[1]/$dte[0]";
        }

        #format commission
        if($row->{'Commission'}){
            $row->{'Commission'} = currency_format('gbp', $row->{'Commission'}, FMT_SYMBOL);  
            $row->{'Commission'} =~ s/£//;
        }

        #format Client Fee
        if($row->{'Client_Fee'}){
            $row->{'Client_Fee'} = currency_format('gbp', $row->{'Client_Fee'}, FMT_SYMBOL);  
            $row->{'Client_Fee'} =~ s/£//;
        }

        #format Amount
        if($row->{'Amount'}){
            $row->{'Amount'} = currency_format('gbp', $row->{'Amount'}, FMT_SYMBOL);  
            $row->{'Amount'} =~ s/£//;
        }

        #AR_Gross
        if($row->{'AR_Gross'}){
            $row->{'AR_Gross'} = currency_format('gbp', $row->{'AR_Gross'}, FMT_SYMBOL);  
            $row->{'AR_Gross'} =~ s/£//;
        }

        #Property Value
        if($row->{'Property_Val'}){
            $row->{'Property_Val'} = currency_format('gbp', $row->{'Property_Val'}, FMT_SYMBOL);  
            $row->{'Property_Val'} =~ s/£//;
        }

       $xls.='"'.join('","',map {$row->{$_} ? $row->{$_} : ''}@head).'"'."\n";

    }

So now i'm thinking I need to loop and just change address, then loop again spliting out address and recording index.

then replacing the column header with the correct number of max address fields

$xls =~ s/Property Address/Add1\,Add2\,Add3\,Add4\,Add5/gi;

Then loop the entire array of hashes for a third time to generate the correct CSV data with the join command.

Looping the record set three times, seem to me like a waste of time and processing resource, can someone advise on a better solution?

many thanks,
1DMF




"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!

 

[1DMF]

For the time being, I've decided it's unlikely there is ever more than 6 address lines (not including Postcode).

So hard coded it...

$vars =~ s/Address/Add1,Add2,Add3,Add4,Add5,Add6/g;        

    # set Vars
    my @head = split(/\,/,$vars);
    my $time = time;

    foreach my $row (@nbcs){

        #format address to CSV
        if($row->{'Address'}){
            $row->{'Address'} =~ s/(\s*\r\n)+/\r\n/g;        
            $row->{'Address'} =~ s/\s*,*\s*(\r\n)/,/g; 
            $row->{'Address'} =~ s/,$//g;            
        }
        else{
            $row->{'Address'} = "";
        }

        #split address & assign to columns
        my @addy = split(/,/,$row->{'Address'});

        for (my $i = 1; $i <= 6; $i++){
            if(!$addy[$i-1]){
                $row->{'Add'.$i} = "";
            }
            else{
                $row->{'Add'.$i} = $addy[$i-1];
            }            
        }   
       $xls.='"'.join('","',map {$row->{$_} ? $row->{$_} : ''}@head).'"'."\n";

    }

So after @nbcs has grabed the SQL recordset using table column address, I replace it with Add1,Add2 etc.. columns.

format the address acordingly and then place each row back into the recordset with its relevant Add? hash key before it is then joined to create the XLS output.

OK not as fancy as a dynamic version, but at least I only have to loop the record set the once!

"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!