2010
01.20

From SQL to Excel, with Perl

Category: IT / Tag: , , , ,  / Add Comment
Be the first to like.
Unlike
Share

Quite often I’m asked to pull out some information from a database, process it and produce an Excel report.
Here is a minimal Perl script that carries out the task.

  • Define the column headings and their widths. @columns array.
  • Handle the command line parameters. There are 5 in the example, assigned to the $p_* variables.
  • Prepare the Excel worksheet, defining cell formatting, …
  • Connect to the database.
  • Prepare the query, substituting the command line parameters.
  • Fetch rows, populate the sheet.
#!/usr/bin/perl
# Giuliano - http://www.108.bz
use strict;
use DBI;
use Spreadsheet::WriteExcel;

use constant C_HEADING => 0;
use constant C_WIDTH   => 1;
my @columns = (
    ['Date',      22 ],
    ['Caller',    20 ],
    ['Called',    20 ],
    ['Connected', 11 ],
    ['Duration',  11 ],
    ['Reason',    24 ],
    ['XferExt',   11 ],
    ['XferName',  22 ]
);

die <<EOM unless @ARGV == 5;
usage:
$0 year month day phonenumber file.xls
EOM
my ($p_year, $p_month, $p_day, $p_phnumber, $p_filename) = @ARGV;

my $workbook        = Spreadsheet::WriteExcel->new($p_filename);
my $sheet           = $workbook->add_worksheet("Data");
my $default_format  = $workbook->add_format(num_format => '@'); $default_format->set_font('Verdana'); $default_format->set_border(1);
my $bold_format     = $workbook->add_format(); $bold_format->set_font('Verdana'); $bold_format->set_bold(); $bold_format->set_border(1);

$sheet->write(0,$_,$columns[$_]->[C_HEADING], $bold_format) for (0..$#columns);
$sheet->set_column($_, $_, $columns[$_]->[C_WIDTH]) for (0..$#columns);

my $dbh = DBI->connect('dbi:Sybase:server=dsnname;database=dnbame','username','password') or die;

my $sth = $dbh->prepare(<<EOQ
SELECT IpPbxCDR.StartTime, IpPbxCDR.OriginationNumber, IpPbxCDR.CalledNumber, IpPbxCDR.DestinationNumber, DATEDIFF(ss, IpPbxCDR.StartTime, IpPbxCDR.EndTime) AS Duration, IpPbxCDR.DisconnectReason, IpPbxCDR_1.CalledNumber AS XferExt,
IpPbxCDR_1.CalledName AS XferName
FROM IpPbxCDR LEFT OUTER JOIN
IpPbxCDR AS IpPbxCDR_1 ON IpPbxCDR.TransferredToCallId = IpPbxCDR_1.CallId
WHERE (IpPbxCDR.CalledNumber LIKE '$p_phnumber') AND
(MONTH(IpPbxCDR.StartTime) = $p_month) AND
(YEAR(IpPbxCDR.StartTime) = $p_year) AND
(DAY(IpPbxCDR.StartTime) = $p_day)
ORDER BY IpPbxCDR.StartTime
EOQ
);

$sth->execute();

my $i = 1;
my $row;
while ( $row = $sth->fetchrow_arrayref ) {
    $sheet->write_string($i,$_,$row->[$_], $default_format) for (0..$#$row);
    $i++;
}

$sheet->activate();

exit;

Actually, the example does something useful. It connects to a Swyx Call Detail Record database, selecting phone calls placed to a given number on a given day. The generated report also contains call duration and transfer status/destination, if any. Here’s what it looks like (some data has been obfuscated, to protect the innocent – click to see all the columns):

And here’s the command that produces it:

./callreport.pl 2010 1 19 '+39%10123123' x.xls
Share