chown-R

We’ve been doing a lot of promo mailings for the marketing department.  They’re giving me an Excel spreadsheet of addresses and I’ve been converting them into CSV format to hand to UPS Worldship to process as a batch job. There’s several problems:

• UPS expects columns to be in the correct order (and the right number of them)
• UPS hates UTF8 (specifically “–” \x2014)
• There’s PO Boxes scattered throughout.  And UPS doesn’t do PO Boxes.

So I wrote up a Perl script this week to handle the conversion from now on.  CPAN has a Spreadsheet::ParseExcel that can read Excel data, and writing CSV is pretty trivial.  The hard part is getting the columns in the right order.

First, I scan through row 0 (the header row) and try to find the columns I need:

# Try to match actual header with desired header
my $count = -1;
SWITCH:
foreach (@header) {
++$count;
/customer/i   && do { $link_header{'Customer_ID'} = $count; next SWITCH;} ;
/outlet/i     && do { $link_header{'Outlet'}      = $count; next SWITCH;} ;
/first/i      && do { $link_header{'First_Name'}  = $count; next SWITCH;} ;
/last/i       && do { $link_header{'Last_Name'}   = $count; next SWITCH;} ;
/title/i      && do { $link_header{'Title'}       = $count; next SWITCH;} ;
/address.+1/i && do { $link_header{'Address_1'}   = $count; next SWITCH;} ;
/address.+2/i && do { $link_header{'Address_2'}   = $count; next SWITCH;} ;
/city/i       && do { $link_header{'City'}        = $count; next SWITCH;} ;
/state/i      && do { $link_header{'State'}       = $count; next SWITCH;} ;
/postal/i     && do { $link_header{'Postal_Code'} = $count; next SWITCH;} ;
/weight/i     && do { $link_header{'Weight'}      = $count; next SWITCH;} ;
/department/i && do { $link_header{'Department'}  = $count; next SWITCH;} ;
}
# if none of the fields match, there's no point to going on
die "No Header Row\n" unless scalar(keys %link_header) > 1;

Then, swap the keys with the values so I can look up the values later

my %reverse_header = reverse %link_header;

Then scan through the spreadsheet and try to match the column I’m reading with a header I want:

for my $row (1 .. $row_max) {
 for my $col (0 .. $col_max) {
   my $cell = $worksheet->get_cell( $row,$col);
   next unless ($cell);
   my $data = $cell->value();
  # if the column of $data is in headers, save the $data.
   if (exists $reverse_header{$col} ) {
      my $keys = $reverse_header{$col};
   ...
   $temp{$keys} = $data;
}

Then filter out the PO Boxes and the UTF8 stuff and write it out to a CSV. It makes life much easier!