in reply to Reformat RAW Excel Data
#!/usr/bin/perl #use warnings; use strict; my @months = ( '','jan','feb','mar','apr','may','jun','jul','aug','sep +','oct','nov','dec' ); my @CUSTOMERS; # retain order of appearance in Crystal reports file. my %DATA; # hash, as suggested by fellow monks my $customer = ""; # holds current customer my ($month, $day, $value); while(<DATA>){ chomp; next unless $_; # skip empty lines # sorry, others did a better job with a simple split. if( m{^\s*(\d+)/(\d+)\s+([\d,\.]+)} ){ # matches a line with date and value ($month,$day,$value) = ($1, $2, $3); # 0+$month transforms string 02 into value 2 $DATA{$customer}{0+$month} = $value; }else{ $customer = $_; # next customer push @CUSTOMERS, $customer; # map{ $DATA{$customer}{$_}=0 } (1..12) # uncomment for zero's } } print join(";", @months). "\n"; for my $customer (@CUSTOMERS){ print $customer .";" . join( ";", map{ $DATA{$customer}{$_} } (1..12) ) . "\n"; } __DATA__ CUSTA 1/2015 100 10/2015 1,000 12/2015 1,000 CUSTB 02/2015 200 10/2015 1,000 12/2015 100
output:
;jan;feb;mar;apr;may;jun;jul;aug;sep;oct;nov;dec CUSTA;100;;;;;;;;;1,000;;1,000 CUSTB;;200;;;;;;;;1,000;;100
|
|---|