Fixed Length Text Extract, Write to Excel

[William Martell]

Hello All,

I am trying to work with the code I have to extract fields from a text file report, and write the values into excel.

I am having trouble.

When I get to push @order_detail, %item
I understand that this is pushing an associative array onto a list. (array
of hashes)

I am trying to write code that will open a new worksheet in excel and print
the values of %item onto a row. When Perl encounters a certain key
(cust_number), I would like it to start a new row.

I have tried keys(), values(), pop() but I can't get it right. I think some
of my trouble is b/c of the reference \%item. When I remove the reference
backslash operator like this %item and type print. Perl returns with the
contents in scalar.

So. My main question is how do I access the values and keys of %item in
@order_detail???

#!/perl -w
use strict;
use Data::Dumper;
use Win32::OLE;

############################################################################
#######################
# CODE TO READ FROM FIXED LENGTH TEXT FILE
############################################################################
#######################


my $file = 'Artb30.da4';
open INFILE, $file or die "Can't open $file: $!";

my @order_detail;
while ( <INFILE> ) {
last if /^GROUP TOTALS/;
s/\s+\Z//;                  # remove all trailing whitespace
next unless /\d\.\d\d\Z/;   # only want lines with dollar amounts at end
my %item;
if ( length() < 120 ) {     # item 1 lines are shorter
@item{ qw/cust_number cust_name cycle customer_type acct_contact
phone credit_limit/ } =
map { s/^\s+//; $_ }
unpack 'A6 x2 A30 x2 A5 x2 A15 x2 A20 x2 A15 x2 A*', $_;
# unpack'A' removes trailing whitespace so we need the map{}
# to remove leading whitespace
}
elsif ( /^\d/ ) {           # item 2 lines start with a digit
@item{ qw/inv_no type inv_date current days_1_30 days_31_60
days_61_90 days_over_90 on_hold unap_cash total_ar/ } =
map { s/^\s+//; $_ }
unpack 'A6 x A3 x A8' . 'x3 A11' x 8, $_;
}
else {                      # item 3 lines
@item{ qw/cust_totals_current cust_totals_days_1_30
cust_totals_days_31_60
cust_totals_days_61_90 cust_totals_days_over_90
cust_totals_on_hold
cust_totals_unap_cash cust_totals_total_ar/ } =
map { s/^\s+//; $_ }
unpack 'x19' . 'x3 A11' x 8, $_;
}

push @order_detail, \%item;

}

print Dumper \@order_detail;

Thanks.

Will Martell
Dallas Texas
-------------------------------------------

[R. Joseph Newton]

William Martell wrote:

> Hello All,
>
> I am trying to work with the code I have to extract fields from a text file report, and write the values into excel.
>
> I am having trouble.
>
> When I get to push @order_detail, %item
> I understand that this is pushing an associative array onto a list. (array
> of hashes)

That is the problem, a list cannot contain a hash. The keys and values of the hash simply become flattened into list
elements, and all the hashing magic is lost.

perldoc perlref

will explain how references can help resolve this problem..

Joseph

[William Martell]

Thanks for your reply Joseph, I am reading perlref now. If you can offer
some pointers on how to access the key value pairs of the reference object
%item. I would appreciate it.


----- Original Message -----
From: "R. Joseph Newton" <rjnewton@efn.org>
To: "William Martell" <willmartell@yahoo.com>
Cc: <beginning_perl@yahoogroups.com>; <beginners@perl.org>
Sent: Wednesday, January 07, 2004 11:20 AM
Subject: Re: Fixed Length Text Extract, Write to Excel

> William Martell wrote:
>

> > Hello All,
> >
> > I am trying to work with the code I have to extract fields from a text

file report, and write the values into excel.

> >
> > I am having trouble.
> >
> > When I get to push @order_detail, %item
> > I understand that this is pushing an associative array onto a list.

(array

> > of hashes)

>
> That is the problem, a list cannot contain a hash. The keys and values

of the hash simply become flattened into list

> elements, and all the hashing magic is lost.
>
> perldoc perlref
>
> will explain how references can help resolve this problem..
>
> Joseph
>
>
>
> --
> To unsubscribe, e-mail: [email]beginners-unsubscribe@perl.org[/email]
> For additional commands, e-mail: [email]beginners-help@perl.org[/email]
> <http://learn.perl.org/> <http://learn.perl.org/first-response>
>

[Rob Dixon]

William Martell wrote:

>
> I am trying to work with the code I have to extract fields from a text file
> report, and write the values into excel.
>
> I am having trouble.
>
> When I get to push @order_detail, %item

Looking at your code, you mean

push @order_detail, \%item

> I understand that this is pushing an associative array onto a list. (array
> of hashes)
>
> I am trying to write code that will open a new worksheet in excel and print
> the values of %item onto a row. When Perl encounters a certain key
> (cust_number), I would like it to start a new row.
>
> I have tried keys(), values(), pop() but I can't get it right. I think some
> of my trouble is b/c of the reference \%item. When I remove the reference
> backslash operator like this %item and type print. Perl returns with the
> contents in scalar.
>
> So. My main question is how do I access the values and keys of %item in
> @order_detail???

Hi William.

You're building an array of hash references. To access a hash values
from a given array element, write

my $cust_number = $order_detail[0]{cust_number}

To do the same for each detail record, do this

foreach my $detail (@order_detail) {
my $cust_number = $detail->{cust_number};
}

The value returned will be 'undef' if there is no such element for a given
order detail record.

I hope this helps.

Rob

[Wiggins D Anconia]

Please bottom post...

> Thanks for your reply Joseph, I am reading perlref now. If you can offer
> some pointers on how to access the key value pairs of the reference object
> %item. I would appreciate it.
>

May I also suggest the little easier reading of,

perldoc perlreftut
perldoc perldsc
perldoc perllol

To go along with, and usually (at least the first) before perlref. It
can be quite thick to try and get through. The above are designed to be
less of a complete reference and more of a real world help tool....

Good luck,

[url]http://danconia.org[/url]