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]