Data Extraction Hierarchial Report

B

banker123

I have the following data in a file.

123456 Johns Account
10.00 Apples 10/08/2006
20.00 Grapes 10/07/2006
987654 Bobs Account
10.00 Oranges 10/08/2006
20.00 Plums 10/07/2006

I need to build a file to populate a database like

123456 Johns Account 10.00 Apples 10/08/2006
123456 Johns Account 20.00 Grapes 10/07/2006
987654 Bobs Account 10.00 Oranges 10/08/2006
987654 Bobs Account 20.00 Plums 10/08/2006

This your classical hierarchial report, the challenge I have had is in
exeucting the append, defining the header and detail records. Please
help, this has been eluding me for some time.
 
B

Ben Morrow

Quoth "banker123 said:
I have the following data in a file.

123456 Johns Account
10.00 Apples 10/08/2006
20.00 Grapes 10/07/2006
987654 Bobs Account
10.00 Oranges 10/08/2006
20.00 Plums 10/07/2006

I need to build a file to populate a database like

123456 Johns Account 10.00 Apples 10/08/2006
123456 Johns Account 20.00 Grapes 10/07/2006
987654 Bobs Account 10.00 Oranges 10/08/2006
987654 Bobs Account 20.00 Plums 10/08/2006

This your classical hierarchial report, the challenge I have had is in
exeucting the append, defining the header and detail records. Please
help, this has been eluding me for some time.

What have you tried?

Ben
 
P

Paul Lalli

banker123 said:
I have the following data in a file.

123456 Johns Account
10.00 Apples 10/08/2006
20.00 Grapes 10/07/2006
987654 Bobs Account
10.00 Oranges 10/08/2006
20.00 Plums 10/07/2006

I need to build a file to populate a database like

123456 Johns Account 10.00 Apples 10/08/2006
123456 Johns Account 20.00 Grapes 10/07/2006
987654 Bobs Account 10.00 Oranges 10/08/2006
987654 Bobs Account 20.00 Plums 10/08/2006

This your classical hierarchial report, the challenge I have had is in
exeucting the append, defining the header and detail records. Please
help, this has been eluding me for some time.

What have you tried so far? How is it not working for you? Please
post a short-but-complete script that demonstrates your best attempt,
and how it is failing.

For a general algorithm, process the file line by line. If the line
does not start with spaces (or tabs, can't tell from your sample data),
set a variable to be that string. If it does, print out the header,
followed by the current line.

Paul Lalli
 
B

banker123

The following code will extract the header record, the challeng I am
having is appending this to the detail records.

open(data,'C:\data.txt');
@array=<data>;

foreach $line(@array){
if ($line =~ /B./){
print "$line";
}
}
 
B

banker123

Should be
open(data,'C:\data.txt');
@array=<data>;


foreach $line(@array){
if ($line =~ /Account/){
print "$line";
 
J

John W. Krahn

banker123 said:
The following code will extract the header record, the challeng I am
having is appending this to the detail records.

open(data,'C:\data.txt');
@array=<data>;

foreach $line(@array){
if ($line =~ /B./){
print "$line";
}
}

You want something like (UNTESTED):

#!/usr/bin/perl
use warnings;
use strict;

my $file = 'C:/data.txt';

open my $fh, '<', $file or die "Cannot open $file: $!";

my $account;
while ( <$fh> ) {
if ( /\A\d/ ) {
( $account = $_ ) =~ s/\s+\z//;
}
elsif ( s/\A\s+// ) {
print "$account $_";
}
}

__END__



John
 
T

Tad McClellan

banker123 said:
Should be


No it shouldn't.

It contains a whole boatload of bad practices.

open(data,'C:\data.txt');


You should use UPPERCASE for bareword filehandles. Even better,
you should use a lexical file handle instead.

The 3-arg form of open() is much much safer.

You should always, yes *always*, check the return value from open().

You can use sensible slashes in paths that are not destined for
a Windows "shell".

@array=<data>;


You should enable strictures in all of your Perl programs. That
requires that you declare each variable that you use.

foreach $line(@array){


There is no need to read the entire file if you are going to
process it line-by-line anyway.

Just read it line-by-line, then it will continue to work even
on huge files.

if ($line =~ /Account/){


Nothing wrong with that one, but some whitespace would make it
easier to read and understand.

print "$line";


You should never quote a lone scalar variable. See:

perldoc -q vars

What's wrong with always quoting "$vars"?



So then, we end up with (untested):

use warnings;
use strict;

open( my $ACCOUNTS, '<', 'C:/data.txt' ) or
die "could not open the 'C:/data.txt' file $!";
while ( my $line = <$ACCOUNTS> ) {
if ( $line =~ /Account/ ) {
print $line;
}
}
close $ACCOUNTS;
 
B

banker123

See output, this seemd to work except for the last line, any
suggestions?

123456 Johns Account 10.00 Apples 10/08/2006
123456 Johns Account 20.00 Grapes 10/07/2006
987654 Bobs Account 10.00 Oranges 10/08/2006
987654 Bobs Account 20.00 Plums 10/07/2006
987654 Bobs Account 987654 Bobs Account
 

Members online

Forum statistics

Threads
473,744
Messages
2,569,482
Members
44,901
Latest member
Noble71S45

Latest Threads

Top