in reply to Re: Reformat RAW Excel Data
in thread Reformat RAW Excel Data

This only populates the array up to the index corresponding to the highest value of $mon. 

__DATA__
CUSTA
1/2015 100
12/2015 1,000
CUSTB
2/2015 100
3/2015 100

[download]
Output: 
$VAR1 = {
          'CUSTA' => [
                       undef,
                       '100',
                       undef,
                       undef,
                       undef,
                       undef,
                       undef,
                       undef,
                       undef,
                       undef,
                       undef,
                       undef,
                       '1,000'
                     ],
          'CUSTB' => [
                       undef,
                       undef,
                       '100',
                       '100'
                     ]
        };

[download]

edit: provided working code

You can pre-populate the array of month values for each customer as you find it: 

    # note:
    #
    # @{ ... } turns the hash entry for this customer into an array.
    #
    # map creates a new list consisting of one element, undef, for eac
+h
    # element from the list it is given, i.e. @months

[download]
#!/usr/bin/perl

use strict;
use warnings;
use Data::Dumper;
$Data::Dumper::Sortkeys = 1;

my @months = qw/dummy jan feb mar apr may jun jul aug sep oct nov dec/
+;

my %report;
my $cust;

while (<DATA>) {
  chomp;
  if ( /^[A-Z]/ ) {
    $cust = $_;
    @{ $report{ $cust } } = map { undef } @months;
    next;
  }

  my ($date, $value) = split ' ', $_;
  my ($mon, $year)   = split '/', $date;

  $report{$cust}[$mon] = $value;
}
print Dumper(\%report);

__DATA__
CUSTA
1/2015 100
12/2015 1,000
CUSTB
2/2015 100
3/2015 100

[download]
Output: 
$VAR1 = {
          'CUSTA' => [
                       undef,
                       '100',
                       undef,
                       undef,
                       undef,
                       undef,
                       undef,
                       undef,
                       undef,
                       undef,
                       undef,
                       undef,
                       '1,000'
                     ],
          'CUSTB' => [
                       undef,
                       undef,
                       '100',
                       '100',
                       undef,
                       undef,
                       undef,
                       undef,
                       undef,
                       undef,
                       undef,
                       undef,
                       undef
                     ]
        };

[download]

Having said all that, however, this problem seems to be ill-suited for using an array. I would store the values in a hash and have the code that reads the hash (probably looping through @months) print 'undef' or a blank or whatever, if there's no value for the given months.


The way forward always starts with a minimal test.

Replies are listed 'Best First'.
Re^3: Reformat RAW Excel Data
by techjohnny (Novice) on Jan 12, 2016 at 01:12 UTC
    First, thank you so much for the response and assistance. Perlmonks never fails to amaze me!
    Here's the code I finally come up with. Those who use Crystal reports will know of the limitations of data printing in rows and not columns.
    #!/bin/perl

my @months=qw/"" jan feb mar apr may jun jul aug sep oct nov dec/; 
open(DATAS,"<","sales.txt") ; 
my (%report,$cust,$value,$date); 

for (@months) 
{ 
print "$_","\t"; 
} 
print "\n"; 
while (<DATAS>) 

{ chomp; 
if (/^[A-Z]/) { 
    $cust = $_; 
    @{$report{$cust}} = map { 0 } @months; 
    next; 
} 
($date,$value) = split ' ', $_; 
my ($mon,$year) = split '/',$date; 
$report{$cust}[$mon]=$value; 

}

foreach my $keys (keys %report) 
{
print $keys,"\t"; 
for (0..12) 
{ print $report{$keys}[$_],"\t"; 
} 
print "\n"; 
}  
print "\n";

    [download]
[reply]
[d/l]

      Glad you got it working. Couple of suggestions:

      • You should always use strict; and use warnings; at the top of your code; Perl provides built-in code validation and it's silly not to use it. One day (many days) it will save your ass.
      • You should check to see whether opening a file succeeded.
      • You should only declare a variable in the smallest scope possible.
      • You might look into Perltidy for help on your code style, which is hard to read and inconsistent. Suggested edit: 

      #!/bin/perl
use strict;
use warnings;

my @months = ('', qw/jan feb mar apr may jun jul aug sep oct nov dec/)
+; 

print($_, "\t") for @months; 
print "\n"; 

my (%report, $cust);

open(my $DATA, '<', 'sales.txt') or die "Open failed: $!";

while (<$DATA>) {
    chomp; 
    if ( /^[A-Z]/ ) { 
        $cust = $_; 
        @{ $report{$cust} } = map { 0 } @months; 
        next; 
    } 

    my ($date, $value) = split ' ', $_; 
    my ($mon, $year) = split '/', $date; 

    $report{$cust}[$mon] = $value; 
}

foreach my $cust (keys %report) {
    print $keys,"\t"; 
    print($report{$cust}[$_], "\t") for 0 .. 12; 
} 
print "\n";

      [download]
      One of the benefits of clear syntax is that it enables you to spot typos, such as the stray 
      }  
print "\n";

      [download]
      in your code above.

      Hope this helps!


      The way forward always starts with a minimal test.
[reply]
[d/l]
[select]

In Section Seekers of Perl Wisdom