I have some code that parses a excel file, basically contact info Name, middle, last, address, city, state, and phone number. Right now it works to parse through the phones and to populate zip codes, but I need to be able to populate state based off zip and I need to be able to populate both based off phone area code. Could you guys help me with this? I'm thinking I could create another file or 2 and have my code reference it for zip code reference and area code reference. How would I do that? This is what I have so far.

input from an excel
195 Rama Sabbavarapu 7370 Capri Way Apt 1 Maineville OH 43230 5138237094 DEBT Homeowner
195 Ramin Sayan 4207 Sorren Ct Columbus 3302230000 DEBT Homeowner
195 Ramona Sheehy 1077 Claudia Ave Apt H London 45342 7406044747 DEBT Homeowner
195 Randa Mikaiel 5524 Sagewood Dr Miamisburg oh 9376400007 DEBT Homeowner
<parse should do a few things first, where there's no zip, add the zip based on state, which I accomplished. Step 2 should calculate state based on zip, which will probably have to be done by connecting to my sql, I'm working on that currently, but stumbling due being a perl noob. 3rd I will either have to create a mysql table or another file to reference area codes, so if zip and state are missing it will check the first 3 numbers of the phone to populate those 2. This should populate a log file of whats been changed something I also have struggled with to get right./p> Output:
195 Rama Sabbavarapu 7370 Capri Way Apt 1 Maineville OH 43230 5138237094 DEBT Homeowner
195 Ramin Sayan 4207 Sorren Ct Columbus OH 43230 3302230000 DEBT Homeowner
195 Ramona Sheehy 1077 Claudia Ave Apt H London OH 45342 7406044747 DEBT Homeowner
195 Randa Mikaiel 5524 Sagewood Dr Miamisburg OH 45645 9376400007 DEBT Homeowner
We get some really horrible data lists sometimes, so this would save an exponential amount of time. 
use strict;
    use warnings;
    use Excel::Writer::XLSX;

    my $in_file = "filein.xlsx";
    my $out_file = "fileout.xlsx";

    # Read in all rows from existing file
    my $rows = read_excel($in_file);

        my $AL_zip = '35007';
    my $AK_zip = '99501';

    my $rowcount = 0;
    # Update the rows
    for my $row (@$rows) {
        if($row->[6] eq 'AL' &&  $row->[7]) {
            $row->[7] = $AL_zip;
        }
        elsif(length($row->[8]) > 10 || (length $row->[8]) < 10) {
            ($row->[0], $row->[1], $row->[3], $row->[4]) = undef;
        }
    }
    # add headers as first row
    unshift(@$rows, $headers);

    # Write the updated rows to new file
    my $col_num = scalar @$headers - 1;
    write_excel($out_file, $rows, $col_num);
    
    sub read_excel {
       my ( $file, $sheet ) = @_;
        $sheet ||= 0;
    
        my $parser = Spreadsheet::ParseXLSX->new();
        my $workbook = $parser->parse($file);
        if ( not defined $workbook ) {
            die $parser->error;
        }
 
        my $worksheet = $workbook->worksheet($sheet);
 
        my ( $row_min, $row_max ) = $worksheet->row_range();
        my ( $col_min, $col_max ) = $worksheet->col_range();
 
        my @rows;
        for my $row ( $row_min .. $row_max ) {
            my @cells;
            for my $col ( $col_min .. $col_max ) {
                my $cell = $worksheet->get_cell( $row, $col );
                if (not $cell) {
                    push(@cells,'');
                    next;
                }
                my $value = $cell->value();
                push(@cells,$value);
            }
            push(@rows,\@cells);
        }
        return \@rows;
    }
    sub write_excel {
        my ( $file, $rows, $col_max ) = @_;
    
        my $workbook = Excel::Writer::XLSX->new( $file );
        if ( not defined $workbook ) {
            die "Could not open file: $!";
        }

        my $worksheet = $workbook->add_worksheet();
        my $worksheet2 = $workbook->add_worksheet();
        my $row_num = 0;

        for my $row ( @$rows ) {
            for my $col (0 .. $col_max) {
                $worksheet->write( $row_num, $col, $row->[$col] );    
+    
            }     
            $row_num++;
        }
        $workbook->close();
        return;
    }

[download]

In reply to Comparing arrays by MoodyDreams999

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.