Re^3: Reformat RAW Excel Data

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]

Comment on Re^3: Reformat RAW Excel Data Download Code

Replies are listed 'Best First'.
Re^4: Reformat RAW Excel Data by 1nickt (Canon) on Jan 12, 2016 at 03:45 UTC
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]

Replies are listed 'Best First'.

Re^4: Reformat RAW Excel Data
by 1nickt (Canon) on Jan 12, 2016 at 03:45 UTC

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]

}  
print "\n";
[download]

Hope this helps!

The way forward always starts with a minimal test.

[reply]
[d/l]
[select]