use strict; use warnings; use Data::Dumper; use Spreadsheet::WriteExcel; # Place a filename into $recordsFile to read Orders from that file # else the Orders below __DATA__ will be used for demo purposes #my $recordsFile = 'finished_report_sample.txt'; my $recordsFile = ''; my ( @records, @orders ); my $recSeparator = 'Order ID:'; # Orders will initially be array elements 1 .. n in @orders; element 0 is initially the first page header { # Set the record separator local $/ = $recSeparator; # If there's a file name, try to read from that file if ($recordsFile) { open my $fh, '<', $recordsFile or die $!; @records = <$fh>; close $fh; } # End If else { @records = ; } # End Else } # End preparatory loop # Remove the first page header shift @records; # Add Order ID: back into each record for later matching $_ = "$recSeparator$_" for @records; ########## Added for writing to Excel # Open a new xls file then create a sheet my $workbook = Spreadsheet::WriteExcel->new('distlist.xls'); my $worksheet= $workbook->add_worksheet(); # Write headings $worksheet->write(0,0,'Fiscal Year'); $worksheet->write(0,1,'Vendor'); $worksheet->write(0,2,'PO Number'); $worksheet->write(0,3,'Orderline'); $worksheet->write(0,4,'Title'); $worksheet->write(0,5,'ISBN/ISSN'); $worksheet->write(0,6,'# copies for Title'); $worksheet->write(0,7,'Distribution'); $worksheet->write(0,8,'Date Received'); $worksheet->write(0,9,'Date Loaded'); $worksheet->write(0,10,'Number of Copies'); # Initialise spreadheet counters my $row=1; my $column=0; # Set this up ready for checking for duplicate orders my $previousOrder=""; # Iterate through each record (Order) for my $record (@records) { my %hash; # Treat the record string like a file, opening it for reading open my $sh, '<', \$record or die "Unable to open record string: $!"; # Read the string like a file, one line at a time now while (<$sh>) { $hash{orderID} = $1 if !defined $hash{orderID} and /Order ID:(\S+)/; $hash{fiscalCycle} = $1 if !defined $hash{fiscalCycle} and /cycle:(\d+)/; $hash{vendorID} = $1 if !defined $hash{vendorID} and /Vendor ID:(\S+)/; $hash{requisitionNum} = $1 if !defined $hash{requisitionNum} and /\s+(\d+).+requisition/; $hash{copies} = $1 if !defined $hash{copies} and /copies:(\d+)/; $hash{'ISBN/ISSN'} = $1 if !defined $hash{'ISBN/ISSN'} and m{ISBN/ISSN:(\S+)}; $hash{title} = $1 if !defined $hash{title} and /Title:(.+)/; # CHeck to see if it's a repeat order, skip if it is. my ($hashReference) = \%hash; if (($previousOrder eq $$hashReference{orderID}) && ($previousOrder ne "")) { print "Order: $previousOrder HashOrder: $$hashReference{orderID} \n"; print "Order already processed. Skipping...\n"; last; } # End If else { $worksheet->write($row,0,$$hashReference{fiscalCycle}); $worksheet->write($row,1,$$hashReference{vendorID}); $worksheet->write($row,2,$$hashReference{orderID}); $worksheet->write($row,3,$$hashReference{requisitionNum}); $worksheet->write($row,4,$$hashReference{title}); $worksheet->write($row,5,$$hashReference{'ISBN/ISSN'}); $worksheet->write($row,6,$$hashReference{copies}); $previousOrder = $$hashReference{orderID}; # $row+=1; # Distributions started? if (/Distribution--/) { # Save the current record separator my $oldRecSeparator = $/; # Set a new record separator local $/ = 'Distribution--'; # Read the string like a file, a distribution 'chunk' at a time while (<$sh>) { #I realise this hashing is now superfluous, with data being written # direct to Excel, but am keeping changes to a minimum until I get # the overall functionality correct. my %tempHash; ( $tempHash{holdingCode} ) = /code:(\S+)/; ( $tempHash{copies} ) = /copies:(\d+)/; ( $tempHash{dateReceived} ) = /received:(\S+)/; ( $tempHash{dateLoaded} ) = /loaded:(\S+)/; $worksheet->write($row,7,$tempHash{holdingCode}); $worksheet->write($row,8,$tempHash{dateReceived}); $worksheet->write($row,9,$tempHash{dateLoaded}); $worksheet->write($row,10,$tempHash{copies}); $row+=1; push @{ $hash{distribution} }, \%tempHash; } # End While # Restore the old record separator $/ = $oldRecSeparator; } # End If } # End Else } # End While # Work with the filled-in %hash by sending a reference to it to a subroutine # This is a complete record # writeToSpreadSheet( \%hash ); # print Dumper \%hash; # Done 'reading' the string close $sh; } # End For - last of the loops $workbook->close(); # Printing in a subroutine's not a good idea, but done here only to show how to access the hash #sub writeToSpreadSheet { # my ($hashReference) = @_; # # The $$ notation dereferences the hash reference # print $$hashReference{vendorID}, "\n"; # # The @{} notation deferences the array reference; the arrow operator deferences to get hash value # for my $distribution ( @{ $$hashReference{distribution} } ) { # print $distribution->{holdingCode}, "\n"; # } # # print "\n"; #} __DATA__ List of Distributions Produced Tuesday, 9 October, 2012 at 1:38 PM Order ID:PO-9999 fiscal cycle:21112 Vendor ID:VEND99 order type:SUBSCRIPT 15) requisition number: copies:9 call number:XX(9999999.999) ISBN/ISSN:9999-999X Title:Item title here. ISSN:9999-999X Publication info:More text here about stuff Distribution-- packing list:STUFF-I-DONT-NEED-999 holding code:CODEINFO1 copies:1 date received:27/6/2012 date loaded:27/6/2012 Distribution-- packing list:STUFF-I-DONT-NEED-999 holding code:CODEINFO3 copies:2 date received:27/9/2012 date loaded:27/6/2012 Distribution-- packing list:STUFF-I-DONT-NEED-999 holding code:CODEINFO2 copies:1 date received:25/8/2012 date loaded:27/6/2012 Order ID:PO-9999 fiscal cycle:21112 Vendor ID:VEND99 order type:SUBSCRIPT 15) requisition number: copies:9 call number:XX(9999999.999) ISBN/ISSN:9999-999X Title:Item title here. ISSN:9999-999X Publication info:More text here about stuff Distribution-- packing list:STUFF-I-DONT-NEED-999 holding code:CODEINFO1 copies:1 date received:27/6/2012 date loaded:27/6/2012 Distribution-- packing list:STUFF-I-DONT-NEED-999 holding code:CODEINFO3 copies:2 date received:27/9/2012 date loaded:27/6/2012 Distribution-- packing list:STUFF-I-DONT-NEED-999 holding code:CODEINFO2 copies:1 date received:25/8/2012 date loaded:27/6/2012 List of Distributions Produced Tuesday, 9 October, 2012 at 1:38 PM Order ID:PO-1111 fiscal cycle:21112 Vendor ID:VEND11 order type:SUBSCRIPT 15) requisition number: copies:417 call number:XX(11111111.111) ISBN/ISSN:1111-111X Title:Item title here. ISSN:9999-999X Publication info:More text here about stuff Distribution-- packing list:STUFF-I-DONT-NEED-111 holding code:CODEINFO9 copies:5 date received:11/6/2012 date loaded:12/6/2012 Distribution-- packing list:STUFF-I-DONT-NEED-111 holding code:CODEINFO8 copies:4 date received:11/9/2012 date loaded:12/6/2012 Distribution-- packing list:STUFF-I-DONT-NEED-111 holding code:CODEINFO7 copies:3 date received:11/8/2012 date loaded:12/6/2012 Distribution-- packing list:STUFF-I-DONT-NEED-111 holding code:CODEINFO6 copies:2 date received:11/8/2012 date loaded:12/6/2012