Re: Reformat RAW Excel Data

Good start. You will need to be able to match month names (jan, feb, etc) to month numbers in your dates. You can simplify that by filling the 0th position in the array with a dummy value. Then the month number will equal the index into the array.

Next you need to design the data structure to hold your report. Here's an idea: a hash keyed by the customer name containing a reference to an array of sales by month.

use strict;
use warnings;
use Data::Dumper;

my @months = qw/dummy jan feb mar apr may jun jul aug sep oct nov dec/
+;
my (%report,$cust,$value,$date);

while (<DATA>) {
  chomp;
  if (/^[A-Z]/) {
    $cust = $_;
    next;
  }
  ($date, $value) = split ' ',$_;
  my ($mon, $year) = split '/',$date;
  $report{$cust}[$mon] = $value;
}
print Dumper(\%report);

__DATA__
CUSTA
1/2015 100
10/2015 1,000
12/2015 1,000
CUSTB
2/2015 100
10/2015 1,000
12/2015 100
[download]

This is incomplete, of course. For instance, I'm ignoring the year values in your data.

But God demonstrates His own love toward us, in that while we were yet sinners, Christ died for us. Romans 5:8 (NASB)

Comment on Re: Reformat RAW Excel Data Download Code

Replies are listed 'Best First'.
Re^2: Reformat RAW Excel Data by 1nickt (Canon) on Jan 11, 2016 at 21:58 UTC
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.	[reply] [d/l] [select]
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]
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]