Beefy Boxes and Bandwidth Generously Provided by pair Networks
Keep It Simple, Stupid
 
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 (Chancellor) 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 rifling through the Monastery: (8)
As of 2014-10-24 08:45 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    For retirement, I am banking on:










    Results (131 votes), past polls