furia furialog · Every Noise at Once · New Particles · The War Against Silence · Aedliga (songs) · photography · other things · contact
perlcsv (utility code)
1.0   23 February 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
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)
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
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
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/\&quot\;/"/g;
   $line =~ s/\&amp\;/\&/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
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.
Site contents published by glenn mcdonald under a Creative Commons BY/NC/ND License except where otherwise noted.