perlcsv (utility code)
8 March 05
1.0 23 February 05
1.1 8 March 05
1.1 8 March 05
I periodically find myself with small amounts of tabular data that I want to share, and hand-building HTML tables each time is tedious in repetition and awkward to instrument. I have finally amused myself by writing a simple server-side perl function that renders data from a csv file into an HTML table, and you are welcome to it if you have similar needs.
To use this, download perlcsv.zip and unzip it onto your web server. csv.lib contains the utility function. test.csv is a sample csv file, and testfull.cgi and testsimple.cgi are two sample page scripts that use the utility function to display the sample data.
In the simplest case, here is all that is required to use the function, as shown in testsimple.cgi:
#!/usr/bin/perl
require "csv.lib";
print "Content-type: text/html\n\n";
print "<html>\n";
print "<body>\n";
print showcsv('test');
print "</body>\n";
print "</html>\n";
Include the module, get the page started (the function produces only the table itself), and then call the showcsv function with the name of the .csv file (but not the extension, which must be ".csv"). The function returns HTML, which you can then simply print. Nothing more is technically necessary.
For a more interesting example using the same function and data, though, here is testfull.cgi:
#!/usr/bin/perl
use CGI;
require "csv.lib";
(@types) = ('sa','dd','da','sa');
$query = new CGI;
$sortfield = $query->param('sortfield');
for ($i = 0; $i<=$#types; $i++) {
@criteria[$i] = $query->param('match' . $i);
};
# start the page
print "Content-type: text/html\n\n";
print "<html>\n";
print "<head>\n";
print "<title>2004 Movies</title>\n";
print q{
</head>
<style>
body,td {font: 80% Trebuchet MS}
.csvtable {border: 1px solid #d8d8d8}
.heading {font-weight: bold;background: #e0e0e0}
.headingcurrent {font-weight: bold;background: #d8d8d8}
.search,.data {background: #f0f0f0}
.searchcurrent,.datacurrent {background: #e8e8e8}
</style>
<body>
<div>
};
print showcsv('test','testfull.cgi',$sortfield,'show rank filter',\@types,\@criteria);
print q{
</div>
</body>
</html>
};
As you see, the function is capable of generating a formatted table with:
- an optional column for automatic rank numbering
- optional clickable column headings for data-type-sensitive sorting
- data-type-sensitive column alignment
- visual indication of the sort column
- an optional row of input widgets for filtering by any field
- an optional column for automatic rank numbering
- optional clickable column headings for data-type-sensitive sorting
- data-type-sensitive column alignment
- visual indication of the sort column
- an optional row of input widgets for filtering by any field
To accomplish this, the showcsv function takes six parameters:
- the name of the csv file (without the extension, which must be ".csv"): here "test" for "test.csv"
- the name of the cgi file itself: here "testfull.cgi"
- the sort field, by numeric index starting with 0, hard-coded or here taken from the query string
- a display-flag string, which can be "show rank filter", "show rank", "show filter" or "hide all"
- an array of type specifiers, one per field, which are the strings "sa", "sd", "na", "nd", "da" or "dd", the first character indicating string, numeric or date, and the second indicating whether the field is sorted in ascending or descending order (or just an "h" to hide the column completely)
- an array of simple per-field filters (substring matches with no wildcard processing)
- the name of the csv file (without the extension, which must be ".csv"): here "test" for "test.csv"
- the name of the cgi file itself: here "testfull.cgi"
- the sort field, by numeric index starting with 0, hard-coded or here taken from the query string
- a display-flag string, which can be "show rank filter", "show rank", "show filter" or "hide all"
- an array of type specifiers, one per field, which are the strings "sa", "sd", "na", "nd", "da" or "dd", the first character indicating string, numeric or date, and the second indicating whether the field is sorted in ascending or descending order (or just an "h" to hide the column completely)
- an array of simple per-field filters (substring matches with no wildcard processing)
Except for the csv filename, all the other parameters are optional. However:
- you must provide the cgi filename in order to use ranking, sorting or filtering
- you must provide the sortfield and the type array to use sorting
- you must provide the criteria array to use filtering
- the type and criteria arrays must have the same number of elements as the table has fields
- you must provide the cgi filename in order to use ranking, sorting or filtering
- you must provide the sortfield and the type array to use sorting
- you must provide the criteria array to use filtering
- the type and criteria arrays must have the same number of elements as the table has fields
For formatting purposes, the function assigns seven classes that you may refer to in styles:
- csvtable: the table itself
- heading: a heading-row cell
- search: a filter-row cell
- data: a data-row cell
- headingcurrent, searchcurrent, datacurrent: same as the above, but in the column currently being sorted
- csvtable: the table itself
- heading: a heading-row cell
- search: a filter-row cell
- data: a data-row cell
- headingcurrent, searchcurrent, datacurrent: same as the above, but in the column currently being sorted
Here is the content of csv.lib:
#!/usr/bin/perl
# csv.lib written by glenn mcdonald, published at www.furia.com/misc/perlcsv
# version 1.1, 8 March 2005
use Text::CSV;
use HTML::Entities;
use Date::Parse;
sub showcsv {
# initialize parameters
my ($source,$cgifile,$sortfield,$show,$typesref,$criteriaref) = @_;
my $r = '';
my (@types,@criteria,@lines,@fields,@alldata,@data,@printrows,%alignment);
my ($showfilter,$showrank,$showsort,$doalignments,$dofilter);
my ($i,$csv,$criteriaset,$criterion,$line,$skip,$row,$printrow);
my ($rawnumber,$lastsortvalue,$lastnumber,$thisnumber);
@types = @{$typesref};
@criteria = @{$criteriaref};
$showfilter = ($show =~ /filter/);
$showrank = ($show =~ /rank/);
%alignment = (
s => 'left',
n => 'right',
d => 'right',
);
# read and parse the CSV file
# filter the data and load the data array
# (assuming that the first line has headings)
$csv = Text::CSV->new();
open (DATA, $source . '.csv');
(@lines) = <DATA>;
close DATA;
@fields = separate(@lines[0]);
$showsort = ($#types == $#fields);
$doalignments = $showsort;
$dofilter = ($#criteria == $#fields);
if ($dofilter) {
$criteriaset = 0;
foreach $criterion (@criteria) {
$criteriaset++ if ($criterion ne '');
};
};
foreach $line (@lines[1 .. $#lines]) {
my @newdata = separate($line);
$skip = 0;
if ($showfilter && $criteriaset) {
for ($i = 0; $i <= $#newdata; $i++) {
if ($criteria[$i] ne '') {
if ($criteria[$i] =~ /[A-Z]/) {
$skip = 1 if (index($newdata[$i],encode_entities($criteria[$i])) < 0);
} else {
$skip = 1 if (index(lc($newdata[$i]),encode_entities(lc($criteria[$i]))) < 0);
};
};
};
};
push @alldata, \@newdata if ($skip == 0);
};
@data = @alldata;
# build the headings (as sort links if set)
$r .= '<table class=csvtable>' . "\n";
$r .= '<tr valign=top>' . "\n";
if ($showrank) {
$r .= '<td class=heading align=right>';
$r .= '<a href="' . $cgifile . '?source=' . $source . '">' if ($showsort || $showfilter);
$r .= '#';
$r .= '</a>' if ($showsort || $showfilter);
$r .= '</td>' . "\n";
};
for ($i = 0; $i <= $#fields; $i++) {
if ($types[$i] ne 'h') {
$r .= '<td class=heading' . ifcurrent($showsort,$sortfield,$i);
$r .= ' align=' . $alignment{ substr($types[$i],0,1) } if ($doalignments);
$r .= '>';
$r .= '<a href="' . makesorturl($cgifile,$source,$i,@criteria) . '">' if ($showsort);
$r .= $fields[$i];
$r .= '</a>' if ($showsort);
$r .= '</td>' . "\n";
};
};
$r .= '</tr>' . "\n";
# build the filter row, if requested
if ($showfilter) {
$r .= '<form action="' . $cgifile . '" method="get" name="searchform">' . "\n";
$r .= '<input type=hidden name="source" value="' . $source . '">' . "\n";
$r .= '<input type=hidden name="sortfield" value="' . $sortfield . '">' . "\n";
$r .= '<tr valign=top>' . "\n";
$r .= '<td class=search' . (!$showrank ? ifcurrent($showsort,$sortfield,0) : '') . '>';
$r .= '<input type="submit" value="." name="filter">';
$r .= '</td>' . "\n" if ($showrank);
for ($i = 0; $i <= $#fields; $i++) {
if ($types[$i] ne 'h') {
if ($showrank || $i > 0) {
$r .= '<td class=search' . ifcurrent($showsort,$sortfield,$i);
$r .= ' align=' . $alignment{ substr($types[$i],0,1) } if ($doalignments);
$r .= '>';
};
$r .= '<input size=5 type="text" name="match' . $i . '" value="' . $criteria[$i] . '">';
$r .= '</td>' . "\n";
};
};
$r .= '</tr></form>';
};
# sort the data rows if necessary
if ($sortfield eq '' || !$showsort) {
@printrows = @data;
} else {
if ($types[$sortfield] eq 'na') {
for $row ( sort { $a->[$sortfield] <=> $b->[$sortfield] } @data ) {
push @printrows,$row;
};
} elsif ($types[$sortfield] eq 'nd') {
for $row ( sort { $b->[$sortfield] <=> $a->[$sortfield] } @data ) {
push @printrows,$row;
};
} elsif ($types[$sortfield] eq 'sa') {
for $row ( sort { lc($a->[$sortfield]) cmp lc($b->[$sortfield]) } @data ) {
push @printrows,$row;
};
} elsif ($types[$sortfield] eq 'sd') {
for $row ( sort { lc($b->[$sortfield]) cmp lc($a->[$sortfield]) } @data ) {
push @printrows,$row;
};
} elsif ($types[$sortfield] eq 'da') {
for $row ( sort { str2time($a->[$sortfield]) <=> str2time($b->[$sortfield]) } @data ) {
push @printrows,$row;
};
} elsif ($types[$sortfield] eq 'dd') {
for $row ( sort { str2time($b->[$sortfield]) <=> str2time($a->[$sortfield]) } @data ) {
push @printrows,$row;
};
};
};
# number and build the rows
$rawnumber = 1;
$lastsortvalue = NaN;
$lastnumber = 0;
$thisnumber = 0;
foreach $printrow (@printrows) {
$r .= '<tr valign=top>';
if (@$printrow[$sortfield] eq $lastsortvalue && $showsort) {
$thisnumber = $lastnumber;
$rawnumber++;
} else {
$thisnumber = $rawnumber++;
$lastnumber = $thisnumber;
$lastsortvalue = @$printrow[$sortfield];
};
$thisnumber = '' if (@$printrow[$sortfield] == -1);
$r .= '<td class=data align=right>' . $thisnumber . '</td>' if ($showrank ne '');
for ($i = 0; $i <= $#fields; $i++) {
if ($types[$i] ne 'h') {
$r .= '<td class=data' . ifcurrent($showsort,$sortfield,$i);
$r .= ' align=' . $alignment{ substr($types[$i],0,1) } if ($doalignments);
$r .= '>' . @$printrow[$i] . '</td>' . "\n";
};
};
$r .= '</tr>' . "\n";
};
# finish the table
$r .= '</table>' . "\n";
return $r;
};
sub ifcurrent{
my($showsort,$sortfield,$i) = @_;
return (($showsort && $sortfield ne '' && $i == $sortfield) ? 'current' : '');
};
sub makesorturl{
my ($cgifile,$source,$fieldid,@criteria) = @_;
my $url = $cgifile . '?source=' . $source . '&sortfield=' . $fieldid;
for (my $j = 0; $j <= $#criteria; $j++) {
$url .= '&match' . $j . '=' . $criteria[$j];
};
return $url;
};
sub separate{
my ($line) = @_;
$line = encode_entities($line);
$line =~ s/\"\;/"/g;
$line =~ s/\&\;/\&/g;
my $csv = Text::CSV->new;
my $status = $csv->parse($line);
my (@fields) = $csv->fields();
return @fields;
};
1;
CSV parsing, date comparisons and HTML encoding are handled by standard modules. All query-string processing is handled by the caller, so this function uses only the data passed it. A few extremely minor optimizations have been done for clarity, such as only filtering the data rows if non-null criteria have actually been supplied.
The two changes in version 1.1 of this utility are:
- the function builds the HTML as a return value that the caller can print, rather than printing the HTML directly
- the "#" rank header is only a link if you are sorting or filtering
- the function builds the HTML as a return value that the caller can print, rather than printing the HTML directly
- the "#" rank header is only a link if you are sorting or filtering
Note that I have deliberately omitted the semi-standard table behavior of clicking on the sorted column-heading to reverse the sort, because in practice I rarely encounter such a need, so if you do, there's your first customization project.
The csv file must have column headers in the first row, must use commas and double-quotes, and must not already have been HTML encoded.
Here is the content of test.csv:
Movie,Date,Time,Theater Wilbur Wants to Kill Himself,12/28/04,2:30 PM,MFA "Life Aquatic with Steve Zissou, The",12/27/04,8:10 PM,Kendall "House of Flying Daggers, The",12/20/04,8:20 PM,Kendall Saved!,12/12/04,,pay per view Return to Kandahar,12/11/04,11:00 AM,MFA "Afghan Alphabet, The",12/11/04,11:00 AM,MFA "Polar Express, The",12/7/04,7:20 PM,Cleveland Circle Sideways,12/5/04,9:49 PM,Kendall "Incredibles, The",11/8/04,8:30 PM,Assembly "Day After Tomorrow, The",11/6/04,,rental Goodbye Dragon Inn,10/29/04,7:00 PM,Brattle "Motorcycle Diaries, The",10/24/04,4:05 PM,Kendall Team America: World Police,10/15/04,9:30 PM,Harvard I [Heart] Huckabee's,10/8/04,9:15 PM,Harvard Sky Captain and the World of Tomorrow,9/27/04,7:30 PM,Revere Kill Bill Vol. 1 & 2,9/17/04,,rental Chain,9/5/04,9:40 PM,FFM Untold Scandal,9/5/04,7:00 PM,FFM "Parking Attendant in July, The",9/4/04,11:20 AM,FFM Toukou no Ki (Embers),9/4/04,7:00 PM,FFM What Sebastian Dreamt,9/4/04,9:20 PM,FFM "Crying Wind, The",9/3/04,4:40 PM,FFM Darkness Bride,9/3/04,2:20 PM,FFM "Ketchup Effect, The (Hip Hip Hora)",9/3/04,9:20 PM,FFM One Last Chance,9/2/04,9:40 PM,FFM "Center, The",9/2/04,7:20 PM,FFM Garden State,8/17/04,2:10 PM,Kendall Fahrenheit 9/11,7/30/04,7:00 PM,Boston Common Paycheck,7/25/04,6:00 PM,pay per view Dodgeball,7/18/04,2:40 PM,Boston Common "Corporation, The",7/16/04,8:00 PM,Kendall Before Sunset,7/9/04,7:45 PM,Coolidge Corner "Notebook, The",7/3/04,7:20 PM,"Bedford, CT" Le Temps du Loup,6/30/04,9:40 PM,Brattle Napoleon Dynamite,6/25/04,8:00 PM,Kendall Love Actually,6/13/04,7:15 PM,pay per view Harry Potter and the Prisoner of Azkaban,6/12/04,6:15 PM,Boston Common Bubba Ho-Tep,6/5/04,,rental "Big Animal, The",5/23/04,9:30 PM,Brattle Coffee and Cigarettes,5/22/04,9:45 PM,Harvard "Spring, Summer, Fall, Winter...and Spring",5/2/04,6:55 PM,Kendall Mean Girls,5/1/04,12:20 PM,Boston Common Mona Lisa Smile,4/24/04,8:00 PM,pay per view Seabiscuit,4/9/04,8:00 PM,pay per view Jersey Girl,3/29/04,7:20 PM,Boston Common Eternal Sunshine of the Spotless Mind,3/21/04,4:50 PM,Kendall Millennium Mambo,3/7/04,7:15 PM,Brattle Touching the Void,2/15/04,7:10 PM,Kendall "Company, The",1/26/04,6:50 PM,Kendall "Mighty Wind, A",1/17/04,8:00 PM,pay per view Calendar Girls,1/4/04,7:30 PM,Kendall
Other than that, I think everything is straightforward, or as straightforward as sparsely-commented perl ever gets. I'm not qualified to teach you programming or perl themselves, but feel free to email furiatech@furia.com if you have comments or questions, and I'll do what I can to help.
This code was written by glenn mcdonald. You may use and/or modify it freely, albeit at your own risk, for any purpose or profit, with the only condition being that you may not pretend to have written it yourself. You need not attribute it in otherwise anonymous work, but if any authorship is being claimed, credit this work to its author.