Beefy Boxes and Bandwidth Generously Provided by pair Networks
Clear questions and runnable code
get the best and fastest answer
 
PerlMonks  

SQL Database table to Perl Spreadsheet script

by dinkar (Initiate)
on Oct 13, 2012 at 18:18 UTC ( #998867=perlquestion: print w/ replies, xml ) Need Help??
dinkar has asked for the wisdom of the Perl Monks concerning the following question:

I have a transaction table like this:
TranID Date AccNum Type Amount ChequeNo DDNo 657520 02-07-1999 0181432 Debit 16000 465774 657524 02-07-1999 0181432 Debit 13000 569086 657538 09-07-1999 0181432 Credit 11000 657548 18-07-1999 0181432 Credit 15500 657519 02-07-1999 0181432 Debit 12000 657523 02-07-1999 0181432 Credit 11000 657529 03-07-1999 0181433 Debit 15000 466777 657539 10-07-1999 0181433 Credit 10000 657541 11-07-1999 0181433 Debit 12000 657525 03-07-1999 0181433 Debit 15000 569999 657533 05-07-1999 0181433 Credit 12500
My question is: Query the data from transaction table and calculate the total amount debited by cheque, dd and cash for each account and store the result in a spreadsheet? My code is like this:
#!/usr/bin/perl use strict; use warnings; use DBI; use Spreadsheet::WriteExcel; my $dbh = DBI->connect('dbi:mysql:database:3306','prithvi','prithvi') +or die $dbh->errstr; my $sth = $dbh->prepare("SELECT `AccNum`,`Type`,`Amount`,`ChequeNo`,`D +DNo` FROM `transaction`"); $sth->execute or die $sth->errstr; my $workbook = Spreadsheet::WriteExcel->new('query_result.xls'); my $worksheet = $workbook->add_worksheet(); my $row = 0; my $col = 0; my %h; $worksheet->write_row($row++,$col,['Account Number','Cheque Debit','DD + Debit','Cash Debit']); while(my @data = $sth->fetchrow_array) { next unless($data[1] eq 'Debit'); my $result = $data[3] ? "ChequeNo" : $data[4] ? "DDNo" : "Cash"; $h{$data[0]}{$result} += $data[2]; $worksheet->write_row($row++,$col,\@data); } $sth->finish; $dbh->disconnect;
I am not getting the proper output. Where am I going wrong? Please help. Thanks in advance

Comment on SQL Database table to Perl Spreadsheet script
Select or Download Code
Re: SQL Database table to Perl Spreadsheet script
by erix (Vicar) on Oct 13, 2012 at 22:27 UTC

    This question was answered already: 997371

      Sir.My question is to store the result in a spreadsheet.

        Sir.My question is to store the result in a spreadsheet.

        so what kind of output do you get?

Re: SQL Database table to Perl Spreadsheet script
by Generoso (Vicar) on Oct 14, 2012 at 08:02 UTC

    Maybe this is what you are looking for.

    my @sql =(); $sql[0] = qq[SELECT AccNum as Account, max(date) as Date, Type, count( +*) as Total_No, sum(Amount) as Amount FROM transaction GROUP BY AccNum, Type union SELECT AccNum, max(date),'Balance', count(*), sum(if(type='Debit',-1*A +mount,Amount)) FROM transaction GROUP BY AccNum;]; $sql[1] = qq[SELECT AccNum as Account, max(date) as Date, Type, 'CHEQU +ENO' as Subtype, count(*) as Total_No, sum(Amount) as Amount FROM transaction where chequeno is not null GROUP BY AccNum, Type union SELECT AccNum as Account, max(date) as Date, Type, 'DDNO' as Subtype, +count(*) as Total_No, sum(Amount) as Amount FROM transaction where DDNO is not null GROUP BY AccNum, Type;]; $sql[2] = qq[desc transaction;]; open (FH, ">".$ENV{"TMP"}."\\mytmp.html") || die "Cannot open temporar +y file: $!\n"; print FH '<HTML><Body>'; foreach (@sql) { my $l = $_; $l =~ s/\n/<br>/g; print FH '<p>'.$l.'</p>'; my $tran_cur = $dbh->prepare($_)or die "Cannot prepare: " . $DBI:: +errstr; $tran_cur->execute or die "Cannot execute: " . $DBI::errstr; print FH '<table border=1 cellspacing=1 cellpadding=2><tr>'; map {print FH "<td>$_</td>"}@{$tran_cur->{NAME}}; print FH "</tr><br/>"; while( my @data = $tran_cur->fetchrow_array) { print FH "<tr>"; map {print FH "<td>"; my $r = defined ($_) ? $_ : '&nbsp;'; print FH length ($r)>0 ? $r: '&nbsp;'; print FH "</td>";}@data; print FH "</tr>"; } print FH "</table>"; } print FH "</Body></HTML>\n"; my $url = "file://".$ENV{"TMP"}."\\mytmp.html"; my $commandline = qq{start "$url" "$url"}; system($commandline) == 0 or die qq{Couldn't launch '$commandline': $!/$?}; close FH; $dbh->disconnect; sleep(5); unlink $ENV{"TMP"}."\\mytmp.html" or warn "Could not unlink ".$ENV{"TM +P"}."\\mytmp.html: $!";

    Result:

    SELECT AccNum as Account, max(date) as Date, Type, count(*) as Total_No, sum(Amount) as Amount
    FROM transaction GROUP BY AccNum, Type
    union
    SELECT AccNum, max(date),'Balance', count(*), sum(if(type='Debit',-1*Amount,Amount))
    FROM transaction GROUP BY AccNum;


    AccountDateTypeTotal_NoAmount
    1814321999-07-18Credit560000.00
    1814321999-07-11Debit683000.00
    1814321999-07-18Balance11-23000.00

    SELECT AccNum as Account, max(date) as Date, Type, 'CHEQUENO' as Subtype, count(*) as Total_No, sum(Amount) as Amount
    FROM transaction where chequeno is not null GROUP BY AccNum, Type
    union
    SELECT AccNum as Account, max(date) as Date, Type, 'DDNO' as Subtype, count(*) as Total_No, sum(Amount) as Amount
    FROM transaction where DDNO is not null GROUP BY AccNum, Type;


    AccountDateTypeSubtypeTotal_NoAmount
    1814321999-07-03DebitCHEQUENO231000.00
    1814321999-07-03DebitDDNO228000.00

    desc transaction;


    FieldTypeNullKeyDefaultExtra
    TranIDint(10) unsignedNOPRI  
    DatedateNO   
    AccNumint(10) unsignedNO   
    Typeenum('Credit','Debit')NO   
    Amountdecimal(10,2)NO   
    ChequeNoint(10) unsignedYES   
    DDNoint(10) unsignedYES   
      Sir.My question is to store the result in a spreadsheet. This is not the script I am looking for. You can make out by the code I have given for which I am looking for.
        I don't think prefixing your posts with "sir" is having the effect you want it to have.


        - Boldra
Re: SQL Database table to Perl Spreadsheet script
by marto (Bishop) on Oct 14, 2012 at 09:11 UTC
Re: SQL Database table to Perl Spreadsheet script
by Generoso (Vicar) on Oct 14, 2012 at 09:45 UTC

    All you need is to change the HTML code for OLE excel code.

    #!/usr/bin/perl use DBI; use strict; use warnings; use OLE; use Win32::OLE::Const "Microsoft Excel"; my $dbh = DBI->connect('dbi:mysql:database:3306','prithvi','prithvi') +or die "Cannot connect: $DBI::errstr\n"; my @sql =(); $sql[0] = qq[SELECT AccNum as Account, max(date) as Date, Type, count( +*) as Total_No, sum(Amount) as Amount FROM transaction GROUP BY AccNum, Type union SELECT AccNum, max(date),'Balance', count(*), sum(if(type='Debit',-1*A +mount,Amount)) FROM transaction GROUP BY AccNum;]; $sql[1] = qq[SELECT AccNum,Type,Amount,ChequeNo,DDNo FROM transaction; +]; $sql[2] = qq[desc transaction;]; my $excel = CreateObject OLE "Excel.Application"; $excel -> {Visible} = 1; my $workbook = $excel -> Workbooks -> Add; my $i =0; foreach (@sql) { $i++; my $sheet = $workbook -> Worksheets("Sheet$i"); $sheet -> Activate; my $row = 1; my $col = 1; my $l = $_; $l =~ s/\n/<br>/g; print ' '.$l.'</p>'; my $tran_cur = $dbh->prepare($_)or die "Cannot prepare: " . $DBI:: +errstr; $tran_cur->execute or die "Cannot execute: " . $DBI::errstr; foreach (@{$tran_cur->{NAME}}) {$sheet->Cells($row,$col++)->{'Value'} = $_;} $col = 1; while( my @data = $tran_cur->fetchrow_array) {$row++; foreach (@data) { $sheet->Cells($row,$col++)->{'Value'} = $_;} $col = 1; } } $dbh->disconnect; #$workbook -> SaveAs ("C:\\file_name.xls"); #$excel -> Quit;

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://998867]
Approved by Corion
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others scrutinizing the Monastery: (6)
As of 2014-12-18 03:25 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    Is guessing a good strategy for surviving in the IT business?





    Results (41 votes), past polls